[Mobilitydb-dev] [EXTERNAL] PostgreSQL Version Support in MobilityDB

[Esteban Zimanyi]

Dear Wendell

Here is an alternative solution that computes a SINGLE temporal
Boolean for each flight determining whether or not it intersected a
storm.

DROP TABLE IF EXISTS Storms;
CREATE TABLE Storms(storm, p) AS
SELECT geometry 'Polygon((10 10,12 10,12 12,10 12,10 10))', period
'[2000-01-02, 2000-01-03)' UNION
SELECT geometry 'Polygon((12 12,16 12,16 16,12 16,12 12))', period
'[2000-01-03, 2000-01-04)' UNION
SELECT geometry 'Polygon((14 14,16 14,16 16,14 16,14 14))', period
'[2000-01-04, 2000-01-05)';

DROP TABLE IF EXISTS Flights;
CREATE TABLE Flights(No, flight) AS
SELECT 1, tgeompoint '[Point(0 0)@2000-01-01, Point(20 20)@2000-01-06]';

WITH Temp1(No, tinter) AS (
  SELECT No, merge(array_agg(tintersects(atPeriod(flight, p), storm)
ORDER BY 1))
  FROM Storms S, Flights F GROUP BY No ORDER BY No ),
Temp2(noStorm) AS (
  SELECT extent(p) FROM storms ),
Temp3(No, tinter) AS (
  SELECT No, tbool_seqset(false, flight::period - noStorm) FROM Flights, Temp2
)
SELECT No, merge(array_agg(tinter ORDER BY tinter))
FROM (SELECT * FROM Temp1 UNION SELECT * FROM Temp3) T
GROUP BY No
ORDER BY No;

no |                                                   merge
----+------------------------------------------------------------------------------------------------------------
  1 | {[f at 2000-01-01 00:00:00+01, t at 2000-01-04 12:00:00+01,
f at 2000-01-05 00:00:00+01, f at 2000-01-06 00:00:00+01]}

In the above query
* Temp1 computes for each flight the tintersects predicate with ANY storm
* Temp2 computes the period encompassing ALL storm periods
* Temp3 computes for each flight a temporal Boolean with value false
for all the periods in which there was NO storm
* The main query combines all the above

Regards

Esteban