From 823ad5d279325878ebe149ba2fe3ad313d6401e4 Mon Sep 17 00:00:00 2001 From: Emily Love Watson Date: Thu, 14 Aug 2025 14:37:24 -0500 Subject: [PATCH] Update entrances schema --- lib-sql/functions/placex_triggers.sql | 17 +++++++--- lib-sql/tables.sql | 7 ++--- src/nominatim_api/results.py | 21 ++++++++++++- src/nominatim_api/sql/sqlalchemy_schema.py | 5 +++ src/nominatim_api/types.py | 27 ++++++++++++++++ src/nominatim_api/v1/format.py | 3 ++ src/nominatim_api/v1/format_json.py | 9 ++++++ src/nominatim_api/v1/format_xml.py | 7 +++++ src/nominatim_api/v1/helpers.py | 2 ++ src/nominatim_api/v1/server_glue.py | 5 +++ src/nominatim_db/clicmd/api.py | 8 +++++ src/nominatim_db/clicmd/args.py | 1 + src/nominatim_db/tools/migration.py | 33 ++++++++++---------- test/bdd/features/api/details/params.feature | 8 +++++ test/bdd/features/api/search/params.feature | 14 +++++++++ 15 files changed, 140 insertions(+), 27 deletions(-) diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 26a82d2f..fe9983ba 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -818,6 +818,8 @@ DECLARE nameaddress_vector INTEGER[]; addr_nameaddress_vector INTEGER[]; + entrances JSONB; + linked_place BIGINT; linked_node_id BIGINT; @@ -880,12 +882,17 @@ BEGIN 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 + IF NEW.osm_type = 'W' and (NEW.rank_search > 27 or NEW.class IN ('landuse', 'leisure')) THEN + SELECT jsonb_agg(jsonb_build_object('osm_id', osm_id, 'type', type, 'lat', ST_Y(geometry), 'lon', ST_X(geometry), 'extratags', extratags)) FROM place - WHERE osm_id IN (SELECT unnest(nodes) FROM planet_osm_ways WHERE id=NEW.osm_id) AND class IN ('routing:entrance', 'entrance'); + WHERE osm_id IN (SELECT unnest(nodes) FROM planet_osm_ways WHERE id=NEW.osm_id) AND class IN ('routing:entrance', 'entrance') + INTO entrances; + IF entrances IS NOT NULL THEN + INSERT INTO place_entrance (place_id, entrances) + SELECT NEW.place_id, entrances + ON CONFLICT (place_id) DO UPDATE + SET entrances = excluded.entrances; + END IF; END IF; -- recalculate country and partition diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index efe08c96..4b11e1a2 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -248,11 +248,10 @@ GRANT SELECT ON location_postcode TO "{{config.DATABASE_WEBUSER}}" ; 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 + entrances JSONB NOT NULL ); -CREATE UNIQUE INDEX idx_place_entrance_id ON place_entrance USING BTREE (place_id, osm_node_id) {{db.tablespace.search_index}}; +CREATE UNIQUE INDEX idx_place_entrance_place_id ON place_entrance + USING BTREE (place_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 diff --git a/src/nominatim_api/results.py b/src/nominatim_api/results.py index 1b74b5aa..918b78c2 100644 --- a/src/nominatim_api/results.py +++ b/src/nominatim_api/results.py @@ -23,7 +23,7 @@ import sqlalchemy as sa from .typing import SaSelect, SaRow from .sql.sqlalchemy_types import Geometry -from .types import Point, Bbox, LookupDetails +from .types import Point, Bbox, LookupDetails, EntranceDetails from .connection import SearchConnection from .logging import log @@ -206,6 +206,8 @@ class BaseResult: name_keywords: Optional[WordInfos] = None address_keywords: Optional[WordInfos] = None + entrances: Optional[List[EntranceDetails]] = None + geometry: Dict[str, str] = dataclasses.field(default_factory=dict) @property @@ -466,6 +468,10 @@ async def add_result_details(conn: SearchConnection, results: List[BaseResultT], log().comment('Query parent places') for result in results: await complete_parented_places(conn, result) + if details.entrances: + log().comment('Query entrances details') + for result in results: + await complete_entrances_details(conn, result) if details.keywords: log().comment('Query keywords') for result in results: @@ -717,6 +723,19 @@ async def complete_linked_places(conn: SearchConnection, result: BaseResult) -> result.linked_rows.append(_result_row_to_address_row(row)) +async def complete_entrances_details(conn: SearchConnection, result: BaseResult) -> None: + """ Retrieve information about tagged entrances for this place. + """ + if result.source_table != SourceTable.PLACEX: + return + + t = conn.t.place_entrance + sql = sa.select(t.c.entrances).where(t.c.place_id == result.place_id) + + for results in await conn.execute(sql): + result.entrances = [EntranceDetails(**r) for r in results[0]] + + async def complete_keywords(conn: SearchConnection, result: BaseResult) -> None: """ Retrieve information about the search terms used for this place. diff --git a/src/nominatim_api/sql/sqlalchemy_schema.py b/src/nominatim_api/sql/sqlalchemy_schema.py index a8989c4c..e7a9ecf3 100644 --- a/src/nominatim_api/sql/sqlalchemy_schema.py +++ b/src/nominatim_api/sql/sqlalchemy_schema.py @@ -127,3 +127,8 @@ class SearchTables: sa.Column('step', sa.SmallInteger), sa.Column('linegeo', Geometry), sa.Column('postcode', sa.Text)) + + self.place_entrance = sa.Table( + 'place_entrance', meta, + sa.Column('place_id', sa.BigInteger), + sa.Column('entrances', KeyValueStore)) diff --git a/src/nominatim_api/types.py b/src/nominatim_api/types.py index b3048fc9..d126d0cc 100644 --- a/src/nominatim_api/types.py +++ b/src/nominatim_api/types.py @@ -401,6 +401,9 @@ class LookupDetails: for, i.e. all places for which it provides the address details. Only POI places can have parents. """ + entrances: bool = False + """ Get detailed information about the tagged entrances for the result. + """ keywords: bool = False """ Add information about the search terms used for this place. """ @@ -548,3 +551,27 @@ class SearchDetails(LookupDetails): true when layer restriction has been disabled completely. """ return self.layers is None or bool(self.layers & layer) + + +@dataclasses.dataclass +class EntranceDetails: + """ Reference a place by its OSM ID and potentially the basic category. + + The OSM ID may refer to places in the main table placex and OSM + interpolation lines. + """ + osm_id: int + """ The OSM ID of the object. + """ + type: str + """ The value of the OSM entrance tag (i.e. yes, main, secondary, etc.). + """ + lat: float + """ The latitude of the entrance node. + """ + lon: float + """ The longitude of the entrance node. + """ + extratags: Dict[str, str] + """ The longitude of the entrance node. + """ diff --git a/src/nominatim_api/v1/format.py b/src/nominatim_api/v1/format.py index 2657d369..2e9aae06 100644 --- a/src/nominatim_api/v1/format.py +++ b/src/nominatim_api/v1/format.py @@ -196,6 +196,9 @@ def _format_details_json(result: DetailedResult, options: Mapping[str, Any]) -> else: _add_address_rows(out, 'hierarchy', result.parented_rows, locales) + if result.entrances is not None: + out.keyval('entrances', result.entrances) + out.end_object() return out() diff --git a/src/nominatim_api/v1/format_json.py b/src/nominatim_api/v1/format_json.py index 9d7c3bd4..18e93dd0 100644 --- a/src/nominatim_api/v1/format_json.py +++ b/src/nominatim_api/v1/format_json.py @@ -107,6 +107,9 @@ def format_base_json(results: Union[ReverseResults, SearchResults], _write_typed_address(out, result.address_rows, result.country_code) out.end_object().next() + if options.get('entrances', False) and result.entrances: + out.keyval('entrances', result.entrances) + if options.get('extratags', False): out.keyval('extratags', result.extratags) @@ -180,6 +183,9 @@ def format_base_geojson(results: Union[ReverseResults, SearchResults], _write_typed_address(out, result.address_rows, result.country_code) out.end_object().next() + if options.get('entrances', False): + out.keyval('entrances', result.entrances) + if options.get('extratags', False): out.keyval('extratags', result.extratags) @@ -251,6 +257,9 @@ def format_base_geocodejson(results: Union[ReverseResults, SearchResults], out.keyval(f"level{line.admin_level}", line.local_name) out.end_object().next() + if options.get('entrances', False): + out.keyval('entrances', result.entrances) + if options.get('extratags', False): out.keyval('extra', result.extratags) diff --git a/src/nominatim_api/v1/format_xml.py b/src/nominatim_api/v1/format_xml.py index ed6aca0a..17a1131c 100644 --- a/src/nominatim_api/v1/format_xml.py +++ b/src/nominatim_api/v1/format_xml.py @@ -8,6 +8,7 @@ Helper functions for output of results in XML format. """ from typing import Mapping, Any, Optional, Union +import dataclasses import datetime as dt import xml.etree.ElementTree as ET @@ -122,4 +123,10 @@ def format_base_xml(results: Union[ReverseResults, SearchResults], for k, v in result.names.items(): ET.SubElement(eroot, 'name', attrib={'desc': k}).text = v + if options.get('entrances', False): + eroot = ET.SubElement(root if simple else place, 'entrances') + if result.entrances: + for entrance_detail in result.entrances: + ET.SubElement(eroot, 'entrance', attrib=dataclasses.asdict(entrance_detail)) + return '\n' + ET.tostring(root, encoding='unicode') diff --git a/src/nominatim_api/v1/helpers.py b/src/nominatim_api/v1/helpers.py index 2c6ddc99..66a1c998 100644 --- a/src/nominatim_api/v1/helpers.py +++ b/src/nominatim_api/v1/helpers.py @@ -72,6 +72,8 @@ def extend_query_parts(queryparts: Dict[str, Any], details: Dict[str, Any], queryparts['polygon_text'] = '1' if parsed.address_details: queryparts['addressdetails'] = '1' + if parsed.entrances: + queryparts['entrances'] = '1' if namedetails: queryparts['namedetails'] = '1' if extratags: diff --git a/src/nominatim_api/v1/server_glue.py b/src/nominatim_api/v1/server_glue.py index 99f7dc48..760afc69 100644 --- a/src/nominatim_api/v1/server_glue.py +++ b/src/nominatim_api/v1/server_glue.py @@ -158,6 +158,7 @@ async def details_endpoint(api: NominatimAPIAsync, params: ASGIAdaptor) -> Any: result = await api.details(place, address_details=params.get_bool('addressdetails', False), + entrances=params.get_bool('entrances', False), linked_places=params.get_bool('linkedplaces', True), parented_places=params.get_bool('hierarchy', False), keywords=params.get_bool('keywords', False), @@ -216,6 +217,7 @@ async def reverse_endpoint(api: NominatimAPIAsync, params: ASGIAdaptor) -> Any: fmt_options = {'query': query, 'extratags': params.get_bool('extratags', False), 'namedetails': params.get_bool('namedetails', False), + 'entrances': params.get_bool('entrances', False), 'addressdetails': params.get_bool('addressdetails', True)} output = params.formatting().format_result(ReverseResults([result] if result else []), @@ -252,6 +254,7 @@ async def lookup_endpoint(api: NominatimAPIAsync, params: ASGIAdaptor) -> Any: fmt_options = {'extratags': params.get_bool('extratags', False), 'namedetails': params.get_bool('namedetails', False), + 'entrances': params.get_bool('entrances', False), 'addressdetails': params.get_bool('addressdetails', True)} output = params.formatting().format_result(results, fmt, fmt_options) @@ -298,6 +301,7 @@ async def search_endpoint(api: NominatimAPIAsync, params: ASGIAdaptor) -> Any: details = parse_geometry_details(params, fmt) details['countries'] = params.get('countrycodes', None) + details['entrances'] = params.get_bool('entrances', False) details['excluded'] = params.get('exclude_place_ids', None) details['viewbox'] = params.get('viewbox', None) or params.get('viewboxlbrt', None) details['bounded_viewbox'] = params.get_bool('bounded', False) @@ -363,6 +367,7 @@ async def search_endpoint(api: NominatimAPIAsync, params: ASGIAdaptor) -> Any: 'viewbox': queryparts.get('viewbox'), 'extratags': params.get_bool('extratags', False), 'namedetails': params.get_bool('namedetails', False), + 'entrances': params.get_bool('entrances', False), 'addressdetails': params.get_bool('addressdetails', False)} output = params.formatting().format_result(results, fmt, fmt_options) diff --git a/src/nominatim_db/clicmd/api.py b/src/nominatim_db/clicmd/api.py index f5a3926f..36b84acd 100644 --- a/src/nominatim_db/clicmd/api.py +++ b/src/nominatim_db/clicmd/api.py @@ -41,6 +41,7 @@ EXTRADATA_PARAMS = ( ('addressdetails', 'Include a breakdown of the address into elements'), ('extratags', ("Include additional information if available " "(e.g. wikipedia link, opening hours)")), + ('entrances', 'Include a list of tagged entrance nodes'), ('namedetails', 'Include a list of alternative names') ) @@ -196,6 +197,7 @@ class APISearch: 'excluded': args.exclude_place_ids, 'viewbox': args.viewbox, 'bounded_viewbox': args.bounded, + 'entrances': args.entrances, } if args.query: @@ -225,6 +227,7 @@ class APISearch: _print_output(formatter, results, args.format, {'extratags': args.extratags, 'namedetails': args.namedetails, + 'entrances': args.entrances, 'addressdetails': args.addressdetails}) return 0 @@ -295,6 +298,7 @@ class APIReverse: _print_output(formatter, napi.ReverseResults([result]), args.format, {'extratags': args.extratags, 'namedetails': args.namedetails, + 'entrances': args.entrances, 'addressdetails': args.addressdetails}) return 0 @@ -358,6 +362,7 @@ class APILookup: _print_output(formatter, results, args.format, {'extratags': args.extratags, 'namedetails': args.namedetails, + 'entrances': args.entrances, 'addressdetails': args.addressdetails}) return 0 @@ -395,6 +400,8 @@ class APIDetails: help='Include a list of name keywords and address keywords') group.add_argument('--linkedplaces', action='store_true', help='Include a details of places that are linked with this one') + group.add_argument('--entrances', action='store_true', + help='Include a list of tagged entrance nodes') group.add_argument('--hierarchy', action='store_true', help='Include details of places lower in the address hierarchy') group.add_argument('--group_hierarchy', action='store_true', @@ -434,6 +441,7 @@ class APIDetails: with napi.NominatimAPI(args.project_dir) as api: result = api.details(place, address_details=args.addressdetails, + entrances=args.entrances, linked_places=args.linkedplaces, parented_places=args.hierarchy, keywords=args.keywords, diff --git a/src/nominatim_db/clicmd/args.py b/src/nominatim_db/clicmd/args.py index 5c6a806a..ee9d8fec 100644 --- a/src/nominatim_db/clicmd/args.py +++ b/src/nominatim_db/clicmd/args.py @@ -142,6 +142,7 @@ class NominatimArgs: format: str list_formats: bool addressdetails: bool + entrances: bool extratags: bool namedetails: bool lang: Optional[str] diff --git a/src/nominatim_db/tools/migration.py b/src/nominatim_db/tools/migration.py index d723246e..853eff7c 100644 --- a/src/nominatim_db/tools/migration.py +++ b/src/nominatim_db/tools/migration.py @@ -124,20 +124,19 @@ def create_place_entrance_table(conn: Connection, config: Configuration, **_: An """ 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'); - """) + -- Table to store location of entrance nodes + DROP TABLE IF EXISTS place_entrance; + CREATE TABLE place_entrance ( + place_id BIGINT NOT NULL, + entrances JSONB NOT NULL + ); + CREATE UNIQUE INDEX idx_place_entrance_place_id ON place_entrance + USING BTREE (place_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/test/bdd/features/api/details/params.feature b/test/bdd/features/api/details/params.feature index 1212e70a..5f76ded1 100644 --- a/test/bdd/features/api/details/params.feature +++ b/test/bdd/features/api/details/params.feature @@ -30,6 +30,14 @@ Feature: Object details And the result is valid json And the result has attributes address + Scenario: Details with entrances + When sending v1/details + | osmtype | osmid | entrances | + | W | 429210603 | 1 | + Then a HTTP 200 is returned + And the result is valid json + And the result has attributes entrances + Scenario: Details with linkedplaces When sending v1/details | osmtype | osmid | linkedplaces | diff --git a/test/bdd/features/api/search/params.feature b/test/bdd/features/api/search/params.feature index d2f41f48..04d6dc1e 100644 --- a/test/bdd/features/api/search/params.feature +++ b/test/bdd/features/api/search/params.feature @@ -63,6 +63,20 @@ Feature: Search queries | geojson | geojson | | xml | xml | + Scenario Outline: Search with entrances + When sending v1/search with format + | q | entrances | + | Saint Joseph Catholic Church | 1 | + Then a HTTP 200 is returned + And the result is valid + + Examples: + | format | outformat | + | json | json | + | jsonv2 | json | + | geojson | geojson | + | xml | xml | + Scenario: Coordinate search with addressdetails When geocoding "47.12400621,9.6047552" | accept-language | -- 2.39.5