From a301a87e65ee9845804fc46f6466fbdec95be7b2 Mon Sep 17 00:00:00 2001 From: Itz-Agasta Date: Fri, 14 Aug 2026 12:54:37 +0530 Subject: [PATCH] Update the database layout documentation The place table holds a single row per OSM object now, with a categories column that carries all of its main tags. Document that, the categories column on placex and the tables that were added since the page was last touched: place_entrance, placex_entrance, place_associated_street and location_area_country. The place_classtype tables are gone, so drop them from the list of auxiliary tables. Also bring the diagrams back in line with the schema. search_name lost its search_rank column a while ago and location_postcodes gained is_area, while its centroid was missing from the diagram because of a typo. --- docs/develop/Database-Layout.md | 67 ++++++++++++++++++++++---- docs/develop/osm2pgsql-tables.plantuml | 19 ++++++++ docs/develop/osm2pgsql-tables.svg | 33 +++++++++++-- docs/develop/search-tables.plantuml | 20 ++++++-- docs/develop/search-tables.svg | 38 ++++++++++----- 5 files changed, 147 insertions(+), 30 deletions(-) diff --git a/docs/develop/Database-Layout.md b/docs/develop/Database-Layout.md index b468b3d1..9b1b80d0 100644 --- a/docs/develop/Database-Layout.md +++ b/docs/develop/Database-Layout.md @@ -11,7 +11,7 @@ The import process creates the following tables: The `planet_osm_*` tables are the usual backing tables for OSM data. Note that Nominatim uses them to look up special relations and to find nodes on -ways. Apart from those the osm2pgsql import produces three tables as output. +ways. Apart from those the osm2pgsql import produces five tables as output. The **place_postcode** table collects postcode information that is not already present on an object in the place table. That is for one thing @@ -54,18 +54,48 @@ has the following fields: * `extratags` - collection of additional interesting tags that are not directly relevant for searching * `geometry` - geometry of the object (in WGS84) + * `categories` - all principal tags of the object, each one as a hierarchical + label of the form `osm..` -A single OSM object may appear multiple times in this table when it is tagged -with multiple tags that may constitute a principal tag. Take for example a +An OSM object appears at most once in this table, even when it is tagged with +more than one tag that may constitute a principal tag. Take for example a motorway bridge. In OSM, this would be a way which is tagged with -`highway=motorway` and `bridge=yes`. This way would appear in the `place` table -once with `class` of `highway` and once with a `class` of `bridge`. Thus the -*unique key* for `place` is (`osm_type`, `osm_id`, `class`). +`highway=motorway` and `bridge=yes`. This way gets a single row in the `place` +table with `categories` of `{osm.highway.motorway, osm.bridge.yes}`. The +*unique key* for `place` is therefore (`osm_type`, `osm_id`). + +The `class` and `type` columns still hold a single principal tag, the one that +Nominatim uses to classify and rank the place. When an object has more than one +principal tag, then the alphabetically first key/value pair wins. Tags that are +only used as a fallback (see [Import styles](../customize/Import-Styles.md#main-tags)) +contribute a category but never become `class` and `type` unless they are the +only principal tag of the object. How raw OSM tags are mapped to the columns in the place table is to a certain degree configurable. See [Customizing Import Styles](../customize/Import-Styles.md) for more information. +The **place_entrance** table collects the nodes that are tagged as an entrance +of a building or another feature. Nominatim does not make them searchable but +returns them together with the enclosing place. The table has the following +fields: + + * `osm_id` - original OSM ID of the node + * `type` - value of the `entrance` tag + * `extratags` - any other tags of the entrance that may be interesting + * `geometry` - position of the node (in WGS84) + +The **place_associated_street** table saves the members of +[associatedStreet relations](https://wiki.openstreetmap.org/wiki/Relation:associatedStreet). +They are used to find the street a housenumber belongs to when no `addr:street` +tag can be matched. The table has the following fields: + + * `relation_id` - OSM ID of the relation + * `member_type`, `member_id` - reference to the OSM object that is a member + of the relation + * `member_role` - role of the member within the relation, usually `house` + or `street` + ### Search tables The following tables carry all information needed to do the search: @@ -102,8 +132,21 @@ additional columns: * `token_info` - a dummy field used to inject information from the tokenizer into the indexing process +The `categories` column is copied from the place table. It is an array of +`ltree` values, so that a search for a category can use the containment +operator `<@` and match all descendants of a category with a single comparison. +The combined index `idx_placex_centroid_categories` over `centroid` and +`categories` backs the search for POIs of a given category around a point. + For implementation details, see the SQL definition in `lib-sql/tables/placex.sql` and the SQLAlchemy schema in `src/nominatim_api/sql/sqlalchemy_schema.py`. +The **placex_entrance** table holds the entrances that could be assigned to a +place, that is all entrance nodes that are part of the way of a place. The +columns have the same meaning as in `place_entrance` with the exception of: + + * `place_id` - reference to the place the entrance belongs to + * `location` - position of the entrance node + The **location_property_osmline** table is a special table for [address interpolations](https://wiki.openstreetmap.org/wiki/Addresses#Using_interpolation). The columns have the same meaning and use as the columns with the same name in @@ -188,6 +231,10 @@ selected subset of places: All other columns reflect their counterpart in the placex table. +The **location_area_country** table is not partitioned. It caches the +geometries of the country boundaries found in the data and is used to determine +the country a place is located in. + ## Static data tables Nominatim also creates a number of static tables at import: @@ -206,7 +253,9 @@ Finally there are some table for auxiliary data: * `location_property_tiger` - saves housenumber from the Tiger import. Its layout is similar to that of `location_propoerty_osmline`. - * `place_class_*` tables are helper tables to facilitate lookup of POIs - by their class and type. They exist because it is not possible to create - combined indexes with geometries. + * `import_polygon_error` - logs objects whose geometry was too broken to be + processed during an update + * `import_polygon_delete` - logs deletions of very large areas, which + Nominatim refuses to apply automatically, see + [Maintenance](../admin/Maintenance.md#removing-large-deleted-objects) diff --git a/docs/develop/osm2pgsql-tables.plantuml b/docs/develop/osm2pgsql-tables.plantuml index 8bb7e444..c78bfaac 100644 --- a/docs/develop/osm2pgsql-tables.plantuml +++ b/docs/develop/osm2pgsql-tables.plantuml @@ -33,6 +33,21 @@ map place { extratags => HSTORE admin_level => SMALLINT geometry => GEOMETRY + categories => LTREE[] +} + +map place_entrance { + osm_id => BIGINT + type => TEXT + extratags => HSTORE + geometry => GEOMETRY +} + +map place_associated_street { + relation_id => BIGINT + member_type => TEXT + member_id => BIGINT + member_role => TEXT } map place_postcode { @@ -58,8 +73,12 @@ planet_osm_ways -[hidden]> planet_osm_rels planet_osm_ways -[hidden]-> place place -[hidden]-> place_postcode place -[hidden]-> place_interpolation +place_postcode -[hidden]-> place_entrance +place_interpolation -[hidden]-> place_associated_street planet_osm_nodes::id <- planet_osm_ways::nodes planet_osm_nodes::id <- place_interpolation::nodes +planet_osm_nodes::id <- place_entrance::osm_id +planet_osm_rels::id <- place_associated_street::relation_id @enduml diff --git a/docs/develop/osm2pgsql-tables.svg b/docs/develop/osm2pgsql-tables.svg index 15a77aff..6b8dd64b 100644 --- a/docs/develop/osm2pgsql-tables.svg +++ b/docs/develop/osm2pgsql-tables.svg @@ -1,11 +1,15 @@ -planet_osm_nodesidBIGINTlatINTlonINTplanet_osm_waysidBIGINTnodesBIGINT[]tagsTEXT[]planet_osm_relsidBIGINTpartsBIGINT[]membersTEXT[]tagsTEXT[]way_offSMALLINTrel_offSMALLINTplaceosm_typeCHAR(1)osm_idBIGINTclassTEXTtypeTEXTnameHSTOREaddressHSTOREextratagsHSTOREadmin_levelSMALLINTgeometryGEOMETRYplace_postcodeosm_typeCHAR(1)osm_idBIGINTpostcodeTEXTcountry_codeTEXTcentroidGEOMETRYgeometryGEOMETRYplace_interpolationosm_idBIGINTtypeTEXTaddressHSTOREnodesBIGINT[]geometryGEOMETRY word::word_id search_name::nameaddress_vector --> word::word_id @@ -99,5 +110,6 @@ search_name::nameaddress_vector --> word::word_id place_addressline -[hidden]> location_property_osmline search_name -[hidden]> place_addressline location_property_osmline -[hidden]-> location_postcodes +location_postcodes -[hidden]-> placex_entrance @enduml diff --git a/docs/develop/search-tables.svg b/docs/develop/search-tables.svg index a8842331..f783c025 100644 --- a/docs/develop/search-tables.svg +++ b/docs/develop/search-tables.svg @@ -1,12 +1,14 @@ -placexplace_idBIGINTosm_typeCHAR(1)osm_idBIGINTclassTEXTtypeTEXTnameHSTOREaddressHSTOREextratagsHSTOREadmin_levelSMALLINTpartitionSMALLINTgeometry_sectorINTparent_place_idBIGINTlinked_place_idBIGINTimportanceDOUBLErank_searchSMALLINTrank_addressSMALLINTwikipediaTEXTcountry_codeVARCHAR(2)housenumberTEXTpostcodeTEXTindexed_statusSMALLINTindexed_dateTIMESTAMPcentroidGEOMETRYgeometryGEOMETRYsearch_nameplace_idBIGINTimportanceDOUBLEsearch_rankSMALLINTaddress_rankSMALLINTname_vectorINT[]nameaddress_vectorINT[]country_codeVARCHAR(2)centroidGEOMETRYwordword_idINTword_tokenTEXT...location_property_osmlineplace_idBIGINTosm_idBIGINTtypeTEXTstartnumberINTendnumberINTstepintaddressHSTOREgeometry_sectorINTparent_place_idBIGINTcountry_codeVARCHAR(2)postcodetextindexed_statusSMALLINTindexed_dateTIMESTAMPlinegeoGEOMETRYplace_addresslineplace_idBIGINTaddress_place_idBIGINTdistanceDOUBLEcached_rank_addressSMALLINTfromareaBOOLEANisaddressBOOLEANlocation_postcodesplace_idBIGINTosm_idBIGINTpostcodeTEXTcountry_codeTEXTparent_place_idBIGINTrank_searchSMALLINTindexed_statusSMALLINTindexed_dateTIMESTAMPgeometryGEOMETRY