Exporting Highway GPS coordinates (lat and long) to database / excel

Question

Dear community,

I spent soe time researching for my problem's solution, but unfortunately I couldn't find any.

So here's what I want to do:

I want to export all highways of German highways (done - with help of e.g. https://help.openstreetmap.org/questions/30692/export-only-roads)
From this OSM file now I want to export the coordinates (latitude and longitude) to export to a database and then to a file which can be edited with Excel or Stata. (osm2pgrs didn't work for me)
Final goal: I have a list of certain coordinates which I want to check whether they're crossed by a highway, e.g. if some of my points are situated on a highway. Thus, eventually I want to match my list with the exported highway coordinates

Does my approach makes sense? I'd be glad for any hints and tips, especially how to bring my OSM-file of highways into a list of all coordinates of the highway. Or, if this approach is not senseful at all, please let me know.

Thanks a lot in advance!

Answer 1

This is what spatial databases are for. You should load the data into PostGIS with osm2pgsql, create a table with your points in it in PostGIS, and then you can make SQL queries like

SELECT mypoint.name, highway.name
FROM mypoints, planet_osm_line highway
WHERE highway.highway IS NOT NULL
AND ST_DWITHIN(mypoint.geom, highway.geom, 100);

Provided your points are stored in the same projection as the highways this will give you points that are roughly within 100m of a highway, of course you can also reduce that to 10m or 2m.

The "manual" approach you sketched won't work because "the points of the highway", even if you were to extract them, can be hundreds of metres apart; even if one of your test points was bang in the middle of a highway, it could still be 100m away from the nearest "highway point". The spatial database does all the necessary calculations for you.

Exporting Highway GPS coordinates (lat and long) to database / excel

Follow this question

Related questions