NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

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

asked 26 Nov '12, 07:50

JuZeeMan's gravatar image

JuZeeMan
41336
accept rate: 0%


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).

permanent link

answered 26 Nov '12, 09:41

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

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:

×710
×235
×165
×118
×11

question asked: 26 Nov '12, 07:50

question was seen: 6,730 times

last updated: 26 Nov '12, 09:41

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum