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

I am building a tracking system and want to find when someone has entered or left an area (Zone or Tier). We are collecting GPS coordinates at 5 sec intervals and they are stored as both lat/long and in a geography column along with the reading date time. The geofences for Zones and Tiers are stored as polygons in a geography column. Zones can contain Tiers and Tiers can contains Sub-Tiers. In the picture, T3 is a sub-tier. If someone is in T3, they are also in T2. The very faint set of dots on the right is also an example of what will happen. People will be going in and out of Zones and Tiers all day long.

我正在建立一个跟踪系统,想要找到有人进入或离开某个区域(区域或层)的时间。我们以5秒的间隔收集GPS坐标,它们同时存储为纬度/经度和地理列以及读取日期时间。区域和层的地理围栏将作为多边形存储在地理列中。区域可以包含层,层可以包含子层。在图中,T3是一个子层。如果有人在T3,他们也在T2。右边非常微弱的点也是将要发生的事情的一个例子。人们将整天进出区域和层级。

The end result is to see a activity list

最终结果是查看活动列表

Entered Zone 1 at 6:00:00 am

上午6:00:00进入1区

Entered T2 at 6:05:00 am

上午6:05:00进入T2

Entered T3 at 7:13:12 am

上午7:13:12进入T3

Exited T3 at 7:49:32 am

上午7:49:32退出T3

etc.

Is there a way to find the points where someone enters/exits a Zone or Tier (green circles in image) and get the Reading Date Time from the GPS points table?

有没有办法找到有人进入/退出区域或层(图像中的绿色圆圈)并从GPS点表中获取阅读日期时间的点?

I have used STContains to get all people in a geofence. I have thought about using this approach, order by reading date time and if I find a 0 then 1, they entered, and conversely a 1 then 0, they exited. I am thinking this is too many loops and hoping there is a better way.

我使用STContains让所有人都在地理围栏中。我已经考虑过使用这种方法,通过阅读日期时间来排序,如果我找到0然后是1,他们就进入了,相反,1然后是0,他们退出了。我认为这是太多的循环,希望有更好的方法。

I have also tried STIntersection but I cant find a way to tie the points back to the date time. If someone is standing still for more than 5 seconds, I will get two identical points and matching on coordinates doesnt work well.

我也尝试过STIntersection,但是我找不到一种方法可以将点数与日期时间联系起来。如果某人静止超过5秒钟,我会得到两个相同的点,坐标上的匹配效果不佳。

It appears that the DBGeography C# class has the same functions as SQL server. Can this be done in C# instead of SQL?

似乎DBGeography C#类具有与SQL Server相同的功能。这可以用C#而不是SQL来完成吗?

1 个解决方案

#1

You've got a host of challenges here, but I think they're all doable.

你在这里遇到了许多挑战,但我认为他们都是可行的。

I think you've already solved the first which is to know which regions a given data point is inside of. Either STContains() or STIntersects().

我想你已经解决了第一个知道给定数据点在哪个区域内的问题。 STContains()或STIntersects()。

The second is that you're essentially looking for clusters of time-based contiguity. Assuming that you have reliable data collection, this is also solvable. Once you've got a set of (Person, Region, Timestamp) tuples (from above), it's a Gaps and Islands problem. A toy solution is presented below:

第二,你基本上是在寻找基于时间的连续集群。假设您有可靠的数据收集,这也是可以解决的。一旦你有了一组(Person,Region,Timestamp)元组(从上面开始),就会出现Gaps和Islands问题。玩具解决方案如下:

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;

The trick (if there is one) is realizing that row_number() increments by 1 for each member in an Island and that (number of seconds)/5 should also increment by 1 for the same criteria. So the difference of them should be constant for rows that qualify as equivalent in the same Island. That gives us a convenient value to group by.

技巧(如果有的话)意识到row_number()对于岛中的每个成员递增1,并且对于相同的标准,(秒数)/ 5也应该递增1。因此,对于在同一岛中具有同等资格的行,它们的差异应该是恒定的。这给了我们分组的便利价值。

#1