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 > [email protected] > http://lists.openstreetmap.org/listinfo/dev _______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
