[GIS] Snap lines to nearest line in PostGIS

intersectionlinepostgissnapping

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?

Best Answer

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.

Related Question