Extending SYS.Geometry to Utilize Temporal Data
We often face a task of modeling temporal data in SQL Server and writing an easy query to achieve the requirement.
Using date and time data types, a period requires two fields to model the time dimension - a beginning time and ending time. Including a third time column, representing episode begin, makes writing sequential and contiguous time based queries easier. An episode is the entire duration of an event. A 60 minute football game is an episode, where each play is a period; each of the plays/periods will have the same episode beginning time.
Writing queries and assertions becomes difficult; how can descriptive and quickly understood queries be written?
The .NET Geometry datatype includes the methods for creation and comparing points, lines, and polygons. Temporal data, converted to geometric data, can then be easily compared: an instance becomes a point, a duration becomes a line, and a Effective/Transaction duration becomes a closed polygon.
Start with a base time, this represents the origin and all user entered time will be compared to this time. Use DATEDIFF to convert the date and time to a vector - did the beginning of the period begin before or after the base time and how far away did the period begin compared to the base time. Converting two instances to points creates a line and the period has been converted from time to geography. When using effective and transaction time, the geometry becomes an area.
Here is a short, simple example using effective and transaction time:
DECLARE @t TABLE
(
[Id]
BIGINT NOT
NULL,
[effBeg]
DATE NOT
NULL DEFAULT(SYSDATETIME()),
[effEnd]
DATE NOT
NULL DEFAULT(CONVERT(DATE, '2050-12-31')),
[txnBeg]
DATE NOT
NULL DEFAULT(SYSDATETIME()),
[txnEnd]
DATE NOT
NULL DEFAULT(CONVERT(DATE, '2050-12-31')),
[effBegPt]
AS CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effBeg])),
[effEndPt]
AS CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effEnd])),
[txnBegPt]
AS CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnBeg])),
[txnEndPt]
AS CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnEnd])),
[eff]
AS 'LINESTRING('+ '0 ' + CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effBeg])) + ', '
+ '0 ' + CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effEnd])) + ')',
[txn]
AS 'LINESTRING('+ CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnBeg])) + ' 0, '
+ CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnEnd])) + ' 0)',
[EffTxn]
AS 'POLYGON(('
+
CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnBeg]))
+ ' ' + CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effBeg]))
+ ', '
+
CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnEnd]))
+ ' ' + CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effBeg]))
+ ', '
+
CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnEnd]))
+ ' ' + CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effEnd]))
+ ', '
+
CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnBeg]))
+ ' ' + CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effEnd]))
+ ', '
+
CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [txnBeg]))
+ ' ' + CONVERT(VARCHAR, DATEDIFF(d, '2014-05-01', [effBeg]))
+ '))',
CHECK([effBeg] <= [effEnd]),
CHECK([txnBeg] <= [txnEnd])
);
INSERT INTO @t([Id],[effBeg], [effEnd], [txnBeg], [txnEnd]) VALUES (1, '2014-05-1', '2014-05-6', '2014-05-1', '2014-05-6');
INSERT INTO @t([Id],[effBeg], [effEnd], [txnBeg], [txnEnd]) VALUES (2, '2014-05-1', '2014-05-3', '2014-05-1', '2014-05-3');
INSERT INTO @t([Id],[effBeg], [effEnd], [txnBeg], [txnEnd]) VALUES (3, '2014-05-5', '2014-05-6', '2014-05-5', '2014-05-6');
INSERT INTO @t([Id],[effBeg], [effEnd], [txnBeg], [txnEnd]) VALUES (4, '2014-05-2', '2014-05-4', '2014-05-2', '2014-05-4');
SELECT
a.*,
SYS.GEOMETRY::STPolyFromText(a.[EffTxn], 0)
[a_EffTxn]
FROM @t
a
INNER JOIN @t b
ON
SYS.GEOMETRY::STPolyFromText(b.[EffTxn], 0).STContains(SYS.GEOMETRY::STPolyFromText(a.[EffTxn], 0)) = 1
WHERE a.Id
<> 1
AND
b.Id = 1;
SELECT
a.*,
SYS.GEOMETRY::STPolyFromText(a.[EffTxn], 0)
[a_EffTxn]
FROM @t
a
INNER JOIN @t b
ON
SYS.GEOMETRY::STPolyFromText(b.[EffTxn], 0).STIntersects(SYS.GEOMETRY::STPolyFromText(a.[EffTxn], 0)) = 1
WHERE b.Id
= 2;