CODEWITHSUNDEEP
sql-serverdistance
James
James

Reputation: 91

Calculating distance between GPS data using SQL Server

I have tables in SQL Server management studio containing the location of individuals by date/time along several months. The tables have the following fields: AnimalID, Date/Time, Lat, Long, Global ID. I am trying to calculate and return the distance between each pair of points in order of its movement without manually entering in the lat and long each time.There are many posts here about calculating distance between two points but I'm trying to run a query that will calculate the distance between each pair in consecutive order. Some of my tables have hundreds of locations.

My values might look like:

        `MD001 10/9/2019 1:00:00PM 40.73995 -111.8739
         MD001 10/9/2019 6:00:00PM 40.75068 -111.8782
         MD001 10/9/2019 10:00:00PM 40.74900 -111.89100`

I want to know the distance between 1:00PM and 6:00PM and then from 6:00PM and 10:00PM, and so forth. I want to accomplish this in SQL Server so that I can query out outliers in the data. Your insight is much appreciated. I also do not want to create a new field in this table.

Upvotes: 0

Views: 658

Answers (1)

Jose Bagatelli
Jose Bagatelli

Reputation: 1409

The algorithm to calculate the distance between points is called Harvesine Formula

To calculate the distance between 2 points in SQL Server you have 2 options:

POINT 1 = 151.209030,-33.877814
POINT 2 = 144.971431, -37.808694

Option 1. You can do your own implementation of the harvesine formula:

select
    2 * 6371 * asin(sqrt(POWER((sin(radians((-37.808694 - -33.877814) / 2))),2) + cos(radians(-33.877814)) * cos(radians(-37.808694)) * POWER((sin(radians((144.971431 - 151.209030) / 2))),2)))

Note this will give you the distance in kilometer. This is defined by the multiplier 6371. To get the distance in miles replace 6371 by 3959

If you do a search on the harvesine formula + sql you can find more in depth details about this implementation.

Option 2.. Use SQL Server built-in functions. In order to do that you'll need to convert your lat and long columns to geography datatype and then use the STDistance function to calculate the actual distance.

The statement below should give you an idea to get started:

select
    cast('POINT(151.209030 -33.877814)' as geography).STDistance(cast('POINT(144.971431 -37.808694)' as geography)) as distance_in_meters,
    cast('POINT(151.209030 -33.877814)' as geography).STDistance(cast('POINT(144.971431 -37.808694)' as geography)) / 1000 as distance_in_km

The default result will be in meters.

Note there's a slight difference between these 2 options when they are applied to the same coordinates. So if you need precision then you might want to do some further investigation on why that is.

Upvotes: 1

Related Questions