Recently I was reading excellent blog post by Lenni Lobel on Spatial Database. He has written very interesting function ShortestLineTo in Spatial Data Classes. I really loved this new feature of the finding shortest distance between two shapes in SQL Server. Following is the example which is same as Lenni talk on his blog article .
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 finds out the shortest distance between two shapes and draws the line. We are using STBuffer so we can see the connecting line clearly.
Now let us modify one of the object and then we 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
Now once again let us modify one of the script and see how the shortest line to 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 -18, -18 -20))'
SELECT @Shape1
UNION ALL
SELECT @Shape2
UNION ALL
SELECT @Shape1.ShortestLineTo(@Shape2).STBuffer(.25)
SELECT @Shape1.STDistance(@Shape2)
GO
You can see as the objects are changing the shortest lines are moving at appropriate place. I think even though this is very small feature this is really cool know.
While I was working on this example, I suddenly thought about distance between Sri Lanka and India. The distance is very short infect it is 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 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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
7 Comments. Leave new
wow. cool
wow…to think after all this technology, our ancestors were still ahead of us :D
Hi Pinal,
When I ran the script: Got the error:
Msg 6506, Level 16, State 10, Line 7
Could not find method ‘ShortestLineTo’ for type ‘Microsoft.SqlServer.Types.SqlGeometry’ in assembly ‘Microsoft.SqlServer.Types’
Using SQL Server 2008.
Thank you
I am also getting the same error Could not find method ‘ShortestLineTo’ for type ‘Microsoft.SqlServer.Types.SqlGeometry’ in assembly ‘Microsoft.SqlServer.Types
This is sql server 2012 feature, not available in 2008.
I had this problem elsewhere. CLR CREATE PROCEDURE likes a fully specified name.
This fails:
EXTERNAL NAME upc_GetWaitingQueueItems.InvokeWebService.upc_GetWaitingQueueItems
While this succeeds:
EXTERNAL NAME upc_GetWaitingQueueItems.[upc_GetWaitingQueueItems.InvokeWebService].upc_GetWaitingQueueItems
Hi Pinal,
I was looking to do something similar on SQL 2008. I end up using FME to get this done by finding the nearest neighbour and creating points at either end of the object then connecting them using the point connector.
Can you post this user defined function so I can use in the future.
Thanks
Surag