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;
Now I am left with two tables -
My goal is first, to snap each trajectory to the nearest road and then, find the "mileage" walked along each road by
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?