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
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
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
The below function gives distance between two geocoordinates in miles
create function [dbo].[fnCalcDistanceMiles] (@Lat1 decimal(8,4), @Long1 decimal(8,4), @Lat2 decimal(8,4), @Long2 decimal(8,4))
returns decimal (8,4) as
begin
declare @d decimal(28,10)
-- Convert to radians
set @Lat1 = @Lat1 / 57.2958
set @Long1 = @Long1 / 57.2958
set @Lat2 = @Lat2 / 57.2958
set @Long2 = @Long2 / 57.2958
-- Calc distance
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))
-- Convert to miles
if @d <> 0
begin
set @d = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
return @d
end
The below function gives distance between two geocoordinates in kilometres
CREATE FUNCTION dbo.fnCalcDistanceKM(@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END
Usage:
select [dbo].[fnCalcDistanceKM](13.077085,80.262675,13.065701,80.258916)
The below function gives distance between two geocoordinates in kilometres using Geography data type which was introduced in sql server 2008
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
SELECT @g.STDistance(@h);
Reference: Ref1,Ref2
I hope this helps