如果您使用的是 SQL Server 2008 或更高版本,则可以使用 GEOGRAPHY 计算距离,例如:
DECLARE @lat1 DECIMAL(19,6) = 44.968046;
DECLARE @lon1 DECIMAL(19,6) = -94.420307;
DECLARE @lat2 DECIMAL(19,6) = 44.33328;
DECLARE @lon2 DECIMAL(19,6) = -89.132008;
SELECT GEOGRAPHY::Point(@lat1, @lon1, 4326).STDistance(GEOGRAPHY::Point(@lat2, @lon2, 4326));
这使得问题变得非常简单?
对于用户的一组纬度/经度,您需要计算每个点之间的距离,然后返回最大距离。将所有内容放在一起,您可能可以像这样做:
DECLARE @UserGPS TABLE (
UserId INT,
GPSId INT,
Lat DECIMAL(19,6),
Lon DECIMAL(19,6));
INSERT INTO @UserGPS SELECT 1, 1, 44.968046, -94.420307;
INSERT INTO @UserGPS SELECT 1, 2, 44.33328, -89.132008;
INSERT INTO @UserGPS SELECT 1, 3, 34.12345, -92.21369;
INSERT INTO @UserGPS SELECT 1, 4, 44.978046, -94.430307;
INSERT INTO @UserGPS SELECT 2, 1, 44.968046, -94.420307;
INSERT INTO @UserGPS SELECT 2, 2, 44.978046, -94.430307;
DECLARE @WorkTable TABLE (
UserId INT,
GPSIdFrom INT,
GPSIdTo INT,
Distance NUMERIC(19,6));
INSERT INTO
@WorkTable
SELECT
c1.UserId,
c1.GPSId,
c2.GPSId,
GEOGRAPHY::Point(c1.Lat, c1.Lon, 4326).STDistance(GEOGRAPHY::Point(c2.Lat, c2.Lon, 4326))
FROM
@UserGPS c1
INNER JOIN @UserGPS c2 ON c2.UserId = c1.UserId AND c2.GPSId > c1.GPSId;
WITH MaxDistance AS (
SELECT
UserId,
MAX(Distance) AS Distance
FROM
@WorkTable
GROUP BY
UserId)
SELECT
w.UserId,
g1.GPSId,
g1.Lat,
g1.Lon,
g2.GPSId,
g2.Lat,
g2.Lon,
md.Distance AS MaxDistance
FROM
MaxDistance md
INNER JOIN @WorkTable w ON w.UserId = md.UserId AND w.Distance = md.Distance
INNER JOIN @UserGPS g1 ON g1.UserId = md.UserId AND g1.GPSId = w.GPSIdFrom
INNER JOIN @UserGPS g2 ON g2.UserId = md.UserId AND g2.GPSId = w.GPSIdTo;
结果如下:
UserId GPSId Lat Lon GPSId Lat Lon MaxDistance
1 3 34.123450 -92.213690 4 44.978046 -94.430307 1219979.460185
2 1 44.968046 -94.420307 2 44.978046 -94.430307 1362.820895
现在,我对你所持有的数据做出了很多假设,因为你的问题中没有关于此的详细信息。你可能需要在一定程度上进行调整?