您在這里遇到了很多挑戰(zhàn)，但我認(rèn)為它們都是可行的。
我認(rèn)為您已經(jīng)解決了第一個(gè)問題，即知道給定數(shù)據(jù)點(diǎn)位于哪些區(qū)域中。無論是STContains()或STIntersects()。
第二個(gè)是您實(shí)際上是在尋找基于時(shí)間的連續(xù)性集群。假設(shè)您具有可靠的數(shù)據(jù)收集，這也是可以解決的。一旦從上面獲得了一組（人，地區(qū)，時(shí)間戳）元組，這就是一個(gè)空白和離島的問題。玩具解決方案如下：
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;
訣竅（如果有的話）是意識(shí)到row_number()島中每個(gè)成員的增量為1，并且對(duì)于相同的條件，（秒數(shù)）/ 5也應(yīng)該增量為1。因此，對(duì)于在同一個(gè)島中具有同等資格的行，它們之間的差異應(yīng)該是恒定的。這給我們提供了方便的分組依據(jù)。
