Merge pull request #2281 from changpingc/changping/fix-tiger-index

[nominatim.git] / lib-sql / tables.sql

diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 0895c6dd3b83b812fb0575384c2ea85ff5f47f4e..aa213dbaccb4ebd49436fc68853ed0ab8d9c6789 100644 (file)
--- a/lib-sql/tables.sql
+++ b/lib-sql/tables.sql
@@ -184,7 +184,10 @@ CREATE INDEX idx_placex_osmid ON placex USING BTREE (osm_type, osm_id) {{db.tabl
 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;
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {{db.tablespace.address_index}};
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};
-CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name')) {{db.tablespace.address_index}} WHERE osm_type='N' and rank_search < 26;
+CREATE INDEX idx_placex_geometry_placenode ON placex
+  USING GIST (geometry) {{db.tablespace.search_index}}
+  WHERE osm_type = 'N' and rank_search < 26
+        and class = 'place' and type != 'postcode' and linked_place_id is null;
 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;
 
 DROP SEQUENCE IF EXISTS seq_place;
@@ -209,6 +212,7 @@ CREATE TABLE location_postcode (
   postcode TEXT,
   geometry GEOMETRY(Geometry, 4326)
   );
+CREATE UNIQUE INDEX idx_postcode_id ON location_postcode USING BTREE (place_id) {{db.tablespace.search_index}};
 CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {{db.tablespace.address_index}};
 GRANT SELECT ON location_postcode TO "{{config.DATABASE_WEBUSER}}" ;