Merge pull request #3345 from lonvia/simplify-large-geometries

Simplify very large polygons that are not used in addresses

diff --combined lib-sql/functions/placex_triggers.sql
index 5d32f496d501e3c6dd98e4276b1c6fb502fa628b,99eebe1267acd86326db972deab6802622caadaa..386140f45c00d0f6fae651ccd970c19496ae78e7
--- 1/lib-sql/functions/placex_triggers.sql
--- 2/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 a8ebcc417d357a9154673474d69f64ce55a6be97,75e83e7f8da6e931fb269736f9a149ce78df13c3..f8b365c582cce8cae569564bc9655335b8b69e1d
--- 1/lib-sql/functions/utils.sql
--- 2/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