What Schema/Tool to store OSM GPS node coordinates in database?

Question

I need to store data of an area (.osm file) to a DB, specifically road and intersection data and points along these roads, both including their GPS coordinates. The idea is to give the system a GPS coordinate and be able to relate that coordinate to a node or road (way?) within the area. (No map rendering is requires; strictly just the coordinate data)

I've tried loading an osm file to a PostGreSQL + PostGIS database using osm2pgsql, however I later found out that this method maps nodes and ways to lines and polygons...problem there :/

Could anyone suggest the right tool for the cause? Would PostGIS snapshot do the trick?

Note: This is a follow-up question to https://help.openstreetmap.org/questions/17787/right-importerdatabase-schema-to-store-osm-road-nodes-and-intersections-relations

Answer 1

Please explain in more detail why the osm2pgsql database doesn't work for you.

Of course it creates lines for way features but you can always get the coordinates of a way with st_astext(way) or similar, and you can find out all ways in the vicinity of a point with st_dwithin - for example

SELECT 
   name,highway,st_astext(way)
FROM
   planet_osm_line
WHERE
   highway IS NOT NULL
AND
   st_dwithin(way, 
      st_setsrid(st_makepoint(8.1234,49.1234),4326), .01)

This gives you all ways that touch within approx. 1km (0.01 degrees) of the point whose coordinates are given as a lon,lat pair. This works for a database imported with the -l (ell) flag. A database imported without that will have geometries stored in a different coordinate system, and will require something like

   st_dwithin(way, 
      st_transform(st_setsrid(st_makepoint(8.1234,49.1234),4326),900913), 1000)

(note that in this case the unit of distance is roughly metres, not degrees). To list the coordinates in degrees you would also want to use st_astext(st_transform(way,4326)) instead of just st_astext(way).

What Schema/Tool to store OSM GPS node coordinates in database?

Follow this question

Related questions