How to calculate distance between multiple points in SQL Server?

Question

I have table with data from GPS, e.g.: Latitude, Longitude, Time, UserId

How to aggregate total distance in specified time frame summing all distances by all points (ordered by time) and group data by user?

Thanks

Answer 1

If you are using SQL Server, you might be interested in using the geography data type so you can request the database using the dedicated method and geographical index.

The geography data type is available since SQL Server 2008, you can more information right here:

http://msdn.microsoft.com/en-us/library/cc280766.aspx

Once you've got the data into the geography column, you will be able to calculate the distance between two points using STDistance() methods, see the MSDN:

http://msdn.microsoft.com/en-us/library/bb933808.aspx

Here is an example where STDistance() is used to calculate the distance (with the geographical deformation) between two points returned in meters (international standard unit):

DECLARE @pointA geography;
DECLARE @pointB geography;
SET @pointA = geography::STGeomFromText('POINT(-122.34900 50)', 4326);
SET @pointB = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @pointA.STDistance(@pointB);

If you're using SQL Server 2005 (or if you want to avoid using the geography data type), you can take a look at the MsSQLSpatial project on CodePlex, available here: http://mssqlspatial.codeplex.com/wikipage?title=Features&referringTitle=Home