SQL查询基于时间戳和经纬度的一天中行驶的距离

Question

我有一个表格，每行都有日期和纬度、经度值。我想要一个 sql 查询来计算一天中辛苦的距离。

说日期 2013-03-01 我想要旅行的总距离，

ID      DATE                  LAT     LONG    V_ID
---------------------------------------------------
123     2013-03-01 06:05:24   45.544  86.544   1
124     2013-03-01 06:15:17   45.676  86.676   1 
125     2013-03-01 06:25:24   46.544  86.544   2
126     2013-03-01 06:38:14   46.651  86.651   2
127     2013-03-02 07:12:04   46.876  86.876   1 
128     2013-03-02 10:38:14   46.871  86.871   1
129     2013-03-02 10:56:14   46.871  86.671   2
130     2013-03-02 15:28:02   46.243  86.871   2

为了计算距离我写了一个sql函数:

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

但我想要一天和一天 2013-03-01 的总行驶距离 我有前 四行 我想要 的总行驶距离2013-03-01

与此日期类似，2013-03-02 有最后四行，我如何计算这些行的距离。

Answer 1

您可以使用带有 ROW_NUMBER() 的自连接来获得这样的行进距离。

SQL Fiddle

查询

;WITH CTE AS 
(
SELECT *,CONVERT(DATE,[Date]) as tday,ROW_NUMBER()OVER(PARTITION BY CONVERT(DATE,[Date]) ORDER BY [Date] ASC) rn
FROM Travel
)
    SELECT T1.tday,SUM([dbo].[fnCalcDistanceKM](T1.lat,T2.lat,T1.long,T2.long)) as dist
    FROM CTE T1
    INNER JOIN CTE T2
        ON T1.tday = T2.tday
        AND T1.rn  = T2.rn -1
    GROUP BY T1.tday

输出

|       tday |               dist |
|------------|--------------------|
| 2013-03-01 | 129.40048639456964 |
| 2013-03-02 |  87.36216677343607 |