2

I retreived europe.osm database with osm2pgsql. And now I try to exploit it. Is there a way to get the state, country, region, from a way like a town hamlet locality or city?

edit
http://wiki.openstreetmap.org/wiki/Nominatim
https://github.com/twain47/Nominatim

databases osm format :
http://download.geofabrik.de/osm/europe/
here is the database schema :
http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema

scai
  • 20,297
  • 4
  • 56
  • 72
Christophe Debove
  • 6,088
  • 20
  • 73
  • 124
  • I think when, I'm asking help with open street map database, it's for people who are familiar with this opensource database, howerver I will add 2 link with the doc reference.... So I aware that "Stack Overflow is not my personal research assistant" and be sure I looked for the solution everywhere I could, I only hope someone in the whole comunity got my problem and found the solution. isn't the ways that work? I'm not asking people look for me..... but just give me some elements (if they have to) continue. – Christophe Debove Apr 23 '12 at 12:28
  • @casperOne, I don't understand why this question is closed, I clearly understood what he wanted the first time I read the question, just because you are not familiar with openstreetmap data, that doesn't mean you should close it – Paco Valdez Apr 25 '12 at 14:23
  • @pacofvf Yeah, regarding the "familiarity with openstreetmap data", that argument is irrelevant to why this was closed. Also, you should familiarize yourself more with the FAQ and with Meta; it's clear that no research effort went into this question. In those situations, the common close reason is "not a real question". – casperOne Apr 25 '12 at 14:25
  • @casperOne well I think you're right, actually in the last link there is an example of how to retrieve data of osm data. – Paco Valdez Apr 25 '12 at 14:35

2 Answers2

1

Well , I didn't downloaded your data but I already have openstreetmaps data of almost all latin america loaded in my database, I'm sure it will do.

This is an example of the nearest state of a city :

select distinct on (a.name) 
   a.name as city,
   b.name as state,
   min(St_Distance(b.way, a.way)) as d 
from planet_osm_point as a, 
  (SELECT name, way, place from planet_osm_point WHERE place='state') as b 
where a.place='city' group by 1,2 order by 1 asc;

But this is not accurate, its supposed that you can do a better guess with polygons table:

select a.name as city,b.name as state,b.place 
from planet_osm_point as a, planet_osm_polygon as b 
where St_Contains(b.way, a.way) 
   and a.place='city'; -- and b.place='state' -- my polys table aren't tagged properly so I ignored polys.place

Hope it works :).

Paco Valdez
  • 1,915
  • 14
  • 26
1

I presume you have a full Nominatim installation as described on the installation page not just an osm2pgsql import.

Doing SQL queries on the database directly is a bit involved. You can get administrative information much easier by sending reverse requests directly to your local installation. If you have the OSM id of your way, send something like: http://your.local.nominatim.server/reverse?osm_type=W&osm_id= and process the XML returned.

If you still want to go down the SQL road, have a look at the placex and place_addressline tables. The former contains all OSM objects and the latter the relationship between them.

Sachin Mhetre
  • 4,465
  • 10
  • 43
  • 68