SQL SERVER – Finding Shortest Distance between Two Shapes using Spatial Data Classes – Ramsetu or Adam’s Bridge

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.

SQL SERVER - Finding Shortest Distance between Two Shapes using Spatial Data Classes - Ramsetu or Adam's Bridge spatial_1

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

SQL SERVER - Finding Shortest Distance between Two Shapes using Spatial Data Classes - Ramsetu or Adam's Bridge spatial_2

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

SQL SERVER - Finding Shortest Distance between Two Shapes using Spatial Data Classes - Ramsetu or Adam's Bridge spatial_3

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.

SQL SERVER - Finding Shortest Distance between Two Shapes using Spatial Data Classes - Ramsetu or Adam's Bridge spatial_4

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.

SQL SERVER - Finding Shortest Distance between Two Shapes using Spatial Data Classes - Ramsetu or Adam's Bridge ramsetu

SQL SERVER - Finding Shortest Distance between Two Shapes using Spatial Data Classes - Ramsetu or Adam's Bridge ramsetu1

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – TechEd India 2012 – Content, Speakers and a Lots of Fun
Next Post
SQLAuthority News – #TechEDIn – TechEd India 2012 – Things to Do and Explore for SQL Enthusiast

Related Posts

7 Comments. Leave new

  • wow. cool

    Reply
  • Chintan Gandhi
    March 20, 2012 12:42 pm

    wow…to think after all this technology, our ancestors were still ahead of us :D

    Reply
  • 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

    Reply
  • I am also getting the same error Could not find method ‘ShortestLineTo’ for type ‘Microsoft.SqlServer.Types.SqlGeometry’ in assembly ‘Microsoft.SqlServer.Types

    Reply
  • This is sql server 2012 feature, not available in 2008.

    Reply
  • 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

    Reply
  • 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

    Reply

Leave a Reply

Menu