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.

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)

About these ads

7 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

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

  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

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

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