获取一组GPS坐标中的最大距离

Question

获取一组GPS坐标中的最大距离

5

我有一个包含多个GPS坐标的数据库。我知道如何从数据库中的任何一个坐标计算给定纬度/经度到该坐标的距离，但我想做的基本上是查看一组行的坐标，并获取最远的两行。如果我能在SQL中完成这个任务就太好了，但如果必须在我的应用程序代码中完成也可以。以下是我计算两点之间距离的方法：

ROUND(( 3960 * acos( cos( radians( :lat ) ) *
cos( radians( p.latitude ) ) * cos( radians(  p.longitude  ) - radians( :lng ) ) +
sin( radians( :lat ) ) * sin( radians(  p.latitude  ) ) ) ),1) AS distance

我们的目标是查看特定用户的GPS数据，并确保他们没有在全国范围内大幅移动。用户的所有坐标应该最多只相差几英里。如果坐标分布在全国各地，则表示我们的系统存在恶意活动。因此，我希望能够快速浏览特定用户的数据，并知道他们已经到达的最远距离。

我考虑过分别对纬度和经度运行Max / Min，并设置内部阈值来确定可接受的范围。也许这样做更简单，但如果可以实现我在第一部分提出的要求，那将是最好的。

- Leeish

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Richard Hansell · Accepted Answer

如果您使用的是 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, --the user
    GPSId INT, --the incrementing unique id associated with this GPS reading (could link to a table with more details, e.g. time, date)
    Lat DECIMAL(19,6), --lattitude
    Lon DECIMAL(19,6)); --longitude
INSERT INTO @UserGPS SELECT 1, 1, 44.968046, -94.420307; --User #1 goes on a very long journey
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; --User #2 doesn't get far
INSERT INTO @UserGPS SELECT 2, 2, 44.978046, -94.430307;

--Make a working table to store the distances between each set of co-ordinates
--This isn't strictly necessary; we could change this into a common-table expression
DECLARE @WorkTable TABLE (
    UserId INT, --the user
    GPSIdFrom INT, --the id of the first set of co-ordinates
    GPSIdTo INT, --the id of the second set of co-ordinates being compared
    Distance NUMERIC(19,6)); --the distance

--Get the distance between each and every combination of co-ordinates for each user
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;
--Note this is a self-join, but single-tailed.  So we compare each set of co-ordinates to each other set of co-ordinates for a user
--This is handled by the "c2.GPSID > c1.GPSId" in the JOIN clause
--As an example, say we have three sets of co-ordinates for a user
--We would compare set #1 to set #2
--We would compare set #1 to set #3
--We would compare set #2 to set #3
--We wouldn't compare set #3 to anything (as we already did this)

--Determine the maximum distance between all the GPS co-ordinates per user
WITH MaxDistance AS (
    SELECT
        UserId,
        MAX(Distance) AS Distance
    FROM
        @WorkTable
    GROUP BY
        UserId)
--Report the results
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

现在，我对你所持有的数据做出了很多假设，因为你的问题中没有关于此的详细信息。你可能需要在一定程度上进行调整？