let's assume a Linestring(Z)M holding a timestamp as measure variable with each point. Is there a way to easily query "on the relation of those timestamps between the points". For example to find segments where duration between two timestamps are larger than x minutes.

I am aware of the ST_LocateBetween function, where I could filter an M-geometry for a certain specified range. But that is more an absolute function and I am more looking at relative filters.

  • It is possible to do unspeakable things with generate_series and ST_PointN, or with ST_Dump, but I, too, would like to know if there's anything better... – Ture Pålsson Nov 5 '19 at 7:58

I think you can dump individual vertices as geoms using st_dumppoints, compute difference between consecutive rows M values using lag window function and then query that for the required difference. Something like this:

select * from 
    st_m(geom) - lag(st_m(geom)) over () diff, 
from ST_DumpPoints(ST_GeomFromText('LINESTRING M (0 0 0, 10 0 20, 12 0 40, 20 0 50, 21 0 70)'))) p
where diff > 10;

which gives you this:

  20|{2} |
  20|{3} |
  20|{5} |

Of course, you have to modify over() statement to accommodate your requirements - for example, partition by line ID to compute diff only within one line.

  • Works perfect! Thanks! – ASI Nov 5 '19 at 10:38

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.