Shortest Distance Between Two Shapes in SQL Server: Ramsetu or Adam’s Bridge

Finding the shortest distance between two shapes is one of the interesting things SQL Server spatial data can do. I was reading a post by Lenni Lobel about the ShortestLineTo method. I enjoyed the example so much that I wanted to explore it further.

The first example follows Lenni’s original polygons. ShortestLineTo returns the shortest connecting line. STBuffer makes that line wide enough to see in the spatial results.

DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -26, 14 -28, 20 -40, -20 -30))'
DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))'
SELECT @Shape1
UNION ALL
SELECT @Shape2
UNION ALL
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)
GO

Run the query in SQL Server Management Studio and select the Spatial results tab.

Now change one point in the first shape and see where the shortest line moves.

DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -30, 14 -28, 20 -40, -20 -30))'
DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))'
SELECT @Shape1
UNION ALL
SELECT @Shape2
UNION ALL
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)
GO

Once again, change a point, this time on the second shape. I have also added STDistance to return the distance between the polygons. The picture at the top of this post shows the result.

DECLARE @Shape1 geometry = 'POLYGON ((-20 -30, -3 -30, 14 -28, 20 -40, -20 -30))'
DECLARE @Shape2 geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -18, -18 -20))'
SELECT @Shape1
UNION ALL
SELECT @Shape2
UNION ALL
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)
SELECT @Shape1.STDistance(@Shape2)
GO

As the shapes change, the shortest line moves to the closest points. I find it fascinating that a small change to the coordinates gives a different connecting line.

The Numbers Behind the Pictures

The pictures show the line, and a query gives you its numbers. This query returns each line as text, with its length and the distance. The first point of a line is on the first shape. The last point is on the second shape.

DECLARE @FirstShape geometry = 'POLYGON ((-20 -30, -3 -26, 14 -28, 20 -40, -20 -30))';
DECLARE @FirstShapeMoved geometry = 'POLYGON ((-20 -30, -3 -30, 14 -28, 20 -40, -20 -30))';
DECLARE @SecondShape geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -22, -18 -20))';
DECLARE @SecondShapeMoved geometry = 'POLYGON ((-18 -20, 0 -10, 4 -12, 10 -20, 2 -18, -18 -20))';
SELECT 'example 1' AS example, @FirstShape.ShortestLineTo(@SecondShape).STAsText() AS shortest_line,
       @FirstShape.ShortestLineTo(@SecondShape).STLength() AS line_length, @FirstShape.STDistance(@SecondShape) AS distance
UNION ALL
SELECT 'example 2', @FirstShapeMoved.ShortestLineTo(@SecondShape).STAsText(),
       @FirstShapeMoved.ShortestLineTo(@SecondShape).STLength(), @FirstShapeMoved.STDistance(@SecondShape)
UNION ALL
SELECT 'example 3', @FirstShapeMoved.ShortestLineTo(@SecondShapeMoved).STAsText(),
       @FirstShapeMoved.ShortestLineTo(@SecondShapeMoved).STLength(), @FirstShapeMoved.STDistance(@SecondShapeMoved);

The line length matches STDistance in every row, as it should. I use STBuffer only to draw the line. For a number, I use STDistance. The distance is in the units of the coordinates, not meters, because these are flat geometry shapes.

While working on this example, I thought about the short distance between India and Sri Lanka. I plotted simplified shapes of the two countries. The connecting line was near the chain of shoals known as Ramsetu or Adam’s Bridge. The shoals run between Pamban Island in India and Mannar Island in Sri Lanka.

The country shapes in my original plot are simplified. The plot illustrates the idea. It doesn’t measure the actual coastline or the route of the shoals.

Find the Closest of Several Shapes

STDistance also ranks shapes. This query measures from one point to three fields and sorts by the answer. The nearest field comes first, so the first row answers the closest-shape question for a whole table.

DECLARE @Origin geometry = geometry::STGeomFromText('POINT (0 0)', 0);
SELECT s.Name, @Origin.STDistance(s.Shape) AS Distance
FROM (VALUES ('North field', geometry::STGeomFromText('POLYGON ((-2 5, 2 5, 2 8, -2 8, -2 5))', 0)),
             ('East field', geometry::STGeomFromText('POLYGON ((6 -1, 9 -1, 9 1, 6 1, 6 -1))', 0)),
             ('South field', geometry::STGeomFromText('POLYGON ((-1 -4, 1 -4, 1 -3, -1 -3, -1 -4))', 0))) AS s(Name, Shape)
ORDER BY Distance;

The south field came first at 3, then the north field at 5 and the east field at 6.

The Same Method on the Globe

Flat coordinates suit a drawing. For real places, the geography type measures on the earth’s surface and returns meters. I picked two points: one near Dhanushkodi, the end of Pamban Island, and one near Talaimannar on Mannar Island. The coordinates are my own rounded values from public maps. They aren’t surveyed, so the result is only as good as the points.

DECLARE @Dhanushkodi geography = geography::Point(9.17, 79.42, 4326);
DECLARE @Talaimannar geography = geography::Point(9.08, 79.72, 4326);
SELECT CAST(@Dhanushkodi.STDistance(@Talaimannar) AS decimal(12,0)) AS metres,
       CAST(@Dhanushkodi.STDistance(@Talaimannar) / 1000.0 AS decimal(8,1)) AS kilometres,
       @Dhanushkodi.ShortestLineTo(@Talaimannar).STAsText() AS shortest_line;

It returned 34,446 meters, about 34.4 kilometers. In the text form of the line, the longitude comes first. That order differs from the constructor, where geography::Point takes latitude first. When I swapped the two arguments, SQL Server accepted them without an error and the point landed somewhere else entirely. Check the order whenever a distance looks wrong.

Shapes That Overlap, and Mismatched Spatial IDs

If the shapes intersect, there is no gap to measure. I tested two overlapping squares. STIntersects returned 1, STDistance returned 0 and ShortestLineTo returned an empty line. The two shapes also need matching spatial reference identifiers. A point with SRID 0 and a point with SRID 4326 returned NULL for the distance. Two points with the same SRID returned 5.

DECLARE @P geometry = 'POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))';
DECLARE @Q geometry = 'POLYGON ((5 5, 15 5, 15 15, 5 15, 5 5))';
SELECT @P.STIntersects(@Q) AS intersects, @P.STDistance(@Q) AS distance, @P.ShortestLineTo(@Q).STAsText() AS shortest_line;
DECLARE @G0 geometry = geometry::STGeomFromText('POINT (0 0)', 0);
DECLARE @G4326 geometry = geometry::STGeomFromText('POINT (3 4)', 4326);
SELECT @G0.STDistance(@G4326) AS distance_srid_0_vs_4326, @G0.STDistance(geometry::STGeomFromText('POINT (3 4)', 0)) AS distance_same_srid;

One practical note: ShortestLineTo is available in SQL Server 2012 and later. For the plain geometry examples, the distance uses the coordinate units of the shapes.

This small spatial feature led me from two simple polygons to a place I find fascinating. That is what I enjoy about exploring SQL Server.

A shortest line is not a route, it is the closest pair of points between two shapes.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.