lib-sql/tables.sql

   1 -- SPDX-License-Identifier: GPL-2.0-only
   2 --
   3 -- This file is part of Nominatim. (https://nominatim.org)
   4 --
   5 -- Copyright (C) 2026 by the Nominatim developer community.
   6 -- For a full list of authors see the git log.
   7
   8 drop table if exists import_status;
   9 CREATE TABLE import_status (
  10   lastimportdate timestamp with time zone NOT NULL,
  11   sequence_id integer,
  12   indexed boolean
  13   );
  14 GRANT SELECT ON import_status TO "{{config.DATABASE_WEBUSER}}" ;
  15
  16 drop table if exists import_osmosis_log;
  17 CREATE TABLE import_osmosis_log (
  18   batchend timestamp,
  19   batchseq integer,
  20   batchsize bigint,
  21   starttime timestamp,
  22   endtime timestamp,
  23   event text
  24   );
  25
  26 GRANT SELECT ON TABLE country_name TO "{{config.DATABASE_WEBUSER}}";
  27
  28 DROP TABLE IF EXISTS nominatim_properties;
  29 CREATE TABLE nominatim_properties (
  30     property TEXT NOT NULL,
  31     value TEXT
  32 );
  33 GRANT SELECT ON TABLE nominatim_properties TO "{{config.DATABASE_WEBUSER}}";
  34
  35 drop table IF EXISTS location_area CASCADE;
  36 CREATE TABLE location_area (
  37   place_id BIGINT NOT NULL,
  38   keywords INTEGER[] NOT NULL,
  39   partition SMALLINT NOT NULL,
  40   rank_search SMALLINT NOT NULL,
  41   rank_address SMALLINT NOT NULL,
  42   country_code VARCHAR(2),
  43   isguess BOOL NOT NULL,
  44   postcode TEXT,
  45   centroid GEOMETRY(Point, 4326) NOT NULL,
  46   geometry GEOMETRY(Geometry, 4326) NOT NULL
  47   );
  48
  49 CREATE TABLE location_area_large () INHERITS (location_area);
  50
  51 DROP TABLE IF EXISTS location_area_country;
  52 CREATE TABLE location_area_country (
  53   place_id BIGINT NOT NULL,
  54   country_code varchar(2) NOT NULL,
  55   geometry GEOMETRY(Geometry, 4326) NOT NULL
  56   ) {{db.tablespace.address_data}};
  57 CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {{db.tablespace.address_index}};
  58
  59
  60 CREATE TABLE location_property_tiger (
  61   place_id BIGINT NOT NULL,
  62   parent_place_id BIGINT,
  63   startnumber INTEGER NOT NULL,
  64   endnumber INTEGER NOT NULL,
  65   step SMALLINT NOT NULL,
  66   partition SMALLINT NOT NULL,
  67   linegeo GEOMETRY NOT NULL,
  68   postcode TEXT);
  69 GRANT SELECT ON location_property_tiger TO "{{config.DATABASE_WEBUSER}}";
  70
  71 drop table if exists location_property_osmline;
  72 CREATE TABLE location_property_osmline (
  73     place_id BIGINT NOT NULL,
  74     osm_id BIGINT NOT NULL,
  75     parent_place_id BIGINT,
  76     geometry_sector INTEGER NOT NULL,
  77     indexed_date TIMESTAMP,
  78     startnumber INTEGER,
  79     endnumber INTEGER,
  80     step SMALLINT,
  81     partition SMALLINT NOT NULL,
  82     indexed_status SMALLINT NOT NULL,
  83     linegeo GEOMETRY NOT NULL,
  84     address HSTORE,
  85     token_info JSONB, -- custom column for tokenizer use only
  86     postcode TEXT,
  87     country_code VARCHAR(2)
  88   ){{db.tablespace.search_data}};
  89 CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline USING BTREE (place_id) {{db.tablespace.search_index}};
  90 CREATE INDEX idx_osmline_geometry_sector ON location_property_osmline USING BTREE (geometry_sector) {{db.tablespace.address_index}};
  91 CREATE INDEX idx_osmline_linegeo ON location_property_osmline USING GIST (linegeo) {{db.tablespace.search_index}}
  92   WHERE startnumber is not null;
  93 GRANT SELECT ON location_property_osmline TO "{{config.DATABASE_WEBUSER}}";
  94
  95 drop table IF EXISTS search_name;
  96 {% if not db.reverse_only %}
  97 CREATE TABLE search_name (
  98   place_id BIGINT NOT NULL,
  99   importance FLOAT NOT NULL,
 100   search_rank SMALLINT NOT NULL,
 101   address_rank SMALLINT NOT NULL,
 102   name_vector integer[] NOT NULL,
 103   nameaddress_vector integer[] NOT NULL,
 104   country_code varchar(2),
 105   centroid GEOMETRY(Geometry, 4326) NOT NULL
 106   ) {{db.tablespace.search_data}};
 107 CREATE UNIQUE INDEX idx_search_name_place_id
 108   ON search_name USING BTREE (place_id) {{db.tablespace.search_index}};
 109 GRANT SELECT ON search_name to "{{config.DATABASE_WEBUSER}}" ;
 110 {% endif %}
 111
 112 drop table IF EXISTS place_addressline;
 113 CREATE TABLE place_addressline (
 114   place_id BIGINT NOT NULL,
 115   address_place_id BIGINT NOT NULL,
 116   distance FLOAT NOT NULL,
 117   cached_rank_address SMALLINT NOT NULL,
 118   fromarea boolean NOT NULL,
 119   isaddress boolean NOT NULL
 120   ) {{db.tablespace.search_data}};
 121 CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {{db.tablespace.search_index}};
 122
 123 ---------  PLACEX - storage for all indexed places -----------------
 124
 125 DROP TABLE IF EXISTS placex;
 126 CREATE TABLE placex (
 127   place_id BIGINT NOT NULL,
 128   parent_place_id BIGINT,
 129   linked_place_id BIGINT,
 130   importance FLOAT,
 131   indexed_date TIMESTAMP,
 132   geometry_sector INTEGER NOT NULL,
 133   rank_address SMALLINT NOT NULL,
 134   rank_search SMALLINT NOT NULL,
 135   partition SMALLINT NOT NULL,
 136   indexed_status SMALLINT NOT NULL,
 137   LIKE place INCLUDING CONSTRAINTS,
 138   wikipedia TEXT, -- calculated wikipedia article name (language:title)
 139   token_info JSONB, -- custom column for tokenizer use only
 140   country_code varchar(2),
 141   housenumber TEXT,
 142   postcode TEXT,
 143   centroid GEOMETRY(Geometry, 4326) NOT NULL
 144   ) {{db.tablespace.search_data}};
 145
 146 CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {{db.tablespace.search_index}};
 147 {% for osm_type in ('N', 'W', 'R') %}
 148 CREATE INDEX idx_placex_osmid_{{osm_type | lower}} ON placex
 149   USING BTREE (osm_id) {{db.tablespace.search_index}}
 150   WHERE osm_type = '{{osm_type}}';
 151 {% endfor %}
 152
 153 -- Usage: - removing linkage status on update
 154 --        - lookup linked places for /details
 155 CREATE INDEX idx_placex_linked_place_id ON placex
 156   USING BTREE (linked_place_id) {{db.tablespace.address_index}}
 157   WHERE linked_place_id IS NOT NULL;
 158
 159 -- Usage: - check that admin boundaries do not overtake each other rank-wise
 160 --        - check that place node in a admin boundary with the same address level
 161 --        - boundary is not completely contained in a place area
 162 --        - parenting of large-area or unparentable features
 163 CREATE INDEX idx_placex_geometry_address_area_candidates ON placex
 164   USING gist (geometry) {{db.tablespace.address_index}}
 165   WHERE rank_address between 1 and 25
 166         and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon');
 167
 168 -- Usage: - POI is within building with housenumber
 169 CREATE INDEX idx_placex_geometry_buildings ON placex
 170   USING SPGIST (geometry) {{db.tablespace.address_index}}
 171   WHERE address is not null and rank_search = 30
 172         and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon');
 173
 174 -- Usage: - linking of similar named places to boundaries
 175 --        - linking of place nodes with same type to boundaries
 176 CREATE INDEX idx_placex_geometry_placenode ON placex
 177   USING SPGIST (geometry) {{db.tablespace.address_index}}
 178   WHERE osm_type = 'N' and rank_search < 26 and class = 'place';
 179
 180 -- Usage: - is node part of a way?
 181 --        - find parent of interpolation spatially
 182 CREATE INDEX idx_placex_geometry_lower_rank_ways ON placex
 183   USING SPGIST (geometry) {{db.tablespace.address_index}}
 184   WHERE osm_type = 'W' and rank_search >= 26;
 185
 186 -- Usage: - linking place nodes by wikidata tag to boundaries
 187 CREATE INDEX idx_placex_wikidata on placex
 188   USING BTREE ((extratags -> 'wikidata')) {{db.tablespace.address_index}}
 189   WHERE extratags ? 'wikidata' and class = 'place'
 190         and osm_type = 'N' and rank_search < 26;
 191
 192 -- The following two indexes function as a todo list for indexing.
 193
 194 CREATE INDEX idx_placex_rank_address_sector ON placex
 195   USING BTREE (rank_address, geometry_sector) {{db.tablespace.address_index}}
 196   WHERE indexed_status > 0;
 197
 198 CREATE INDEX idx_placex_rank_boundaries_sector ON placex
 199   USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}}
 200   WHERE class = 'boundary' and type = 'administrative'
 201         and indexed_status > 0;
 202
 203
 204 DROP SEQUENCE IF EXISTS seq_place;
 205 CREATE SEQUENCE seq_place start 1;
 206 GRANT SELECT on placex to "{{config.DATABASE_WEBUSER}}" ;
 207 GRANT SELECT on place_addressline to "{{config.DATABASE_WEBUSER}}" ;
 208 GRANT SELECT ON planet_osm_ways to "{{config.DATABASE_WEBUSER}}" ;
 209 GRANT SELECT ON planet_osm_rels to "{{config.DATABASE_WEBUSER}}" ;
 210 GRANT SELECT on location_area to "{{config.DATABASE_WEBUSER}}" ;
 211
 212 -- Table for synthetic postcodes.
 213 DROP TABLE IF EXISTS location_postcodes;
 214 CREATE TABLE location_postcodes (
 215   place_id BIGINT NOT NULL,
 216   parent_place_id BIGINT,
 217   osm_id BIGINT,
 218   rank_search SMALLINT NOT NULL,
 219   indexed_status SMALLINT NOT NULL,
 220   indexed_date TIMESTAMP,
 221   country_code varchar(2) NOT NULL,
 222   postcode TEXT NOT NULL,
 223   centroid GEOMETRY(Geometry, 4326) NOT NULL,
 224   geometry GEOMETRY(Geometry, 4326) NOT NULL
 225   );
 226 CREATE UNIQUE INDEX idx_location_postcodes_id ON location_postcodes
 227   USING BTREE (place_id) {{db.tablespace.search_index}};
 228 CREATE INDEX idx_location_postcodes_geometry ON location_postcodes
 229   USING GIST (geometry) {{db.tablespace.search_index}};
 230 CREATE INDEX IF NOT EXISTS idx_location_postcodes_postcode
 231   ON location_postcodes USING BTREE (postcode, country_code)
 232   {{db.tablespace.search_index}};
 233 CREATE INDEX IF NOT EXISTS idx_location_postcodes_osmid
 234   ON location_postcodes USING BTREE (osm_id) {{db.tablespace.search_index}};
 235 GRANT SELECT ON location_postcodes TO "{{config.DATABASE_WEBUSER}}" ;
 236
 237 -- Table to store location of entrance nodes
 238 DROP TABLE IF EXISTS placex_entrance;
 239 CREATE TABLE placex_entrance (
 240   place_id BIGINT NOT NULL,
 241   osm_id BIGINT NOT NULL,
 242   type TEXT NOT NULL,
 243   location GEOMETRY(Point, 4326) NOT NULL,
 244   extratags HSTORE
 245   );
 246 CREATE UNIQUE INDEX idx_placex_entrance_place_id_osm_id ON placex_entrance
 247   USING BTREE (place_id, osm_id) {{db.tablespace.search_index}};
 248 GRANT SELECT ON placex_entrance TO "{{config.DATABASE_WEBUSER}}" ;
 249
 250 -- Create an index on the place table for lookups to populate the entrance
 251 -- table
 252 CREATE INDEX IF NOT EXISTS idx_placex_entrance_lookup ON place
 253   USING BTREE (osm_id)
 254   WHERE class IN ('routing:entrance', 'entrance');
 255
 256 DROP TABLE IF EXISTS import_polygon_error;
 257 CREATE TABLE import_polygon_error (
 258   osm_id BIGINT,
 259   osm_type CHAR(1),
 260   class TEXT NOT NULL,
 261   type TEXT NOT NULL,
 262   name HSTORE,
 263   country_code varchar(2),
 264   updated timestamp,
 265   errormessage text,
 266   prevgeometry GEOMETRY(Geometry, 4326),
 267   newgeometry GEOMETRY(Geometry, 4326)
 268   );
 269 CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
 270 GRANT SELECT ON import_polygon_error TO "{{config.DATABASE_WEBUSER}}";
 271
 272 DROP TABLE IF EXISTS import_polygon_delete;
 273 CREATE TABLE import_polygon_delete (
 274   osm_id BIGINT,
 275   osm_type CHAR(1),
 276   class TEXT NOT NULL,
 277   type TEXT NOT NULL
 278   );
 279 CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
 280 GRANT SELECT ON import_polygon_delete TO "{{config.DATABASE_WEBUSER}}";
 281
 282 DROP SEQUENCE IF EXISTS file;
 283 CREATE SEQUENCE file start 1;
 284
 285 {% if 'wikimedia_importance' not in db.tables and 'wikipedia_article' not in db.tables %}
 286 -- create dummy tables here, if nothing was imported
 287 CREATE TABLE wikimedia_importance (
 288   language TEXT NOT NULL,
 289   title TEXT NOT NULL,
 290   importance double precision NOT NULL,
 291   wikidata TEXT
 292 )  {{db.tablespace.address_data}};
 293 {% endif %}
 294
 295 -- osm2pgsql does not create indexes on the middle tables for Nominatim
 296 -- Add one for lookup of associated street relations.
 297 {% if db.middle_db_format == '1' %}
 298 CREATE INDEX planet_osm_rels_parts_associated_idx ON planet_osm_rels USING gin(parts)
 299   {{db.tablespace.address_index}}
 300   WHERE tags @> ARRAY['associatedStreet'];
 301 {% else %}
 302 CREATE INDEX planet_osm_rels_relation_members_idx ON planet_osm_rels USING gin(planet_osm_member_ids(members, 'R'::character(1)))
 303   WITH (fastupdate=off)
 304   {{db.tablespace.address_index}};
 305 {% endif %}
 306
 307 -- Needed for lookups if a node is part of an interpolation.
 308 CREATE INDEX IF NOT EXISTS idx_place_interpolations
 309     ON place USING gist(geometry) {{db.tablespace.address_index}}
 310     WHERE osm_type = 'W' and address ? 'interpolation';
 311
 312 GRANT SELECT ON table country_osm_grid to "{{config.DATABASE_WEBUSER}}";