Recently, I came across an excellent blog post by Lenni Lobel on Spatial Database. In his post, he discussed a very interesting function called ShortestLineTo in Spatial Data Classes. This function allows us to find the shortest distance between two shapes in SQL Server.
Let’s take a look at an example:
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
When you run this script, SQL Server will find the shortest distance between the two shapes and draw a connecting line. We are using STBuffer to make the connecting line more visible.
Now, let’s modify one of the objects and see how the connecting shortest line works:
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
As you can see, as the objects are changing, the shortest lines are moving to the appropriate places.
Although this may seem like a small feature, it is actually quite cool. It can be used in various scenarios, such as finding the shortest distance between two locations or analyzing spatial data.
While I was working on this example, I suddenly thought about the distance between Sri Lanka and India. The distance is very short, less than 30 km by sea. I decided to map India and Sri Lanka using spatial data classes. To my surprise, the plotted shortest line is the same as Adam’s Bridge or Ramsetu.
Adam’s Bridge starts as a chain of shoals from the Dhanushkodi tip of India’s Pamban Island and ends at Sri Lanka’s Mannar Island. Geological evidence suggests that this bridge is a former land connection between India and Sri Lanka.
In conclusion, the ShortestLineTo function in SQL Server’s Spatial Data Classes is a powerful tool for analyzing spatial data and finding the shortest distance between shapes. It can be used in various scenarios and provides valuable insights into spatial relationships.