1 -- SPDX-License-Identifier: GPL-2.0-only
3 -- This file is part of Nominatim. (https://nominatim.org)
5 -- Copyright (C) 2022 by the Nominatim developer community.
6 -- For a full list of authors see the git log.
8 drop table if exists import_status;
9 CREATE TABLE import_status (
10 lastimportdate timestamp with time zone NOT NULL,
14 GRANT SELECT ON import_status TO "{{config.DATABASE_WEBUSER}}" ;
16 drop table if exists import_osmosis_log;
17 CREATE TABLE import_osmosis_log (
26 CREATE TABLE new_query_log (
39 CREATE INDEX idx_new_query_log_starttime ON new_query_log USING BTREE (starttime);
40 GRANT INSERT ON new_query_log TO "{{config.DATABASE_WEBUSER}}" ;
41 GRANT UPDATE ON new_query_log TO "{{config.DATABASE_WEBUSER}}" ;
42 GRANT SELECT ON new_query_log TO "{{config.DATABASE_WEBUSER}}" ;
44 GRANT SELECT ON TABLE country_name TO "{{config.DATABASE_WEBUSER}}";
46 DROP TABLE IF EXISTS nominatim_properties;
47 CREATE TABLE nominatim_properties (
48 property TEXT NOT NULL,
51 GRANT SELECT ON TABLE nominatim_properties TO "{{config.DATABASE_WEBUSER}}";
53 drop table IF EXISTS location_area CASCADE;
54 CREATE TABLE location_area (
58 rank_search SMALLINT NOT NULL,
59 rank_address SMALLINT NOT NULL,
60 country_code VARCHAR(2),
63 centroid GEOMETRY(Point, 4326),
64 geometry GEOMETRY(Geometry, 4326)
67 CREATE TABLE location_area_large () INHERITS (location_area);
69 DROP TABLE IF EXISTS location_area_country;
70 CREATE TABLE location_area_country (
72 country_code varchar(2),
73 geometry GEOMETRY(Geometry, 4326)
74 ) {{db.tablespace.address_data}};
75 CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {{db.tablespace.address_index}};
78 CREATE TABLE location_property_tiger (
80 parent_place_id BIGINT,
87 GRANT SELECT ON location_property_tiger TO "{{config.DATABASE_WEBUSER}}";
89 drop table if exists location_property_osmline;
90 CREATE TABLE location_property_osmline (
91 place_id BIGINT NOT NULL,
93 parent_place_id BIGINT,
94 geometry_sector INTEGER,
95 indexed_date TIMESTAMP,
100 indexed_status SMALLINT,
103 token_info JSONB, -- custom column for tokenizer use only
105 country_code VARCHAR(2)
106 ){{db.tablespace.search_data}};
107 CREATE UNIQUE INDEX idx_osmline_place_id ON location_property_osmline USING BTREE (place_id) {{db.tablespace.search_index}};
108 CREATE INDEX idx_osmline_geometry_sector ON location_property_osmline USING BTREE (geometry_sector) {{db.tablespace.address_index}};
109 CREATE INDEX idx_osmline_linegeo ON location_property_osmline USING GIST (linegeo) {{db.tablespace.search_index}}
110 WHERE startnumber is not null;
111 GRANT SELECT ON location_property_osmline TO "{{config.DATABASE_WEBUSER}}";
113 drop table IF EXISTS search_name;
114 {% if not db.reverse_only %}
115 CREATE TABLE search_name (
118 search_rank SMALLINT,
119 address_rank SMALLINT,
120 name_vector integer[],
121 nameaddress_vector integer[],
122 country_code varchar(2),
123 centroid GEOMETRY(Geometry, 4326)
124 ) {{db.tablespace.search_data}};
125 CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) {{db.tablespace.search_index}};
126 GRANT SELECT ON search_name to "{{config.DATABASE_WEBUSER}}" ;
129 drop table IF EXISTS place_addressline;
130 CREATE TABLE place_addressline (
132 address_place_id BIGINT,
134 cached_rank_address SMALLINT,
137 ) {{db.tablespace.search_data}};
138 CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {{db.tablespace.search_index}};
140 drop table if exists placex;
141 CREATE TABLE placex (
142 place_id BIGINT NOT NULL,
143 parent_place_id BIGINT,
144 linked_place_id BIGINT,
146 indexed_date TIMESTAMP,
147 geometry_sector INTEGER,
148 rank_address SMALLINT,
149 rank_search SMALLINT,
151 indexed_status SMALLINT,
152 LIKE place INCLUDING CONSTRAINTS,
153 wikipedia TEXT, -- calculated wikipedia article name (language:title)
154 token_info JSONB, -- custom column for tokenizer use only
155 country_code varchar(2),
158 centroid GEOMETRY(Geometry, 4326)
159 ) {{db.tablespace.search_data}};
160 CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {{db.tablespace.search_index}};
161 CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {{db.tablespace.search_index}};
162 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {{db.tablespace.address_index}} WHERE linked_place_id IS NOT NULL;
163 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}};
164 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};
165 CREATE INDEX idx_placex_geometry_buildings ON placex
166 USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.search_index}}
167 WHERE address is not null and rank_search = 30
168 and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon');
169 CREATE INDEX idx_placex_geometry_placenode ON placex
170 USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.search_index}}
171 WHERE osm_type = 'N' and rank_search < 26
172 and class = 'place' and type != 'postcode' and linked_place_id is null;
173 CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {{db.tablespace.address_index}} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26;
175 DROP SEQUENCE IF EXISTS seq_place;
176 CREATE SEQUENCE seq_place start 1;
177 GRANT SELECT on placex to "{{config.DATABASE_WEBUSER}}" ;
178 GRANT SELECT on place_addressline to "{{config.DATABASE_WEBUSER}}" ;
179 GRANT SELECT ON planet_osm_ways to "{{config.DATABASE_WEBUSER}}" ;
180 GRANT SELECT ON planet_osm_rels to "{{config.DATABASE_WEBUSER}}" ;
181 GRANT SELECT on location_area to "{{config.DATABASE_WEBUSER}}" ;
183 -- Table for synthetic postcodes.
184 DROP TABLE IF EXISTS location_postcode;
185 CREATE TABLE location_postcode (
187 parent_place_id BIGINT,
188 rank_search SMALLINT,
189 rank_address SMALLINT,
190 indexed_status SMALLINT,
191 indexed_date TIMESTAMP,
192 country_code varchar(2),
194 geometry GEOMETRY(Geometry, 4326)
196 CREATE UNIQUE INDEX idx_postcode_id ON location_postcode USING BTREE (place_id) {{db.tablespace.search_index}};
197 CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {{db.tablespace.address_index}};
198 GRANT SELECT ON location_postcode TO "{{config.DATABASE_WEBUSER}}" ;
200 DROP TABLE IF EXISTS import_polygon_error;
201 CREATE TABLE import_polygon_error (
207 country_code varchar(2),
210 prevgeometry GEOMETRY(Geometry, 4326),
211 newgeometry GEOMETRY(Geometry, 4326)
213 CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
214 GRANT SELECT ON import_polygon_error TO "{{config.DATABASE_WEBUSER}}";
216 DROP TABLE IF EXISTS import_polygon_delete;
217 CREATE TABLE import_polygon_delete (
223 CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
224 GRANT SELECT ON import_polygon_delete TO "{{config.DATABASE_WEBUSER}}";
226 DROP SEQUENCE IF EXISTS file;
227 CREATE SEQUENCE file start 1;
229 -- null table so it won't error
230 -- deliberately no drop - importing the table is expensive and static, if it is already there better to avoid removing it
231 CREATE TABLE wikipedia_article (
232 language text NOT NULL,
237 lat double precision,
238 lon double precision,
239 importance double precision,
240 osm_type character(1),
245 ALTER TABLE ONLY wikipedia_article ADD CONSTRAINT wikipedia_article_pkey PRIMARY KEY (language, title);
246 CREATE INDEX idx_wikipedia_article_osm_id ON wikipedia_article USING btree (osm_type, osm_id);
248 CREATE TABLE wikipedia_redirect (
253 ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title);
255 -- osm2pgsql does not create indexes on the middle tables for Nominatim
256 -- Add one for lookup of associated street relations.
257 CREATE INDEX planet_osm_rels_parts_associated_idx ON planet_osm_rels USING gin(parts) WHERE tags @> ARRAY['associatedStreet'];
259 -- Needed for lookups if a node is part of an interpolation.
260 CREATE INDEX IF NOT EXISTS idx_place_interpolations
261 ON place USING gist(geometry) {{db.tablespace.address_index}}
262 WHERE osm_type = 'W' and address ? 'interpolation';
264 GRANT SELECT ON table country_osm_grid to "{{config.DATABASE_WEBUSER}}";