From aef014a47d5509039a49776b0999fde902b5f8f4 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sun, 18 Sep 2022 16:50:18 +0200 Subject: [PATCH] add indexes for lookup of addressable areas The generic geometry index has become to slow for that purpose. --- lib-sql/functions/placex_triggers.sql | 7 ++++++- lib-sql/tables.sql | 8 ++++++-- 2 files changed, 12 insertions(+), 3 deletions(-) diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index bb34883a..6c23fd67 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -874,8 +874,9 @@ BEGIN FROM placex, LATERAL compute_place_rank(country_code, 'A', class, type, admin_level, False, null) prank - WHERE class = 'place' and rank_address < 24 + WHERE class = 'place' and rank_address between 1 and 23 and prank.address_rank >= NEW.rank_address + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index and geometry && NEW.geometry and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal @@ -896,6 +897,8 @@ BEGIN LATERAL compute_place_rank(country_code, 'A', class, type, admin_level, False, null) prank WHERE prank.address_rank < 24 + and rank_address between 1 and 25 -- select right index + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index and prank.address_rank >= NEW.rank_address and geometry && NEW.geometry and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test @@ -916,6 +919,8 @@ BEGIN LATERAL compute_place_rank(country_code, 'A', class, type, admin_level, False, null) prank WHERE osm_type = 'R' + and rank_address between 1 and 25 -- select right index + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index and ((class = 'place' and prank.address_rank = NEW.rank_address) or (class = 'boundary' and rank_address = NEW.rank_address)) and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid) diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index 03431d95..2ad5243c 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -162,12 +162,16 @@ 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_geometry_address_area_candidates ON placex + USING gist (geometry) {{db.tablespace.address_index}} + WHERE rank_address between 1 and 25 + and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon'); CREATE INDEX idx_placex_geometry_buildings ON placex - USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.search_index}} + USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_index}} WHERE address is not null and rank_search = 30 and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon'); CREATE INDEX idx_placex_geometry_placenode ON placex - USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.search_index}} + USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_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; -- 2.45.2