4

I am querying the intersection of a path (LinestringZM) through a region (MultiPolygon) but the ST_Intersection returns only the Lat/Long/Alt coordinates and I need to recover time, stored in the M values.

SELECT path.name AS path_name,
  ST_Length(path.geom::geography) AS distance,
  ST_Length(geography(ST_Intersection(path.geom,region.geom))) AS region_distance,
  ST_M(ST_EndPoint(path.geom)) - ST_M(ST_StartPoint(path.geom)) AS time,
  ST_M(ST_EndPoint(ST_Intersection(path.geom,region.geom))) - ST_M(ST_StartPoint(ST_Intersection(path.geom,region.geom))) AS region_time
  region.name AS region_name
FROM path INNER JOIN region ON ST_Intersects(path.geom, region.geom) 
ORDER BY vehicle_name, region_distance DESC;

How can I get the exact elapsed time within a region? (the above region_time is null because M coordinate is dropped. I was thinking of using LATERAL select to get the equivalent matching segment of the original path like so: ST_SharedPaths(path.geom,rel.intersection_path) ... LATERAL (SELECT ST_Intersection(path.geom,region.geom) AS intersection_path) AS rel

But that gives me ERROR: GEOSSharedPaths: TopologyException: found non-noded intersection between LINESTRING (...

I'm using PostGIS 2.4 and PostgreSQL 10.1 beta, in case it matters.

2
  • 2
    FROM path, region WHERE ST_Intersects(path.geom, region.geom) is older syntax that shouldn't be used. It was replaced over 20 years ago with FROM path INNER JOIN region ON ST_Intersects(path.geom, region.geom) Jan 31, 2018 at 0:16
  • corrected, thanks -- it appears to run the query faster with explicit inner join
    – sventechie
    Jan 31, 2018 at 1:49

1 Answer 1

3

I use several functions to do the deed which you can grab here https://github.com/pauldzy/pg_dz_lrs/blob/master/Functions/LRS_INTERSECTION.sql

We could log an enhancement but I believe all the tepid M support derives from the underlying GEOS implementation.

Cheers, Paul

2
  • Thanks, I ended up approximating the time with number of data points, since they're collected at regular intervals, but that looks like it might be useful.
    – sventechie
    Mar 9, 2018 at 0:48
  • OSGEO M Values support was merged to a branch and left there awaiting performance analysis and bug fixing, in case someone wants to have a go at it. github.com/OSGeo/geos/pull/45
    – sventechie
    Mar 28, 2018 at 22:23

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.