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.

sventechie
  • 113
  • 7
  • 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) – Evan Carroll Jan 31 '18 at 00:16
  • corrected, thanks -- it appears to run the query faster with explicit inner join – sventechie Jan 31 '18 at 01:49

1 Answers1

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

pauldzy
  • 680
  • 3
  • 6
  • 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 09 '18 at 00: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. https://github.com/OSGeo/geos/pull/45 – sventechie Mar 28 '18 at 22:23