From 048d571e4618697eaa1301980503c0a0856ac0ae Mon Sep 17 00:00:00 2001 From: Emily Love Watson Date: Tue, 5 Aug 2025 10:23:45 -0500 Subject: [PATCH] Index and return entrance coordinates for indexed locations --- lib-lua/themes/nominatim/presets.lua | 4 ++++ lib-sql/functions/placex_triggers.sql | 14 ++++++++++++++ lib-sql/tables.sql | 17 +++++++++++++++++ src/nominatim_db/tools/migration.py | 26 ++++++++++++++++++++++++++ src/nominatim_db/version.py | 2 +- 5 files changed, 62 insertions(+), 1 deletion(-) diff --git a/lib-lua/themes/nominatim/presets.lua b/lib-lua/themes/nominatim/presets.lua index 5331e372..5aed0f57 100644 --- a/lib-lua/themes/nominatim/presets.lua +++ b/lib-lua/themes/nominatim/presets.lua @@ -172,6 +172,10 @@ module.MAIN_TAGS_POIS = function (group) no = group, yes = group, fire_hydrant = group}, + entrance = {'always', + no = group}, + ["routing:entrance"] = {'always', + no = group}, healthcare = {'fallback', yes = group, no = group}, diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index db7e7c4e..26a82d2f 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -667,6 +667,11 @@ DECLARE BEGIN {% if debug %}RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;{% endif %} + IF NEW.class IN ('routing:entrance', 'entrance') THEN + -- We don't need entrance nodes in the placex table. + RETURN NULL; + END IF; + NEW.place_id := nextval('seq_place'); NEW.indexed_status := 1; --STATUS_NEW @@ -874,6 +879,15 @@ BEGIN -- Compute a preliminary centroid. NEW.centroid := get_center_point(NEW.geometry); + -- Record the entrance node locations + IF NEW.osm_type = 'W' THEN + DELETE FROM place_entrance WHERE place_id = NEW.place_id; + INSERT INTO place_entrance (place_id, osm_node_id, type, geometry) + SELECT NEW.place_id, osm_id, type, geometry + FROM place + WHERE osm_id IN (SELECT unnest(nodes) FROM planet_osm_ways WHERE id=NEW.osm_id) AND class IN ('routing:entrance', 'entrance'); + END IF; + -- recalculate country and partition IF NEW.rank_search = 4 AND NEW.address is not NULL AND NEW.address ? 'country' THEN -- for countries, believe the mapped country code, diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index cde33952..efe08c96 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -244,6 +244,23 @@ CREATE UNIQUE INDEX idx_postcode_id ON location_postcode USING BTREE (place_id) CREATE INDEX idx_postcode_geometry ON location_postcode USING GIST (geometry) {{db.tablespace.address_index}}; GRANT SELECT ON location_postcode TO "{{config.DATABASE_WEBUSER}}" ; +-- Table to store location of entrance nodes +DROP TABLE IF EXISTS place_entrance; +CREATE TABLE place_entrance ( + place_id BIGINT NOT NULL, + osm_node_id BIGINT NOT NULL, + type TEXT NOT NULL, + geometry GEOMETRY(Point, 4326) NOT NULL + ); +CREATE UNIQUE INDEX idx_place_entrance_id ON place_entrance USING BTREE (place_id, osm_node_id) {{db.tablespace.search_index}}; +GRANT SELECT ON place_entrance TO "{{config.DATABASE_WEBUSER}}" ; + +-- Create an index on the place table for lookups to populate the entrance +-- table +CREATE INDEX IF NOT EXISTS idx_place_entrance_lookup ON place + USING BTREE (osm_id) + WHERE class IN ('routing:entrance', 'entrance'); + DROP TABLE IF EXISTS import_polygon_error; CREATE TABLE import_polygon_error ( osm_id BIGINT, diff --git a/src/nominatim_db/tools/migration.py b/src/nominatim_db/tools/migration.py index 80df2932..d723246e 100644 --- a/src/nominatim_db/tools/migration.py +++ b/src/nominatim_db/tools/migration.py @@ -15,6 +15,7 @@ from ..config import Configuration from ..db import properties from ..db.connection import connect, Connection, \ table_exists, register_hstore +from ..db.sql_preprocessor import SQLPreprocessor from ..version import NominatimVersion, NOMINATIM_VERSION, parse_version from ..tokenizer import factory as tokenizer_factory from . import refresh @@ -115,3 +116,28 @@ def create_postcode_parent_index(conn: Connection, **_: Any) -> None: cur.execute("""CREATE INDEX IF NOT EXISTS idx_location_postcode_parent_place_id ON location_postcode USING BTREE (parent_place_id)""") + + +@_migration(5, 1, 99, 0) +def create_place_entrance_table(conn: Connection, config: Configuration, **_: Any) -> None: + """ Add the place_entrance table to store entrance nodes + """ + sqlp = SQLPreprocessor(conn, config) + sqlp.run_string(conn, """ +-- Table to store location of entrance nodes +CREATE TABLE IF NOT EXISTS place_entrance ( + place_id BIGINT NOT NULL, + osm_node_id BIGINT NOT NULL, + type TEXT NOT NULL, + geometry GEOMETRY(Point, 4326) NOT NULL + ); +CREATE UNIQUE INDEX IF NOT EXISTS idx_place_entrance_id + ON place_entrance USING BTREE (place_id, osm_node_id) {{db.tablespace.search_index}}; +GRANT SELECT ON place_entrance TO "{{config.DATABASE_WEBUSER}}" ; + +-- Create an index on the place table for lookups to populate the entrance +-- table +CREATE INDEX IF NOT EXISTS idx_place_entrance_lookup ON place + USING BTREE (osm_id) + WHERE class IN ('routing:entrance', 'entrance'); + """) diff --git a/src/nominatim_db/version.py b/src/nominatim_db/version.py index 070417e3..13d83af8 100644 --- a/src/nominatim_db/version.py +++ b/src/nominatim_db/version.py @@ -55,7 +55,7 @@ def parse_version(version: str) -> NominatimVersion: return NominatimVersion(*[int(x) for x in parts[:2] + parts[2].split('-')]) -NOMINATIM_VERSION = parse_version('5.1.0-0') +NOMINATIM_VERSION = parse_version('5.1.99-0') POSTGRESQL_REQUIRED_VERSION = (12, 0) POSTGIS_REQUIRED_VERSION = (3, 0) -- 2.39.5