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