MS SQL - Is using geometry data type to find distance significantly faster?

Question

Hi guys,

I have a database which contains a lot of geospatial data ... basically information on 10s of thousands of people, with coordinates for each of them.

The coordinates are currently stored as two floats for latitude and longitude, and I use a function to determine the distance between the coordinates in that record and a coordinate I pass in ... basically to sort and limit the results I get by distance. This is roughly the code used in the function.

DECLARE @earthSphereRadiusKilometers as float
DECLARE @kilometerConversionToMilesFactor as float
SELECT @earthSphereRadiusKilometers = 6366.707019
SELECT @kilometerConversionToMilesFactor = .621371

-- convert degrees to radians
DECLARE @lat1Radians float
DECLARE @lon1Radians float
DECLARE @lat2Radians float
DECLARE @lon2Radians float
SELECT @lat1Radians = (@lat1Degrees / 180) * PI()
SELECT @lon1Radians = (@lon1Degrees / 180) * PI()
SELECT @lat2Radians = (@lat2Degrees / 180) * PI()
SELECT @lon2Radians = (@lon2Degrees / 180) * PI()

-- formula for distance from [lat1,lon1] to [lat2,lon2]
RETURN ROUND(2 * ASIN(SQRT(POWER(SIN((@lat1Radians - @lat2Radians) / 2) ,2) + COS(@lat1Radians) * COS(@lat2Radians) * POWER(SIN((@lon1Radians - @lon2Radians) / 2), 2))) * (@earthSphereRadiusKilometers * @kilometerConversionToMilesFactor), 4)

The stored procedure is taking 4 or 5 seconds to run.

I've noticed that SQL Azure now supports the geometry data type .. (it didn't when I created the database).

So my question is ... would I experience a significant increase in the speed that my stored procedure would run that would make it worthwhile me investing the time it would take to change things over to using the geometry data type?

Thanks!

Steven

Answer 1

I can't give you the yes/no answer you are looking for, because I also have no experience with using the new spatial datatypes.

But what I can give you are some pointers:

First off: Your SP seems to just convert some geographical data. SQL Server 2008 has methods to do just that for you with the new geography datatype. Look at the OGC Methods on Geography Instances on the MSDN geography Data Type reference. So the new methods would at least give you the benefit of encapsulation.
Especially interesting for you must be the STDistance (STDistance (geography Data Type)) method, because it seems that this is what your SP is actually doing, calculating the distance from lat1, lon1 to lat2, lon2. I believe a built-in function is faster than a self-created function, but I wouldn't know without testing.

Using MS buzzwords, the spatial datatypes big plus is having spatial indexes. If you have some database with a lot of spatial data (your SP just converts some parameters), spatial indexes will bring you a performance increase. Or quoting from the spatial data whitepaper:

Performance of queries against spatial data is further enhanced by the inclusion of spatial index support in SQL Server 2008. You can index spatial data with an adaptive multi-level grid index that is integrated into the SQL Server database engine.

And then there are some articles suggesting the better performance of spatially indexed (is that a word?) data against normal indexes:

Performance is certainly enhanced... (from SQL Server 2008 Spatial Index Performance)

And then there is some nice graph comparing different kinds of holding spatial data against each other on the performance side: SQL Server 2008 Spatial - Performance of database calls?

So, to sum this up: Using spatial index WILL give you a performance increase. Whether using the pre-defined spatial methods will give you a significant performance increase, I don't know.

Bonus: To get you started with geography datatypes I suggest you read this blog post with lots of examples: Demystifying Spatial Support in SQL Server 2008.