From 36b1660121a9261998a2b36cff741587ce5b3c5b Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Thu, 15 Feb 2024 16:19:56 +0100 Subject: [PATCH] add support for new middle table format of osm2pgsql Functions are adapted according to the format detected from the osm2pgsql property table. --- lib-sql/functions/placex_triggers.sql | 68 ++++++++++++++++++++++ lib-sql/functions/utils.sql | 20 +++++++ lib-sql/tables.sql | 10 +++- nominatim/db/sql_preprocessor.py | 16 ++++- test/bdd/steps/steps_db_ops.py | 84 ++++++++++++++++++--------- 5 files changed, 166 insertions(+), 32 deletions(-) diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 530bf541..5d32f496 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -119,12 +119,14 @@ CREATE OR REPLACE FUNCTION find_associated_street(poi_osm_type CHAR(1), AS $$ DECLARE location RECORD; + member JSONB; parent RECORD; result BIGINT; distance FLOAT; new_distance FLOAT; waygeom GEOMETRY; BEGIN +{% if db.middle_db_format == '1' %} FOR location IN SELECT members FROM planet_osm_rels WHERE parts @> ARRAY[poi_osm_id] @@ -161,6 +163,40 @@ BEGIN END LOOP; END LOOP; +{% else %} + FOR member IN + SELECT value FROM planet_osm_rels r, LATERAL jsonb_array_elements(members) + WHERE planet_osm_member_ids(members, poi_osm_type::char(1)) && ARRAY[poi_osm_id] + and tags->>'type' = 'associatedStreet' + and value->>'role' = 'street' + LOOP + FOR parent IN + SELECT place_id, geometry + FROM placex + WHERE osm_type = (member->>'type')::char(1) + and osm_id = (member->>'ref')::bigint + and name is not null + and rank_search between 26 and 27 + LOOP + -- Find the closest 'street' member. + -- Avoid distance computation for the frequent case where there is + -- only one street member. + IF waygeom is null THEN + result := parent.place_id; + waygeom := parent.geometry; + ELSE + distance := coalesce(distance, ST_Distance(waygeom, bbox)); + new_distance := ST_Distance(parent.geometry, bbox); + IF new_distance < distance THEN + distance := new_distance; + result := parent.place_id; + waygeom := parent.geometry; + END IF; + END IF; + END LOOP; + END LOOP; +{% endif %} + RETURN result; END; $$ @@ -257,7 +293,11 @@ CREATE OR REPLACE FUNCTION find_linked_place(bnd placex) RETURNS placex AS $$ DECLARE +{% if db.middle_db_format == '1' %} relation_members TEXT[]; +{% else %} + relation_members JSONB; +{% endif %} rel_member RECORD; linked_placex placex%ROWTYPE; bnd_name TEXT; @@ -749,7 +789,11 @@ CREATE OR REPLACE FUNCTION placex_update() DECLARE i INTEGER; location RECORD; +{% if db.middle_db_format == '1' %} relation_members TEXT[]; +{% else %} + relation_member JSONB; +{% endif %} geom GEOMETRY; parent_address_level SMALLINT; @@ -971,6 +1015,7 @@ BEGIN -- waterway ways are linked when they are part of a relation and have the same class/type IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN +{% if db.middle_db_format == '1' %} FOR relation_members IN select members from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[] LOOP FOR i IN 1..array_upper(relation_members, 1) BY 2 LOOP @@ -989,6 +1034,29 @@ BEGIN END IF; END LOOP; END LOOP; +{% else %} + FOR relation_member IN + SELECT value FROM planet_osm_rels r, LATERAL jsonb_array_elements(r.members) + WHERE r.id = NEW.osm_id + LOOP + IF relation_member->>'role' IN ('', 'main_stream', 'side_stream') + and relation_member->>'type' = 'W' + THEN + {% if debug %}RAISE WARNING 'waterway parent %, child %', NEW.osm_id, relation_member;{% endif %} + FOR linked_node_id IN + SELECT place_id FROM placex + WHERE osm_type = 'W' and osm_id = (relation_member->>'ref')::bigint + and class = NEW.class and type in ('river', 'stream', 'canal', 'drain', 'ditch') + and (relation_member->>'role' != 'side_stream' or NEW.name->'name' = name->'name') + LOOP + UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id; + {% if 'search_name' in db.tables %} + DELETE FROM search_name WHERE place_id = linked_node_id; + {% endif %} + END LOOP; + END IF; + END LOOP; +{% endif %} {% if debug %}RAISE WARNING 'Waterway processed';{% endif %} END IF; diff --git a/lib-sql/functions/utils.sql b/lib-sql/functions/utils.sql index ff2f037d..a8ebcc41 100644 --- a/lib-sql/functions/utils.sql +++ b/lib-sql/functions/utils.sql @@ -73,6 +73,26 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; + +CREATE OR REPLACE FUNCTION get_rel_node_members(members JSONB, memberLabels TEXT[]) + RETURNS SETOF BIGINT + AS $$ +DECLARE + member JSONB; +BEGIN + FOR member IN SELECT * FROM jsonb_array_elements(members) + LOOP + IF member->>'type' = 'N' and member->>'role' = ANY(memberLabels) THEN + RETURN NEXT (member->>'ref')::bigint; + END IF; + END LOOP; + + RETURN; +END; +$$ +LANGUAGE plpgsql IMMUTABLE; + + -- Copy 'name' to or from the default language. -- -- \param country_code Country code of the object being named. diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index 17216b50..eafed6d8 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -298,7 +298,15 @@ CREATE TABLE IF NOT EXISTS wikipedia_redirect ( -- osm2pgsql does not create indexes on the middle tables for Nominatim -- Add one for lookup of associated street relations. -CREATE INDEX planet_osm_rels_parts_associated_idx ON planet_osm_rels USING gin(parts) WHERE tags @> ARRAY['associatedStreet']; +{% if db.middle_db_format == '1' %} +CREATE INDEX planet_osm_rels_parts_associated_idx ON planet_osm_rels USING gin(parts) + {{db.tablespace.address_index}} + WHERE tags @> ARRAY['associatedStreet']; +{% else %} +CREATE INDEX planet_osm_rels_relation_members_idx ON planet_osm_rels USING gin(planet_osm_member_ids(members, 'R'::character(1))) + WITH (fastupdate=off) + {{db.tablespace.address_index}}; +{% endif %} -- Needed for lookups if a node is part of an interpolation. CREATE INDEX IF NOT EXISTS idx_place_interpolations diff --git a/nominatim/db/sql_preprocessor.py b/nominatim/db/sql_preprocessor.py index 3762d82e..d3fb3058 100644 --- a/nominatim/db/sql_preprocessor.py +++ b/nominatim/db/sql_preprocessor.py @@ -7,7 +7,7 @@ """ Preprocessing of SQL files. """ -from typing import Set, Dict, Any +from typing import Set, Dict, Any, cast import jinja2 from nominatim.db.connection import Connection @@ -28,13 +28,24 @@ def _get_partitions(conn: Connection) -> Set[int]: def _get_tables(conn: Connection) -> Set[str]: """ Return the set of tables currently in use. - Only includes non-partitioned """ with conn.cursor() as cur: cur.execute("SELECT tablename FROM pg_tables WHERE schemaname = 'public'") return set((row[0] for row in list(cur))) +def _get_middle_db_format(conn: Connection, tables: Set[str]) -> str: + """ Returns the version of the slim middle tables. + """ + if 'osm2pgsql_properties' not in tables: + return '1' + + with conn.cursor() as cur: + cur.execute("SELECT value FROM osm2pgsql_properties WHERE property = 'db_format'") + row = cur.fetchone() + + return cast(str, row[0]) if row is not None else '1' + def _setup_tablespace_sql(config: Configuration) -> Dict[str, str]: """ Returns a dict with tablespace expressions for the different tablespace @@ -84,6 +95,7 @@ class SQLPreprocessor: db_info['tables'] = _get_tables(conn) db_info['reverse_only'] = 'search_name' not in db_info['tables'] db_info['tablespace'] = _setup_tablespace_sql(config) + db_info['middle_db_format'] = _get_middle_db_format(conn, db_info['tables']) self.env.globals['config'] = config self.env.globals['db'] = db_info diff --git a/test/bdd/steps/steps_db_ops.py b/test/bdd/steps/steps_db_ops.py index 14ae5d52..e6122bfe 100644 --- a/test/bdd/steps/steps_db_ops.py +++ b/test/bdd/steps/steps_db_ops.py @@ -52,33 +52,52 @@ def add_data_to_planet_relations(context): for tests on data that looks up members. """ with context.db.cursor() as cur: - for r in context.table: - last_node = 0 - last_way = 0 - parts = [] - if r['members']: - members = [] - for m in r['members'].split(','): - mid = NominatimID(m) - if mid.typ == 'N': - parts.insert(last_node, int(mid.oid)) - last_node += 1 - last_way += 1 - elif mid.typ == 'W': - parts.insert(last_way, int(mid.oid)) - last_way += 1 - else: - parts.append(int(mid.oid)) - - members.extend((mid.typ.lower() + mid.oid, mid.cls or '')) - else: - members = None - - tags = chain.from_iterable([(h[5:], r[h]) for h in r.headings if h.startswith("tags+")]) - - cur.execute("""INSERT INTO planet_osm_rels (id, way_off, rel_off, parts, members, tags) - VALUES (%s, %s, %s, %s, %s, %s)""", - (r['id'], last_node, last_way, parts, members, list(tags))) + cur.execute("SELECT value FROM osm2pgsql_properties WHERE property = 'db_format'") + row = cur.fetchone() + if row is None or row[0] == '1': + for r in context.table: + last_node = 0 + last_way = 0 + parts = [] + if r['members']: + members = [] + for m in r['members'].split(','): + mid = NominatimID(m) + if mid.typ == 'N': + parts.insert(last_node, int(mid.oid)) + last_node += 1 + last_way += 1 + elif mid.typ == 'W': + parts.insert(last_way, int(mid.oid)) + last_way += 1 + else: + parts.append(int(mid.oid)) + + members.extend((mid.typ.lower() + mid.oid, mid.cls or '')) + else: + members = None + + tags = chain.from_iterable([(h[5:], r[h]) for h in r.headings if h.startswith("tags+")]) + + cur.execute("""INSERT INTO planet_osm_rels (id, way_off, rel_off, parts, members, tags) + VALUES (%s, %s, %s, %s, %s, %s)""", + (r['id'], last_node, last_way, parts, members, list(tags))) + else: + for r in context.table: + if r['members']: + members = [] + for m in r['members'].split(','): + mid = NominatimID(m) + members.append({'ref': mid.oid, 'role': mid.cls or '', 'type': mid.typ}) + else: + members = [] + + tags = {h[5:]: r[h] for h in r.headings if h.startswith("tags+")} + + cur.execute("""INSERT INTO planet_osm_rels (id, tags, members) + VALUES (%s, %s, %s)""", + (r['id'], psycopg2.extras.Json(tags), + psycopg2.extras.Json(members))) @given("the ways") def add_data_to_planet_ways(context): @@ -86,12 +105,19 @@ def add_data_to_planet_ways(context): tests on that that looks up node ids in this table. """ with context.db.cursor() as cur: + cur.execute("SELECT value FROM osm2pgsql_properties WHERE property = 'db_format'") + row = cur.fetchone() + json_tags = row is not None and row[0] != '1' for r in context.table: - tags = chain.from_iterable([(h[5:], r[h]) for h in r.headings if h.startswith("tags+")]) + if json_tags: + tags = psycopg2.extras.Json({h[5:]: r[h] for h in r.headings if h.startswith("tags+")}) + else: + tags = list(chain.from_iterable([(h[5:], r[h]) + for h in r.headings if h.startswith("tags+")])) nodes = [ int(x.strip()) for x in r['nodes'].split(',') ] cur.execute("INSERT INTO planet_osm_ways (id, nodes, tags) VALUES (%s, %s, %s)", - (r['id'], nodes, list(tags))) + (r['id'], nodes, tags)) ################################ WHEN ################################## -- 2.45.1