From 0aa9eee3e7b0a46c1bd75ecbc1c9b4d2d8a46aaa Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 23 Dec 2025 11:39:34 +0100 Subject: [PATCH] remove special casing for postcodes in trigger code --- lib-sql/functions/place_triggers.sql | 42 +------------------ lib-sql/functions/placex_triggers.sql | 29 +------------ lib-sql/indices.sql | 13 ++---- lib-sql/tables.sql | 5 +-- src/nominatim_api/sql/sqlalchemy_functions.py | 6 +-- 5 files changed, 10 insertions(+), 85 deletions(-) diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql index 216ab487..b64bf4fc 100644 --- a/lib-sql/functions/place_triggers.sql +++ b/lib-sql/functions/place_triggers.sql @@ -2,7 +2,7 @@ -- -- This file is part of Nominatim. (https://nominatim.org) -- --- Copyright (C) 2022 by the Nominatim developer community. +-- Copyright (C) 2025 by the Nominatim developer community. -- For a full list of authors see the git log. CREATE OR REPLACE FUNCTION place_insert() @@ -89,35 +89,6 @@ BEGIN RETURN NEW; END IF; - -- ---- Postcode points. - - IF NEW.class = 'place' AND NEW.type = 'postcode' THEN - -- Pure postcodes are never queried from placex so we don't add them. - -- location_postcodes is filled from the place table directly. - - -- Remove any old placex entry. - DELETE FROM placex WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id; - - IF existing.osm_type IS NOT NULL THEN - IF coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore) - OR existing.geometry::text != NEW.geometry::text - THEN - UPDATE place - SET name = NEW.name, - address = NEW.address, - extratags = NEW.extratags, - admin_level = NEW.admin_level, - geometry = NEW.geometry - WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id - and class = NEW.class and type = NEW.type; - END IF; - - RETURN NULL; - END IF; - - RETURN NEW; - END IF; - -- ---- All other place types. -- When an area is changed from large to small: log and discard change @@ -269,17 +240,6 @@ BEGIN WHERE osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type; - -- Postcode areas are only kept, when there is an actual postcode assigned. - IF NEW.class = 'boundary' AND NEW.type = 'postal_code' THEN - IF NEW.address is NULL OR NOT NEW.address ? 'postcode' THEN - -- postcode was deleted, no longer retain in placex - DELETE FROM placex where place_id = existingplacex.place_id; - RETURN NULL; - END IF; - - NEW.name := hstore('ref', NEW.address->'postcode'); - END IF; - -- Boundaries must be areas. IF NEW.class in ('boundary') AND ST_GeometryType(NEW.geometry) not in ('ST_Polygon','ST_MultiPolygon') diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 0494257e..5ac5fdca 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -304,7 +304,6 @@ DECLARE BEGIN IF bnd.rank_search >= 26 or bnd.rank_address = 0 or ST_GeometryType(bnd.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon') - or bnd.type IN ('postcode', 'postal_code') THEN RETURN NULL; END IF; @@ -359,8 +358,7 @@ BEGIN -- 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.extratags->'place' != 'postcode' - and bnd_name is not null + IF bnd.extratags ? 'place' and bnd_name is not null THEN FOR linked_placex IN SELECT * FROM placex @@ -393,7 +391,6 @@ BEGIN AND placex.class = 'place' 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 matching place node %', linked_placex.osm_id;{% endif %} @@ -697,17 +694,7 @@ BEGIN ELSE is_area := ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon'); - IF NEW.class in ('place','boundary') - AND NEW.type in ('postcode','postal_code') - THEN - IF NEW.address IS NULL OR NOT NEW.address ? 'postcode' THEN - -- most likely just a part of a multipolygon postcode boundary, throw it away - RETURN NULL; - END IF; - - NEW.name := hstore('ref', NEW.address->'postcode'); - - ELSEIF NEW.class = 'highway' AND is_area AND NEW.name is null + IF NEW.class = 'highway' AND is_area AND NEW.name is null AND NEW.extratags ? 'area' AND NEW.extratags->'area' = 'yes' THEN RETURN NULL; @@ -877,16 +864,6 @@ BEGIN and (linked_place is null or place_id != linked_place); -- update not necessary for osmline, cause linked_place_id does not exist - -- Postcodes are just here to compute the centroids. They are not searchable - -- unless they are a boundary=postal_code. - -- There was an error in the style so that boundary=postal_code used to be - -- imported as place=postcode. That's why relations are allowed to pass here. - -- This can go away in a couple of versions. - IF NEW.class = 'place' and NEW.type = 'postcode' and NEW.osm_type != 'R' THEN - NEW.token_info := null; - RETURN NEW; - END IF; - -- Compute a preliminary centroid. NEW.centroid := get_center_point(NEW.geometry); @@ -1286,8 +1263,6 @@ BEGIN END IF; ELSEIF NEW.rank_address > 25 THEN max_rank := 25; - ELSEIF NEW.class in ('place','boundary') and NEW.type in ('postcode','postal_code') THEN - max_rank := NEW.rank_search; ELSE max_rank := NEW.rank_address; END IF; diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql index 8a4e91cd..0fdc2001 100644 --- a/lib-sql/indices.sql +++ b/lib-sql/indices.sql @@ -2,7 +2,7 @@ -- -- This file is part of Nominatim. (https://nominatim.org) -- --- Copyright (C) 2022 by the Nominatim developer community. +-- Copyright (C) 2025 by the Nominatim developer community. -- For a full list of authors see the git log. -- Indices used only during search and update. @@ -21,30 +21,25 @@ CREATE INDEX IF NOT EXISTS idx_placex_parent_place_id ON placex USING BTREE (parent_place_id) {{db.tablespace.search_index}} WHERE parent_place_id IS NOT NULL; --- --- Used to find postcode areas after a search in location_postcode. -CREATE INDEX IF NOT EXISTS idx_placex_postcode_areas - ON placex USING BTREE (country_code, postcode) {{db.tablespace.search_index}} - WHERE osm_type = 'R' AND class = 'boundary' AND type = 'postal_code'; ---- CREATE INDEX IF NOT EXISTS idx_placex_geometry ON placex USING GIST (geometry) {{db.tablespace.search_index}}; +--- -- Index is needed during import but can be dropped as soon as a full -- geometry index is in place. The partial index is almost as big as the full -- index. ---- DROP INDEX IF EXISTS idx_placex_geometry_lower_rank_ways; --- 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') - AND rank_address between 4 and 25 AND type != 'postcode' + AND rank_address between 4 and 25 AND name is not null AND indexed_status = 0 AND linked_place_id is null; --- -- used in reverse large area lookup CREATE INDEX IF NOT EXISTS idx_placex_geometry_reverse_lookupPlaceNode ON placex USING gist (ST_Buffer(geometry, reverse_place_diameter(rank_search))) {{db.tablespace.search_index}} - WHERE rank_address between 4 and 25 AND type != 'postcode' + WHERE rank_address between 4 and 25 AND name is not null AND linked_place_id is null AND osm_type = 'N'; --- CREATE INDEX IF NOT EXISTS idx_osmline_parent_place_id diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index 956009ac..3a00acd1 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -2,7 +2,7 @@ -- -- This file is part of Nominatim. (https://nominatim.org) -- --- Copyright (C) 2022 by the Nominatim developer community. +-- Copyright (C) 2025 by the Nominatim developer community. -- For a full list of authors see the git log. drop table if exists import_status; @@ -192,8 +192,7 @@ CREATE INDEX idx_placex_geometry_buildings ON placex -- - linking of place nodes with same type to boundaries CREATE INDEX idx_placex_geometry_placenode ON placex USING SPGIST (geometry) {{db.tablespace.address_index}} - WHERE osm_type = 'N' and rank_search < 26 - and class = 'place' and type != 'postcode'; + WHERE osm_type = 'N' and rank_search < 26 and class = 'place'; -- Usage: - is node part of a way? -- - find parent of interpolation spatially diff --git a/src/nominatim_api/sql/sqlalchemy_functions.py b/src/nominatim_api/sql/sqlalchemy_functions.py index 00830f33..424e6031 100644 --- a/src/nominatim_api/sql/sqlalchemy_functions.py +++ b/src/nominatim_api/sql/sqlalchemy_functions.py @@ -2,7 +2,7 @@ # # This file is part of Nominatim. (https://nominatim.org) # -# Copyright (C) 2024 by the Nominatim developer community. +# Copyright (C) 2025 by the Nominatim developer community. # For a full list of authors see the git log. """ Custom functions and expressions for SQLAlchemy. @@ -32,7 +32,6 @@ def _default_intersects(element: PlacexGeometryReverseLookuppolygon, compiler: 'sa.Compiled', **kw: Any) -> str: return ("(ST_GeometryType(placex.geometry) in ('ST_Polygon', 'ST_MultiPolygon')" " AND placex.rank_address between 4 and 25" - " AND placex.type != 'postcode'" " AND placex.name is not null" " AND placex.indexed_status = 0" " AND placex.linked_place_id is null)") @@ -43,7 +42,6 @@ def _sqlite_intersects(element: PlacexGeometryReverseLookuppolygon, compiler: 'sa.Compiled', **kw: Any) -> str: return ("(ST_GeometryType(placex.geometry) in ('POLYGON', 'MULTIPOLYGON')" " AND placex.rank_address between 4 and 25" - " AND placex.type != 'postcode'" " AND placex.name is not null" " AND placex.indexed_status = 0" " AND placex.linked_place_id is null)") @@ -64,7 +62,6 @@ def default_reverse_place_diameter(element: IntersectsReverseDistance, compiler: 'sa.Compiled', **kw: Any) -> str: table = element.tablename return f"({table}.rank_address between 4 and 25"\ - f" AND {table}.type != 'postcode'"\ f" AND {table}.name is not null"\ f" AND {table}.linked_place_id is null"\ f" AND {table}.osm_type = 'N'" + \ @@ -79,7 +76,6 @@ def sqlite_reverse_place_diameter(element: IntersectsReverseDistance, table = element.tablename return (f"({table}.rank_address between 4 and 25" - f" AND {table}.type != 'postcode'" f" AND {table}.name is not null" f" AND {table}.linked_place_id is null" f" AND {table}.osm_type = 'N'" -- 2.39.5