[GIS] the correct way to get road segments from OSM based on a route

openstreetmappostgispostgresql

I'm using a Postgresql+postgis database with OSM data (just the Netherlands). I would like to get a list of all road segments based on a set of LAT/LONG coordinates.

For my test I use (part of) a random GPX track which you can easily visualize on for example https://clydedacruz.github.io/openstreetmap-wkt-playground/:

LINESTRING(4.62925 52.66297,4.62873 52.661519999999996,4.62967 52.66099,4.62994 52.66038,4.63432 52.66063,4.63506 52.66077000000004,4.63619 52.661460000000005)

Below is my query:

select st_astext(st_transform(way, 4326)), highway from planet_osm_line straten where st_dwithin(straten.way, st_transform(ST_GeomFromText('LINESTRING(4.62925 52.66297,4.62873 52.661519999999996,4.62967 52.66099,4.62994 52.66038,4.63432 52.66063,4.63506 52.66077000000004,4.63619 52.661460000000005)', 4326), 3857), 0.1) and not straten.highway is null

This query returns 12 road segments of which one is the Torensduin street which isn't part of the actual route but is part of the results just because the linestring between two point crosses this street.

Road segment from the query result for the Torensduin street:

LINESTRING(4.62970101935771 52.660935187484,4.62952629703495 52.6608663721515,4.62933181177594 52.6606963765665)

Also this (nameless) street is added to the road segments but isn't part of the route:

LINESTRING(4.62970101935771 52.660935187484,4.62952629703495 52.6608663721515,4.62933181177594 52.660696376566506)

What I have tried so far is:

  1. Use MULTIPOINT instead of LINESTRING, but that query only returns a
    very limited list of road segments because not every road segment on
    the route has a point in the GPX track
  2. Use ST_INTERSECTS instead of ST_DWITHIN, but (obviously) with the same result.

I think I'm using the wrong approach with my query but haven't got a clue on what approach I should use to get the actual road segments.

So the question is, how can I get a 99,99% correct list of road segments based on a list of LAT/LON coordinates?

Best Answer

You would have to do some map-matching or routing to accomplish this.

For example you would pass in the route into OSRM (http://project-osrm.org/).

Which would do the map-matching, so it matches the route to nodes on the OSM network. Then you would extract those segments from your OSM network which have the matching start and end nodes.

So this would be the best way to do this.

But a more simplified way would be just within PostgreSQL. So your first query could limit the segments you have to check. Then you would loop through each segment of you input line and see if the OSM lines within your distance threshold match the orientation of the input line, and are within an acceptable Hausdorff Distance of the input line.

This should be able to remove slivers, and small cross streets.

Related Question