Re: [OSM-dev] Questions on Nominatim and administrative boundaries + osm2pgsql
Hello Jon, Thank you for the detailed reply. We have made significant progress with your explanations. Regards, david -- for MapOSMatic team ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] Questions on Nominatim and administrative boundaries + osm2pgsql
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? 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. 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? Are there any documentation on the DB tables produced by osm2pgsql? Thanks a lot in advance! Regards, david -- for MapOSMatic dev team ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Questions on Nominatim and administrative boundaries + osm2pgsql
2009/12/20 David MENTRE dmen...@linux-france.org: 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. I think ways are positive and relations are negative, that's just what I've observed but I could be wrong. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Questions on Nominatim and administrative boundaries + osm2pgsql
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 dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Questions on Nominatim and administrative boundaries + osm2pgsql
On Sun, 2009-12-20 at 11:27 +, Jon Burgess wrote: 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? ... The ways which make up the 7444 relation must have an error in them somewhere which prevents osm2pgsql from forming a polygon. On closer inspection, the Paris boundary does form a complete closed polygon and is stored in the _polygon table as well: gis= select osm_id,name,boundary,admin_level from planet_osm_polygon where osm_id in (7444,-7444); osm_id | name |boundary| admin_level +---++- -7444 | Paris | administrative | 8 All boundaries which form a closed ring should have entries in the polygon table. Jon ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
