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

Question

我正在建立一个跟踪系统，想找到某人何时进入或离开某个区域（区域或层）。我们以5秒钟的间隔收集GPS坐标，并且将它们存储为经/纬度，并与读取日期时间一起存储在“地理”列中。区域和层的地理围栏以多边形的形式存储在“地理”列中。区域可以包含层，层可以包含子层。在图中，T3是子层。如果有人在T3中，那么他们也在T2中。右侧非常微弱的点集也是即将发生的情况的一个示例。人们将整天进出区域和层级。

最终结果是看到一个活动列表

1. 上午6:00:00进入1区

2. 在上午6:05:00进入T2

3. 在上午7:13:12进入T3

4. 上午7:49:32退出T3

5. 等等。

有没有办法找到某人进入/离开区域或层的点（图像中的绿色圆圈），并从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 cteGROUP BY cte.PersonID ,         cte.RegionID,         cte.gid;诀窍（如果有的话）是意识到row_number()岛中每个成员的增量为1，并且对于相同的条件，（秒数）/ 5也应该增量为1。因此，对于在同一个岛中具有同等资格的行，它们之间的差异应该是恒定的。这给我们提供了方便的分组依据。