Retrieve the osm_id (from Postgres DB) if I have LAT/LON

Question

I see that you can use nominatim with an HTTP POST for this. However, I went to the trouble of loading the OSM database into postgres, and I want to take advantage of the speed of querying it from my DB, without an HTTP call.

Is there a SQL QUERY that can get the osm_ids associated with a specific latitude/longitude??

Thanks so much. Ive spend dozens of hours trying to figure this out!

Answer 1

This depends on how you have imported the data. For an osm2pgsql import, the query would look like this:

SELECT 
   osm_id
FROM
   planet_osm_point (or _line, or _polygon)
WHERE
   ST_DWITHIN(mypoint, way, mydistance);

What you put for mypoint and mydistance depends on what projection your data is in; if you have imported without -l, i.e. your data is in spherical mercator, you would have to put something like

ST_TRANSFORM(ST_SETSRID(ST_MAKEPOINT(mylon, mylat), 4326), 900913)

and for mydistance you would put the distance in spherical mercator units, i.e. roughly metres.

The query will then return all IDs within the given distance of the point.

Answer 2

0

I have a simliar problem trying to show some localities only having the lat long data availabe. Can you point me to a different resolution for this problem. I'm using mapserver on windows and openlayers. Thank you

permanent link

answered 13 Mar '14, 09:18

Victor1989
1
accept rate: 0%

This is a completely different question, and you'd be better off asking it as a separate question, fully explaining what you want to do and what your setup is.

(13 Mar '14, 10:01) Richard ♦

Retrieve the osm_id (from Postgres DB) if I have LAT/LON

Follow this question

Related questions