X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/33c919ab09070ed0ec8f4d87366c5f9a0910e2c9..a8dfbcef44e8cfe844e396572a997b9cef07c522:/sql/tables.sql?ds=sidebyside diff --git a/sql/tables.sql b/sql/tables.sql index f3217d5a..cf51cbe6 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 ( @@ -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,28 +189,21 @@ 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(); - -DROP SEQUENCE IF EXISTS seq_postcodes; -CREATE SEQUENCE seq_postcodes start 1; +-- Table for synthetic postcodes. +DROP TABLE IF EXISTS location_postcode; +CREATE TABLE location_postcode ( + place_id BIGINT, + parent_place_id BIGINT, + rank_search SMALLINT, + rank_address SMALLINT, + indexed_status SMALLINT, + indexed_date TIMESTAMP, + country_code varchar(2), + postcode TEXT, + geometry GEOMETRY(Geometry, 4326) + ); +CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {ts:address-index}; +GRANT SELECT ON location_postcode TO "{www-user}" ; DROP TABLE IF EXISTS import_polygon_error; CREATE TABLE import_polygon_error ( @@ -209,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}"; @@ -241,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);