Answered by:
STDistance is slow
Question

Hi all,
I have a table containing 2 location columns, l1 and l2.
They are stored as line strings inside geography columns, and are initially computed from ordered sets of (Latitude, Longitude) associated with each row.
I noticed that a query [select l1.STDistance(l2) from t] that calculates the distances between l1 and l2 is very slow (i'm doing a full table select so it's not a matter of indices here).
My objects are relatively close (under 50 Kilometers), and I could get away with a precision within a hundred meters for instance, so I was wondering if there is any way to significantly speed up the query without having to write my own methods for computing distances between line strings using some lighter algorithm.
Thanks
Thursday, January 12, 2012 5:27 PM
Answers

I don't think there's anything you can do to speed up the STDistance() method itself. The only thing I can think of is that, if you're willing to accept a degree of tolerance in the result, why not calculate the distance between only one point in each LineString? (For example, calculate the distance between the centre point of each LineString, or between the endpoint of one l1 and the startpoint of l2).
I believe there are some efficiencies programmed into STDistance() when calculating the distance between two point geometries rather than between two LineStrings.
Other than that, you could implement your own more approximate algorithm, based on the Haversine formula, say.
twitter: @alastaira blog: http://alastaira.wordpress.com/ Marked as answer by Stephanie Lv Tuesday, January 24, 2012 9:25 AM
Monday, January 16, 2012 10:06 AMAnswerer
All replies


I don't think there's anything you can do to speed up the STDistance() method itself. The only thing I can think of is that, if you're willing to accept a degree of tolerance in the result, why not calculate the distance between only one point in each LineString? (For example, calculate the distance between the centre point of each LineString, or between the endpoint of one l1 and the startpoint of l2).
I believe there are some efficiencies programmed into STDistance() when calculating the distance between two point geometries rather than between two LineStrings.
Other than that, you could implement your own more approximate algorithm, based on the Haversine formula, say.
twitter: @alastaira blog: http://alastaira.wordpress.com/ Marked as answer by Stephanie Lv Tuesday, January 24, 2012 9:25 AM
Monday, January 16, 2012 10:06 AMAnswerer