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

[Esteban Zimanyi]

---------- Forwarded message ---------
I have some ideas about how to calculate the intersections of tgeompoints
with "temporal polygons" (!), that is, a polygon with a Period, and would
like your opinion & advice.

Air traffic controllers vector aircraft away from their planned routes for
various reasons.  If the original flight plan would have taken the flight
through bad weather, then the controller had to vector the aircraft (that
is, the vectoring/maneuvering away from the planned route wasn't because of
traffic congestion).

I get raster weather radar data about storms every 15 minutes, and using
GDAL's Polygonize(), convert radar levels above a threshold (30 dBz) into
polygons.  I store those into PostGIS with a Period of "[now, next
interval)".

Currently I make linestrings of the planned flight path and of the flown
flight route through a particular airspace.  If they're about the same,
then there is no issue.  If the flown flight path is much longer than the
planned flight path, then the air traffic controller had to "work the
aircraft", and it would be nice to know why that was.

Here's my idea:  make tgeompoint arrays of the planned flight path and
somehow temporally intersect that with the various radar polygons over the
course of the flight.  The flight may span several 15-minute intervals
including several weather polygons, so I may have to union together all
intersections to eliminate overlaps that would end up duplicating
intersections.  The distance (or time) of the intersections would indicate
the about of weather that the flight would have encountered if it had
remained on its original planned flight path.

That way, when the airline asks "why was my aircraft late?", or the
controller's supervisor asks "what were you doing all day?", the controller
could answer "let me show you the scores calculated by Wendell's MobilityDB
project."
---------- End forwarded message ---------

Dear Wendell

Here is an idea of how this can be solved. Please let me know if I
understood correctly your message.

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(flight) AS
SELECT tgeompoint '[Point(0 0)@2000-01-01, Point(20 20)@2000-01-06]';

SELECT asText(tintersects(atPeriod(flight, p), storm)) FROM Storms S,
Flights F ORDER BY 1;
                                      astext
----------------------------------------------------------------------------------
 [f at 2000-01-02 00:00:00+01, f at 2000-01-03 00:00:00+01)
 {[f at 2000-01-03 00:00:00+01, f at 2000-01-04 00:00:00+01)}
 {[f at 2000-01-04 00:00:00+01, t at 2000-01-04 12:00:00+01, t at 2000-01-05
00:00:00+01)}
(3 rows)

Regards

Esteban
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mobilitydb-dev/attachments/20220702/013068a4/attachment.htm>