distance between two points across land using sql server

Question

I am looking to calculate the shortest distance between two points inside SQL Server 2008 taking into account land mass only.

I have used the geography data type along with STDistance() to work out point x distance to point y as the crow flies, however this sometimes crosses the sea which i am trying to avoid.

I have also created a polygon around the land mass boundary I am interested in.

I believe that I need to combine these two methods to ensure that STDistance always remains within polygon - unless there is a simpler solution.

Thanks for any advice

Answer 1

Use STIntersects - http://msdn.microsoft.com/en-us/library/bb933899%28v=SQL.105%29.aspx to find out what part of the line is over land.

After reading your comment your requirement makes sense. However I'm pretty sure there are no inbuilt techniques to do this in SQL Server. I'm assuming you are ignoring roads, and taking an as-the-crow-flies approach but over land only.

The only way I can think to do this would be to convert your area into a raster (grid cells) and perform a cost path analysis. You would set the area of sea to have a prohibitively high cost so the algorithm would route around the sea. See this link for description of technique:

http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=cost_path

Otherwise try implementing the algorithm below!

http://bit.ly/ckvciz

There may be other libraries that do this. Alteratively how about using the new Google Directions API between the two cities - you'd get actual road distances then.

http://code.google.com/apis/maps/documentation/directions/