X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/909b0c7462b336d07cab4499529957a4096a8c18..a8ec250993b0ed0e48bbefd8fb3bcbb861c5f2a9:/sql/tables.sql?ds=inline diff --git a/sql/tables.sql b/sql/tables.sql index 4a22a814..5686bcd2 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -10,7 +10,7 @@ drop table if exists import_osmosis_log; CREATE TABLE import_osmosis_log ( batchend timestamp, batchseq integer, - batchsize integer, + batchsize bigint, starttime timestamp, endtime timestamp, event text @@ -36,6 +36,7 @@ GRANT SELECT ON new_query_log TO "{www-user}" ; GRANT SELECT ON TABLE country_name TO "{www-user}"; GRANT SELECT ON TABLE gb_postcode TO "{www-user}"; +GRANT SELECT ON TABLE us_postcode TO "{www-user}"; drop table IF EXISTS word; CREATE TABLE word ( @@ -69,6 +70,15 @@ CREATE TABLE location_area ( CREATE TABLE location_area_large () INHERITS (location_area); +DROP TABLE IF EXISTS location_area_country; +CREATE TABLE location_area_country ( + place_id BIGINT, + country_code varchar(2), + geometry GEOMETRY(Geometry, 4326) + ) {ts:address-data}; +CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {ts:address-index}; + + drop table IF EXISTS location_property CASCADE; CREATE TABLE location_property ( place_id BIGINT, @@ -166,7 +176,8 @@ CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {ts:searc CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id) {ts:address-index} WHERE linked_place_id IS NOT NULL; CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {ts:address-index}; CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {ts:search-index}; -CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) {ts:address-index} WHERE osm_type='N' and rank_search < 26; +CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name')) {ts:address-index} WHERE osm_type='N' and rank_search < 26; +CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {ts:address-index} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26; DROP SEQUENCE IF EXISTS seq_place; CREATE SEQUENCE seq_place start 1; @@ -178,26 +189,6 @@ GRANT SELECT ON planet_osm_ways to "{www-user}" ; GRANT SELECT ON planet_osm_rels to "{www-user}" ; GRANT SELECT on location_area to "{www-user}" ; --- insert creates the location tables, creates location indexes if indexed == true -CREATE TRIGGER placex_before_insert BEFORE INSERT ON placex - FOR EACH ROW EXECUTE PROCEDURE placex_insert(); -CREATE TRIGGER osmline_before_insert BEFORE INSERT ON location_property_osmline - FOR EACH ROW EXECUTE PROCEDURE osmline_insert(); - --- update insert creates the location tables -CREATE TRIGGER placex_before_update BEFORE UPDATE ON placex - FOR EACH ROW EXECUTE PROCEDURE placex_update(); -CREATE TRIGGER osmline_before_update BEFORE UPDATE ON location_property_osmline - FOR EACH ROW EXECUTE PROCEDURE osmline_update(); - --- diff update triggers -CREATE TRIGGER placex_before_delete AFTER DELETE ON placex - FOR EACH ROW EXECUTE PROCEDURE placex_delete(); -CREATE TRIGGER place_before_delete BEFORE DELETE ON place - FOR EACH ROW EXECUTE PROCEDURE place_delete(); -CREATE TRIGGER place_before_insert BEFORE INSERT ON place - FOR EACH ROW EXECUTE PROCEDURE place_insert(); - -- Table for synthetic postcodes. DROP TABLE IF EXISTS location_postcode; CREATE TABLE location_postcode ( @@ -214,9 +205,6 @@ CREATE TABLE location_postcode ( CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index}; GRANT SELECT ON location_postcode TO "{www-user}" ; -CREATE TRIGGER location_postcode_before_update BEFORE UPDATE ON location_postcode - FOR EACH ROW EXECUTE PROCEDURE postcode_update(); - DROP TABLE IF EXISTS import_polygon_error; CREATE TABLE import_polygon_error ( osm_id BIGINT, @@ -258,7 +246,9 @@ CREATE TABLE wikipedia_article ( lon double precision, importance double precision, osm_type character(1), - osm_id bigint + osm_id bigint, + wd_page_title text, + instance_of text ); ALTER TABLE ONLY wikipedia_article ADD CONSTRAINT wikipedia_article_pkey PRIMARY KEY (language, title); CREATE INDEX idx_wikipedia_article_osm_id ON wikipedia_article USING btree (osm_type, osm_id); @@ -270,3 +260,6 @@ CREATE TABLE wikipedia_redirect ( ); ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title); +-- osm2pgsql does not create indexes on the middle tables for Nominatim +-- Add one for lookup of associated street relations. +CREATE INDEX planet_osm_rels_parts_associated_idx ON planet_osm_rels USING gin(parts) WHERE tags @> ARRAY['associatedStreet'];