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:
- 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
- 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?