从GPS坐标组中获取最大距离

So I have a database with multiple rows of GPS coordinates. I know how to calculate the distance from a given lat/lng from any one of them in the database, but what I want to do basically is look at the coordinates of a set of rows and get the two rows that are farthest apart. I'd love it if I could do this in SQL, but if I have to do it in my application code that would work to. Here is what I am doing to calculate the distance between two points:

所以我有一个包含多行GPS坐标的数据库。我知道如何计算数据库中任意一个给定lat / lng的距离，但我想要做的基本上是查看一组行的坐标并获得最远的两行。我喜欢它，如果我可以在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

What we are trying to do is, look at GPS data for a specific user and make sure they aren't moving wildly all over the country. All the coordinates for a user should be within a couple miles at most of each other. A flag that there is malicious activity in our system is if the coordinates are all over the country. So I'd like to be able to quickly run through the data for a spcicic user and know what is the max distance they have been.

我们要做的是，查看特定用户的GPS数据，并确保他们不会在全国范围内疯狂地移动。用户的所有坐标应彼此相距几英里。如果坐标遍布全国，我们系统中存在恶意活动的标志。所以我希望能够为spcicic用户快速浏览数据，并知道他们的最大距离是多少。

I thought about just running a Max/Min on the lat and lng separately and set an internal threshold for what is acceptable. And maybe that is easier, but if what I asked in the first part is possible, that would be best.

我想在lat和lng上分别运行Max / Min并设置一个可接受的内部阈值。也许这更容易，但如果我在第一部分中提出的问题是可能的，那将是最好的。

1 个解决方案

#1

If you have SQL Server 2008 or later then you can use GEOGRAPHY to calculate the distance, e.g.:

如果您有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));

This makes the problem pretty trivial?

这使问题相当微不足道？

For a set of lats/ longs for a user you would need to calculate the distance between each set and then return the highest distance. Putting this all together, you could probably do something like this:

对于用户的一组lats / long，您需要计算每组之间的距离，然后返回最高距离。把这一切放在一起，你可能会做这样的事情：

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;

Results are:

结果是：

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

Now I made a LOT of assumptions about what data you are holding as there was no information about the detail of this in your question. You would probably need to adapt this to some degree?

现在我对你持有的数据做了很多假设，因为在你的问题中没有关于这个细节的信息。你可能需要在某种程度上适应这个？

#1