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 (http://blog.sqlauthority.com)
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
[...] Finding Shortest Distance between Two Shapes using Spatial Data Classes – Ramsetu or Adam’s Brid… 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. [...]