SQL Server / C＃如何找到与多边形相交的线串的DateTime

Question

我正在建立一个跟踪系统，想找到某人何时进入或离开某个区域（区域或层）。我们以5秒钟的间隔收集GPS坐标，并且将它们存储为经/纬度，并与读取日期时间一起存储在“地理”列中。区域和层的地理围栏以多边形的形式存储在“地理”列中。区域可以包含层，层可以包含子层。在图中，T3是子层。如果有人在T3中，那么他们也在T2中。右侧非常微弱的点集也是即将发生的情况的一个示例。人们将整天进出区域和层级。最终结果是看到一个活动列表上午6:00:00进入1区在上午6:05:00进入T2在上午7:13:12进入T3上午7:49:32退出T3等等。有没有办法找到某人进入/离开区域或层的点（图像中的绿色圆圈），并从GPS点表中获取读取日期时间？我已经使用STContains让所有人都陷入了地理围栏。我考虑过使用这种方法，通过读取日期时间进行排序，如果我找到一个0到1，则输入它们，反之，则返回1到0，则它们退出。我认为这是太多的循环，希望有更好的方法。我也尝试过STIntersection，但找不到将点与日期时间联系起来的方法。如果有人静止超过 5 秒，我将得到两个相同的点，并且坐标匹配效果不佳。看来DBGeography C＃类具有与SQL Server相同的功能。可以用C＃代替SQL吗？

Answer 1

您在这里遇到了很多挑战，但我认为它们都是可行的。

我认为您已经解决了第一个问题，即知道给定数据点位于哪些区域中。无论是STContains()或STIntersects()。

第二个是您实际上是在寻找基于时间的连续性集群。假设您具有可靠的数据收集，这也是可以解决的。一旦从上面获得了一组（人，地区，时间戳）元组，这就是一个空白和离岛的问题。玩具解决方案如下：

IF OBJECT_ID('tempdb.dbo.#observations') IS NOT NULL

    DROP TABLE #observations;

IF OBJECT_ID('tempdb.dbo.#regions') IS NOT NULL

    DROP TABLE #regions;

CREATE TABLE #observations (

    ObservationID INT NOT NULL IDENTITY,

        CONSTRAINT PK_Observations PRIMARY KEY CLUSTERED (ObservationID),

    PersonID      INT NOT null,

    Point         GEOMETRY NOT null,

    TS            DATETIME2(0) NOT NULL CONSTRAINT DF_Observations_TS DEFAULT SYSUTCDATETIME()

);

CREATE TABLE #regions (

    RegionID INT NOT NULL IDENTITY,

        CONSTRAINT PK_Regions PRIMARY KEY CLUSTERED (RegionID),

    Area GEOMETRY NOT NULL

);

INSERT INTO #regions

(

    Area

)

VALUES

( geometry::STGeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))', 0) ),

( geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0) ),

( geometry::STGeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))', 0) );

INSERT INTO #observations

(

    PersonID ,

    Point ,

    TS

)

VALUES

(   1 ,

    geometry::Point(0.5, 0.5, 0) ,

    '2018-01-01 00:00:00'

),

(   1 ,

    geometry::Point(1.5, 1.5, 0) ,

    '2018-01-01 00:00:05'

),

(   1 ,

    geometry::Point(2.5, 2.5, 0) ,

    '2018-01-01 00:00:10'

),

(   1 ,

    geometry::Point(3.5, 3.5, 0) ,

    '2018-01-01 00:00:15'

),

(   1 ,

    geometry::Point(4.5, 4.5, 0) ,

    '2018-01-01 00:00:20'

),

(   1 ,

    geometry::Point(0.5, 0.5, 0) ,

    '2018-01-01 01:00:00'

),

(   1 ,

    geometry::Point(1.5, 1.5, 0) ,

    '2018-01-01 01:00:05'

),

(   1 ,

    geometry::Point(2.5, 2.5, 0) ,

    '2018-01-01 01:00:10'

),

(   1 ,

    geometry::Point(3.5, 3.5, 0) ,

    '2018-01-01 01:00:15'

),

(   1 ,

    geometry::Point(4.5, 4.5, 0) ,

    '2018-01-01 01:00:20'

),

(   2 ,

    geometry::Point(3.5, 3.5, 0) ,

    '2018-01-01 00:00:00'

),

(   2 ,

    geometry::Point(3.5, 3.5, 0) ,

    '2018-01-01 00:00:05'

),

(   2 ,

    geometry::Point(3.5, 3.5, 0) ,

    '2018-01-01 00:00:10'

),

(   2 ,

    geometry::Point(3.6, 3.6, 0) ,

    '2018-01-01 00:00:15'

),

(   2 ,

    geometry::Point(4.5, 4.5, 0) ,

    '2018-01-01 00:00:20'

);

WITH cte AS (

    SELECT o.ObservationID,

           o.PersonID ,

           o.TS ,

           r.RegionID,

           (DATEDIFF(SECOND, '2017-01-01', o.ts)/5) - ROW_NUMBER() OVER (PARTITION BY o.PersonID, r.RegionID ORDER BY o.ts) AS gid,

           DATEDIFF(SECOND, '2017-01-01', o.ts)/5 AS diff,

           ROW_NUMBER() OVER (PARTITION BY o.PersonID, r.RegionID ORDER BY o.ts) AS rn

    FROM #observations AS o

    JOIN #regions AS r

        ON o.Point.STIntersects(r.Area) = 1

    --JOIN #timestamps AS ts

    --    ON ts.TS = o.TS

)

SELECT cte.PersonID, cte.RegionID, MIN(ts), MAX(ts)

FROM cte

GROUP BY cte.PersonID ,

         cte.RegionID,

         cte.gid;

诀窍（如果有的话）是意识到row_number()岛中每个成员的增量为1，并且对于相同的条件，（秒数）/ 5也应该增量为1。因此，对于在同一个岛中具有同等资格的行，它们之间的差异应该是恒定的。这给我们提供了方便的分组依据。