Adds partial indexes for all geometry queries used during import.
A full index is not necessary anymore at that point. Still create
the index afterwards for use in queries.
Also adds documentation for all indexes on where they are used.
IF parent_place_id is null THEN
FOR location IN SELECT place_id FROM placex
- WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26
+ WHERE ST_DWithin(geom, placex.geometry, 0.001)
+ and placex.rank_search = 26
+ and placex.osm_type = 'W' -- needed for index selection
ORDER BY CASE WHEN ST_GeometryType(geom) = 'ST_Line' THEN
(ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+
SELECT place_id FROM placex
WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox))
AND rank_address between 5 and 25
+ AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
ORDER BY rank_address desc
LOOP
RETURN location.place_id;
SELECT place_id FROM placex
WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox))
AND rank_address between 5 and 25
+ AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
ORDER BY rank_address desc
LOOP
RETURN location.place_id;
-- If extratags has a place tag, look for linked nodes by their place type.
-- Area and node still have to have the same name.
- IF bnd.extratags ? 'place' and bnd_name is not null THEN
+ IF bnd.extratags ? 'place' and bnd.extratags->'place' != 'postcode'
+ and bnd_name is not null
+ THEN
FOR linked_placex IN
SELECT * FROM placex
WHERE (position(lower(name->'name') in bnd_name) > 0
AND placex.osm_type = 'N'
AND (placex.linked_place_id is null or placex.linked_place_id = bnd.place_id)
AND placex.rank_search < 26 -- needed to select the right index
- AND placex.type != 'postcode'
AND ST_Covers(bnd.geometry, placex.geometry)
LOOP
{% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %}
-- Copyright (C) 2022 by the Nominatim developer community.
-- For a full list of authors see the git log.
--- The following indicies are only useful during imoprt when all of placex is processed.
-
-{% if drop %}
- DROP INDEX IF EXISTS idx_placex_rank_address_sector;
- DROP INDEX IF EXISTS idx_placex_rank_boundaries_sector;
-{% endif %}
-
-- Indices used only during search and update.
-- These indices are created only after the indexing process is done.
ON placex USING BTREE (parent_place_id) {{db.tablespace.search_index}}
WHERE parent_place_id IS NOT NULL;
---
+CREATE INDEX IF NOT EXISTS idx_placex_geometry ON placex
+ USING GIST (geometry) {{db.tablespace.search_index}};
+---
CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPolygon
ON placex USING gist (geometry) {{db.tablespace.search_index}}
WHERE St_GeometryType(geometry) in ('ST_Polygon', 'ST_MultiPolygon')
---
CREATE INDEX IF NOT EXISTS idx_postcode_postcode
ON location_postcode USING BTREE (postcode) {{db.tablespace.search_index}};
--- Indices only needed for updating.
-{% if not drop %}
+{% if drop %}
+---
+ DROP INDEX IF EXISTS idx_placex_geometry_address_area_candidates;
+ DROP INDEX IF EXISTS idx_placex_geometry_buildings;
+ DROP INDEX IF EXISTS idx_placex_geometry_lower_rank_ways;
+ DROP INDEX IF EXISTS idx_placex_wikidata;
+ DROP INDEX IF EXISTS idx_placex_rank_address_sector;
+ DROP INDEX IF EXISTS idx_placex_rank_boundaries_sector;
+{% else %}
+-- Indices only needed for updating.
---
CREATE INDEX IF NOT EXISTS idx_location_area_country_place_id
ON location_area_country USING BTREE (place_id) {{db.tablespace.address_index}};
) {{db.tablespace.search_data}};
CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {{db.tablespace.search_index}};
-drop table if exists placex;
+--------- PLACEX - storage for all indexed places -----------------
+
+DROP TABLE IF EXISTS placex;
CREATE TABLE placex (
place_id BIGINT NOT NULL,
parent_place_id BIGINT,
postcode TEXT,
centroid GEOMETRY(Geometry, 4326)
) {{db.tablespace.search_data}};
+
CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {{db.tablespace.search_index}};
-CREATE INDEX idx_placex_node_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'N';
-CREATE INDEX idx_placex_way_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'W';
-CREATE INDEX idx_placex_relation_osmid ON placex USING BTREE (osm_id) {{db.tablespace.search_index}} WHERE osm_type = 'R';
-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_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}};
+{% for osm_type in ('N', 'W', 'R') %}
+CREATE INDEX idx_placex_osmid_{{osm_type | lower}} ON placex
+ USING BTREE (osm_id) {{db.tablespace.search_index}}
+ WHERE osm_type = '{{osm_type}}';
+{% endfor %}
+
+-- Usage: - removing linkage status on update
+-- - lookup linked places for /details
+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;
+
+-- Usage: - check that admin boundaries do not overtake each other rank-wise
+-- - check that place node in a admin boundary with the same address level
+-- - boundary is not completely contained in a place area
+-- - parenting of large-area or unparentable features
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');
+
+-- Usage: - POI is within building with housenumber
CREATE INDEX idx_placex_geometry_buildings ON placex
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');
+
+-- Usage: - linking of similar named places to boundaries
+-- - linking of place nodes with same type to boundaries
+-- - lookupPolygon()
CREATE INDEX idx_placex_geometry_placenode ON placex
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;
+ and class = 'place' and type != 'postcode';
+
+-- Usage: - is node part of a way?
+-- - find parent of interpolation spatially
+CREATE INDEX idx_placex_geometry_lower_rank_ways ON placex
+ USING {{postgres.spgist_geom}} (geometry) {{db.tablespace.address_index}}
+ WHERE osm_type = 'W' and rank_search >= 26;
+
+-- Usage: - linking place nodes by wikidata tag to boundaries
+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;
-- The following two indexes function as a todo list for indexing.
projects / nominatim.git / commitdiff