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!

asked 24 Oct '17, 14:47

tom_sen's gravatar image

tom_sen
11112
accept rate: 0%


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.

permanent link

answered 24 Oct '17, 18:50

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
69.0k806261079
accept rate: 24%

Thank you very much, Frederik! That code helped me a lot and thanks for sharing the general approach with me!

(31 Oct '17, 19:09) tom_sen
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×276
×202
×141
×116
×7

question asked: 24 Oct '17, 14:47

question was seen: 1,934 times

last updated: 31 Oct '17, 19:09

powered by OSQA