From: Sarah Hoffmann Date: Wed, 28 Feb 2024 11:06:49 +0000 (+0100) Subject: Merge pull request #3345 from lonvia/simplify-large-geometries X-Git-Tag: v4.4.0~9 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/019a68a4bbc90834386dec3f4fac04dd64afc5ce?hp=-c Merge pull request #3345 from lonvia/simplify-large-geometries Simplify very large polygons that are not used in addresses --- 019a68a4bbc90834386dec3f4fac04dd64afc5ce diff --combined lib-sql/functions/placex_triggers.sql index 5d32f496,99eebe12..386140f4 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@@ -119,14 -119,12 +119,14 @@@ CREATE OR REPLACE FUNCTION find_associa 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] @@@ -163,40 -161,6 +163,40 @@@ 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; $$ @@@ -293,11 -257,7 +293,11 @@@ CREATE OR REPLACE FUNCTION find_linked_ 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; @@@ -718,6 -678,12 +718,12 @@@ BEGI NEW.country_code := NULL; END IF; + -- Simplify polygons with a very large memory footprint when they + -- do not take part in address computation. + IF NEW.rank_address = 0 THEN + NEW.geometry := simplify_large_polygons(NEW.geometry); + END IF; + END IF; {% if debug %}RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;{% endif %} @@@ -789,11 -755,7 +795,11 @@@ CREATE OR REPLACE FUNCTION placex_updat 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; @@@ -1015,7 -977,6 +1021,7 @@@ BEGI -- 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 @@@ -1034,29 -995,6 +1040,29 @@@ 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 --combined lib-sql/functions/utils.sql index a8ebcc41,75e83e7f..f8b365c5 --- a/lib-sql/functions/utils.sql +++ b/lib-sql/functions/utils.sql @@@ -73,26 -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. @@@ -436,6 -416,20 +436,20 @@@ END $$ LANGUAGE plpgsql IMMUTABLE; + CREATE OR REPLACE FUNCTION simplify_large_polygons(geometry GEOMETRY) + RETURNS GEOMETRY + AS $$ + BEGIN + IF ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') + and ST_MemSize(geometry) > 3000000 + THEN + geometry := ST_SimplifyPreserveTopology(geometry, 0.0001); + END IF; + RETURN geometry; + END; + $$ + LANGUAGE plpgsql IMMUTABLE; + CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT) RETURNS BOOLEAN