Merge remote-tracking branch 'lonvia/partial-search-terms'

[nominatim.git] / sql / tables.sql

diff --git a/sql/tables.sql b/sql/tables.sql
index ff9c1c50fa515a0b7ee5ad76f9dc1555a0de070b..d5cf7bcd47eb0755ecbd2dcfdae9f2fac6092fe0 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -138,7 +138,6 @@ SELECT AddGeometryColumn('search_name_blank', 'centroid', 4326, 'GEOMETRY', 2);
 drop table IF EXISTS search_name;
 CREATE TABLE search_name () INHERITS (search_name_blank);
 CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id);
-CREATE INDEX search_name_name_vector_idx ON search_name USING GIN (name_vector) WITH (fastupdate = off);
 
 drop table IF EXISTS place_addressline;
 CREATE TABLE place_addressline (
@@ -199,7 +198,9 @@ CREATE TABLE placex (
   importance FLOAT,
   indexed_status INTEGER,
   indexed_date TIMESTAMP,
-  geometry_sector INTEGER
+  wikipedia TEXT, -- calculated wikipedia article name (language:title)
+  geometry_sector INTEGER,
+  calculated_country_code varchar(2)
   );
 SELECT AddGeometryColumn('placex', 'centroid', 4326, 'GEOMETRY', 2);
 CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id);
@@ -207,6 +208,7 @@ CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id);
 CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id);
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector);
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry);
+CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name'),rank_search) WHERE osm_type='N' and rank_search < 26;
 
 --CREATE INDEX idx_placex_indexed ON placex USING BTREE (indexed);
 
@@ -288,3 +290,28 @@ CREATE INDEX idx_import_polygon_delete_osmid ON import_polygon_delete USING BTRE
 
 drop sequence file;
 CREATE SEQUENCE file start 1;
+
+-- null table so it won't error
+-- deliberately no drop - importing the table is expensive and static, if it is already there better to avoid removing it
+CREATE TABLE wikipedia_article (
+    language text NOT NULL,
+    title text NOT NULL,
+    langcount integer,
+    othercount integer,
+    totalcount integer,
+    lat double precision,
+    lon double precision,
+    importance double precision,
+    osm_type character(1),
+    osm_id bigint
+);
+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);
+
+CREATE TABLE wikipedia_redirect (
+    language text,
+    from_title text,
+    to_title text
+);
+ALTER TABLE ONLY wikipedia_redirect ADD CONSTRAINT wikipedia_redirect_pkey PRIMARY KEY (language, from_title);
+