[Maposmatic-dev] Re: [OSM-dev] Questions on Nominatim and administrative boundaries + osm2pgsql

[Jon Burgess]

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
> "Paris".

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.
http://wiki.openstreetmap.org/wiki/Nominatim
http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/gazetteer/README.txt


> Thanks a lot in advance!
> Regards,
> 
> david -- for MapOSMatic dev team
> 
> _______________________________________________
> dev mailing list
> address@hidden
> http://lists.openstreetmap.org/listinfo/dev