[Mobilitydb-dev] intersection of tgeompoint with a geometry and period

[Wendell Turner]

Hello,

What is a good way to determine the temporal intersection of a tgeompoint and a
geometry with a time period?

(This is similar to the the issue that I mentioned in the note that
vicky_at_georepublic.de posted on Jun 13, but now using a linestring instead of
a polygon).

In air traffic control, there is often a need to establish a line-in-space for
a specific time period, and then identify the aircraft that crossed it during
that time period.  (What aircraft crossed the boundary between these two air
traffic control regions between 1300z and 1500z for every day last week?  How
does it compare to the number of aircraft that crossed that same boundary
between 1500z and 1700z?)

How can the tintersects(tgeompoint, geometry) be combined with the
atPeriod(tgeompoint, period) to find out if the crossing happened
"when the linestring was active"?

Is there a better way than using:
 SELECT
    tintersects(flown_path, boundary) as crossing_point,
    upper(period(atValue(tintersects(flown_path, boundary), TRUE))) as crossing_time

to get all geospatial intersections (with the times), then following that with:

WHERE crossing_time >= to_timestamp(interval_start, 'YYYY-MM-DD HH24:MI')
  AND crossing_time <= to_timestamp(interval_end,   'YYYY-MM-DD HH24:MI')
  AND tintersects(flown_path, boundary) is not null

Thank you,

Wendell

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mobilitydb-dev/attachments/20220701/8b80fecb/attachment.htm>