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_node_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'N';
162 CREATE INDEX idx_placex_way_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'W';
163 CREATE INDEX idx_placex_relation_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'R';
164 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;
165 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}};
166 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};
167 CREATE INDEX idx_placex_geometry_address_area_candidates ON placex
168 USING gist (geometry) {{db.tablespace.address_index}}
169 WHERE rank_address between 1 and 25
170 and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon');
171 CREATE INDEX idx_placex_geometry_buildings ON placex
172 USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_index}}
173 WHERE address is not null and rank_search = 30
174 and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon');
175 CREATE INDEX idx_placex_geometry_placenode ON placex
176 USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_index}}
177 WHERE osm_type = 'N' and rank_search < 26
178 and class = 'place' and type != 'postcode' and linked_place_id is null;
179 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;
181 DROP SEQUENCE IF EXISTS seq_place;
182 CREATE SEQUENCE seq_place start 1;
183 GRANT SELECT on placex to "{{config.DATABASE_WEBUSER}}" ;
184 GRANT SELECT on place_addressline to "{{config.DATABASE_WEBUSER}}" ;
185 GRANT SELECT ON planet_osm_ways to "{{config.DATABASE_WEBUSER}}" ;
186 GRANT SELECT ON planet_osm_rels to "{{config.DATABASE_WEBUSER}}" ;
187 GRANT SELECT on location_area to "{{config.DATABASE_WEBUSER}}" ;
189 -- Table for synthetic postcodes.
190 DROP TABLE IF EXISTS location_postcode;
191 CREATE TABLE location_postcode (
193 parent_place_id BIGINT,
194 rank_search SMALLINT,
195 rank_address SMALLINT,
196 indexed_status SMALLINT,
197 indexed_date TIMESTAMP,
198 country_code varchar(2),
200 geometry GEOMETRY(Geometry, 4326)
202 CREATE UNIQUE INDEX idx_postcode_id ON location_postcode USING BTREE (place_id) {{db.tablespace.search_index}};
203 CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {{db.tablespace.address_index}};
204 GRANT SELECT ON location_postcode TO "{{config.DATABASE_WEBUSER}}" ;
206 DROP TABLE IF EXISTS import_polygon_error;
207 CREATE TABLE import_polygon_error (
213 country_code varchar(2),
216 prevgeometry GEOMETRY(Geometry, 4326),
217 newgeometry GEOMETRY(Geometry, 4326)
219 CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id);
220 GRANT SELECT ON import_polygon_error TO "{{config.DATABASE_WEBUSER}}";
222 DROP TABLE IF EXISTS import_polygon_delete;
223 CREATE TABLE import_polygon_delete (
229 CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTREE (osm_type, osm_id);
230 GRANT SELECT ON import_polygon_delete TO "{{config.DATABASE_WEBUSER}}";
232 DROP SEQUENCE IF EXISTS file;
233 CREATE SEQUENCE file start 1;
235 -- null table so it won't error
236 -- deliberately no drop - importing the table is expensive and static, if it is already there better to avoid removing it
237 CREATE TABLE wikipedia_article (
238 language text NOT NULL,
243 lat double precision,
244 lon double precision,
245 importance double precision,
246 osm_type character(1),
251 ALTER TABLE ONLY wikipedia_article ADD CONSTRAINT wikipedia_article_pkey PRIMARY KEY (language, title);
252 CREATE INDEX idx_wikipedia_article_osm_id ON wikipedia_article USING btree (osm_type, osm_id);
254 CREATE TABLE wikipedia_redirect (
259 ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title);
261 -- osm2pgsql does not create indexes on the middle tables for Nominatim
262 -- Add one for lookup of associated street relations.
263 CREATE INDEX planet_osm_rels_parts_associated_idx ON planet_osm_rels USING gin(parts) WHERE tags @> ARRAY['associatedStreet'];
265 -- Needed for lookups if a node is part of an interpolation.
266 CREATE INDEX IF NOT EXISTS idx_place_interpolations
267 ON place USING gist(geometry) {{db.tablespace.address_index}}
268 WHERE osm_type = 'W' and address ? 'interpolation';
270 GRANT SELECT ON table country_osm_grid to "{{config.DATABASE_WEBUSER}}";