3

I have a number of pedestrian trajectories (green) that I would like to snap to the nearest road (brown). The roads are downloaded from OpenStreetMap and have been dissolved by name with

CREATE TABLE dissolved AS 
SELECT osm_roads.name, ST_Union(osm_roads.geom) FROM osm_roads GROUP BY osm_roads.name;

enter image description here

Now I am left with two tables - dissolved and trajectories

My goal is first, to snap each trajectory to the nearest road and then, find the "mileage" walked along each road by

ST_LENGTH(ST_Intersection(dissolved.geom, snapped.geom))

How would I perform the first part of the query - snapping each individual trajectory to the nearest road - with ST_Snap - and then group ST_LENGTH(ST_Intersection()) by road name?

  • It depends what you mean by snapping to the nearest road. The nearest point between a road and a trajectory might not be the most appropriate match. However, you could use the <-> operator in the ORDER BY clause with LIMIT 1, which should find you the nearest road to each trajectory. There is also a function, ST_ClosestPointOfApproach, but it looks like it might be useful in this context. – John Powell Nov 2 '17 at 13:18
  • Thanks, how would one write this in a query? I have tried CREATE TABLE snapped AS SELECT ST_Snap(trajectories, dissolved, ST_Distance(trajectories, dissolved)*1.25) from (SELECT september.wkb_geometry as trajectories, dissolved.st_union as dissolved) but this throws an error – the_darkside Nov 2 '17 at 13:21
  • In terms of how to write a query with <-> see this answer. Not a shameless plug, just easiest for me to find and read Paul Ramsey's article on lateral join. You can also use ST_Distance and ST_DWithin, but this needs a known value, so <-> with limit is more efficient. – John Powell Nov 2 '17 at 13:24
  • I don't think you need to use ST_Snap, again at this point, as you have already dissolved the roads -- it's all about distance, and ST_Snap is more useful when dealing with precision issues for intersect/intersection type queries. – John Powell Nov 2 '17 at 13:26
  • But if I don't use ST_Snap, how can I ultimately calculate the distance of each trajectory along certain roads? Each trajectory can be broken down to segments which are closest to a particular road. Then the lengths of segments can be added up and grouped by road, but I have no idea where to start – the_darkside Nov 2 '17 at 13:30
1

In this case ST_SNAP will give you bad results because your pedestrian trajectories are too far from the OSM lines.

One solution is to create a buffer around your OSM line and check wich pedestrian trajectories are fully contains by those buffers. If it's not already done, your OSM lines need to be splitted on each node of the network.

The buffer size need to be large enough in order to include all the trajectories.

For example:

CREATE TABLE line_osm_buffer
AS
(
    -- We create a buffer of, let's say 20 meters around the roads
    SELECT ID, st_buffer(geom,20) as geom
    FROM line_osm 
);



DROP TABLE IF EXISTS pedestrian_line;
CREATE TABLE pedestrian_line
as
(
SELECT ID, st_union(geom) as geom
FROM
    (
    SELECT t2.ID, t1.geom
    FROM line_osm_buffer t1, pedestrian_trajectories t2
    WHERE st_contains(tp.geom, tlm.geom)
    AND st_intersects(tp.geom, tlm.geom)
    ) as undissolved
GROUP BY ID
);



-- We drop the useless tables
DROP TABLE IF EXISTS line_osm_buffer;

It's simple and work well BUT there is 2 disadvantages:

  • Some small roads, adjacent to your main trajectories could be fully contains by the buffer and will appear in the result.

  • If your guy make 2 time the same circle, you lose the path of the second circle.

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.