[OSM-dev] Questions on Nominatim and administrative boundaries + osm2pgsql
jburgess777 at googlemail.com
Sun Dec 20 11:27:17 GMT 2009
On Sun, 2009-12-20 at 11:55 +0100, David MENTRE wrote:
> Dear fellow OSM hackers,
> We are working on improving MapOSMatic, especially for international support.
> We are using http://nominatim.openstreetmap.org to query OSM in a
> human-friendly way, which, indeed, is a wonderful tool!
> Now we would like to use its results to query our own copy of the DB
> (created with osm2pgsql). We found a way to do this, but we are not
> quite sure it's the "right way" (tm) to do it...
> For example, we query Nominatim for "Paris". One of the results is an
> administrative boundary limit (the one with osm_id 7444). For that
> entry, we would like to query our OSM DB to determine its
> administrative level.
> If we do:
> select * from planet_osm_line where osm_id=7444;
> then we don't get anything.
> However, when we do:
> select * from planet_osm_line where osm_id=-7444;
> then we get exactly what we need.
> Is this the right way to do it? Can we assume that, when nominatim
> returns an entry with class="boundary" type="administrative" and
> osm_type="relation", then we can safely query the planet_osm_line
> table with the _opposite_ of osm_id?
This is correct. When osm2pgsql creates entries from relations in the
line (or roads) table then it uses the negative of the relation ID in
the osm_id column.
> Now, we already know that it does not always work like this... For
> example, if we try this with the entry for "Paris, Kentucky, United
> States of America" (osm_id 130722), then it simply does not work at
> all. In fact, it doesn't seem to work at all with none of the other
> administrative boundary query results that nominatim returns us for
The boundary 130722 must be a complete closed ring, this means it ends
up in the polygon table instead. Again it has a negative ID because it
was generated from a relation:
gis=> select osm_id,name,boundary,admin_level from planet_osm_polygon
where osm_id in (130722,-130722);
osm_id | name | boundary | admin_level
-130722 | Paris | administrative | 8
I did consider putting a copy of these polygons into the line (or roads)
tables but in the end I decided against it.
The ways which make up the 7444 relation must have an error in them
somewhere which prevents osm2pgsql from forming a polygon.
> Should we instead query the planet_osm_rels table? Can we assume this
> table is always present in the database? If yes, how do we parse its
> fields to get the IDs to the other tables?
The _rels table is present if you use the --slim mode. The fields are a
little trickier to parse, you may get some inspiration from the queries
used by osm2pgsql otherwise you'll need to read up on how the postgresql
intarray feature works.
> Are there any documentation on the DB tables produced by osm2pgsql?
Not that I am aware of.
One thing that you might like to know is that the backend database for
nominatim is also generated using osm2pgsql, using the gazetteer mode.
> Thanks a lot in advance!
> david -- for MapOSMatic dev team
> dev mailing list
> dev at openstreetmap.org
More information about the dev