3

I have a database of aircraft positions and I'm trying to develop a way to recreate what airspace looked like at a time T. So my idea was to store each pair of positions as a LINESTRING M(lon1 lat1 t1, lon2 lat2 t2) and do a query like:

SELECT ST_3dIntersection(s.segment, 'POLYGON M ((-88.346 27.5 1598331600000, -88.346 41.699 1598331600000, -62.155 41.699 1598331600000, -62.155 41.699 1598331600000, -88.346 27.5 1598331600000))'::geometry) itsc
FROM segments s
WHERE ST_3DIntersects(s.segment, 'POLYGON M ((-88.346 27.5 1598331600000, -88.346 41.699 1598331600000, -62.155 41.699 1598331600000, -62.155 41.699 1598331600000, -88.346 27.5 1598331600000))'::geometry)
LIMIT 10

to get an approximated POINT M(lon lat T) of each aircraft at time T, but Postgres promptly crashes with:

realloc(): invalid old size
2021-08-24 23:13:12.790 GMT [3998987] LOG:  00000: server process (PID 3999192) was terminated by signal 6: Aborted

If I just query for ST_3DIntersects without using ST_3DIntersection it doesn't crash. I also tried with Z coordinates instead of M coordinates, same result. Is there a better way to go about this?

3

PostGIS has a small but powerful set of Linear Referencing functions to work with trajectories, utilizing the full potential of M values:

  • interpolating an isolated event (a MULTIPOINT M, or GEOMETRYCOLLECTION M if multiple events can be isolated) on the trajectory (LINESTRING M) at time T:
    SELECT ST_LocateAlong(s.segment, <T>) AS event
    FROM   segments AS s
    ;
    
    -- dump GEOMETRYCOLLECTION M / MULTI M contents; may be geometries of different dimensions
    SELECT dmp.geom AS event
    FROM   segments AS s,
           LATERAL ST_Dump(ST_LocateAlong(s.segment, <T>)) AS dmp
    ;
    
  • interpolating an event trajectory (a MULTILINESTRING M, or GEOMETRYCOLLECTION M if multiple event trajectories/events can be isolated) on the trajectory (LINESTRING M) between times T1 and T2:
    SELECT ST_LocateBetween(s.segment, <T1>, <T2>) AS trajectory
    FROM segments AS s
    ;
    
    -- dump GEOMETRYCOLLECTION M / MULTI M contents; may be geometries of different dimensions
    SELECT dmp.geom AS trajectory
    FROM   segments AS s,
           LATERAL ST_Dump(ST_LocateBetween(s.segment, <T1>, <T2>)) AS dmp
    ;
    
3
  • That could definitely be helpful, however after further testing, it seems that ST_3DIntersects does not work with M coordinates. Perhaps I should use ZM geometries (using the same value for both Z and M) so that I can take advantage of both ST_3DIntersects and ST_LocateAlong?
    – Thiatt
    Aug 25 at 12:18
  • I am not sure I understand what you want to achieve with your intersection - but yes, ST_3DIntersect[s|ion] won't work without Z ordinate. The above approach will get you the position on the LINESTRING Ms (trajectories) at a any given time T - much faster, and a lot simpler.
    – geozelot
    Aug 25 at 14:43
  • I want to retrieve all positions at time T inside a bounding box, so my idea was to calculate the intersection of a LINESTRING that represents a pair of positions at T-x and T+y with a POLYGON that represents a 3d plane of the bounding box at time T. So right now my query is something like: SELECT DISTINCT ON (s.id) s.*, ST_GeometryN(ST_LocateAlong(s.segment, T), 1) itpl FROM segments s WHERE ST_3DIntersects(s.segment, POLYGON((minX minY T, minX maxY T, maxX maxY T, maxX minY T, minX minY T))) ORDER BY s.id ASC
    – Thiatt
    Aug 25 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.