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.

spatial 1 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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

spatial 2 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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

spatial 3 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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.

spatial 4 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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.

ramsetu SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

ramsetu1 SQL SERVER   Finding Shortest Distance between Two Shapes using Spatial Data Classes   Ramsetu or Adams Bridge

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

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

  1. 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

    Like

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

    Like

  3. 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

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

  5. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s