X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/80ef6cbaab0e503739d98c97cf2d6a80407bfb00..36447c488a78017dc533f7ed42bc8cf20c72556d:/sql/tables.sql?ds=sidebyside diff --git a/sql/tables.sql b/sql/tables.sql index 334fcbc0..8647e304 100644 --- a/sql/tables.sql +++ b/sql/tables.sql @@ -1,6 +1,6 @@ drop table if exists import_status; CREATE TABLE import_status ( - lastimportdate timestamp NOT NULL, + lastimportdate timestamp with time zone NOT NULL, sequence_id integer, indexed boolean ); @@ -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 ( @@ -61,22 +62,32 @@ CREATE TABLE location_area ( rank_search SMALLINT NOT NULL, rank_address SMALLINT NOT NULL, country_code VARCHAR(2), - isguess BOOL + isguess BOOL, + postcode TEXT, + centroid GEOMETRY(Point, 4326), + geometry GEOMETRY(Geometry, 4326) ); -SELECT AddGeometryColumn('location_area', 'centroid', 4326, 'POINT', 2); -SELECT AddGeometryColumn('location_area', 'geometry', 4326, 'GEOMETRY', 2); 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, parent_place_id BIGINT, partition SMALLINT, housenumber TEXT, - postcode TEXT + postcode TEXT, + centroid GEOMETRY(Point, 4326) ); -SELECT AddGeometryColumn('location_property', 'centroid', 4326, 'POINT', 2); CREATE TABLE location_property_aux () INHERITS (location_property); CREATE INDEX idx_location_property_aux_place_id ON location_property_aux USING BTREE (place_id); @@ -125,9 +136,9 @@ CREATE TABLE search_name ( address_rank SMALLINT, name_vector integer[], nameaddress_vector integer[], - country_code varchar(2) + country_code varchar(2), + centroid GEOMETRY(Geometry, 4326) ) {ts:search-data}; -SELECT AddGeometryColumn('search_name', 'centroid', 4326, 'GEOMETRY', 2); CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) {ts:search-index}; drop table IF EXISTS place_addressline; @@ -157,15 +168,16 @@ CREATE TABLE placex ( wikipedia TEXT, -- calculated wikipedia article name (language:title) country_code varchar(2), housenumber TEXT, - postcode TEXT + postcode TEXT, + centroid GEOMETRY(Geometry, 4326) ) {ts:search-data}; -SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2); CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {ts:search-index}; CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {ts:search-index}; 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; @@ -177,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 ( @@ -211,9 +203,7 @@ CREATE TABLE location_postcode ( geometry GEOMETRY(Geometry, 4326) ); CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index}; - -CREATE TRIGGER location_postcode_before_update BEFORE UPDATE ON location_postcode - FOR EACH ROW EXECUTE PROCEDURE postcode_update(); +GRANT SELECT ON location_postcode TO "{www-user}" ; DROP TABLE IF EXISTS import_polygon_error; CREATE TABLE import_polygon_error ( @@ -224,10 +214,10 @@ CREATE TABLE import_polygon_error ( name HSTORE, country_code varchar(2), updated timestamp, - errormessage text + errormessage text, + prevgeometry GEOMETRY(Geometry, 4326), + newgeometry GEOMETRY(Geometry, 4326) ); -SELECT AddGeometryColumn('import_polygon_error', 'prevgeometry', 4326, 'GEOMETRY', 2); -SELECT AddGeometryColumn('import_polygon_error', 'newgeometry', 4326, 'GEOMETRY', 2); CREATE INDEX idx_import_polygon_error_osmid ON import_polygon_error USING BTREE (osm_type, osm_id); GRANT SELECT ON import_polygon_error TO "{www-user}"; @@ -256,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); @@ -268,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'];