simplify very large polygons non used in addresses

Polygons with rank_address = 0 are only used in search and (rarely)
for reverse lookup. Geometries do not need to be precise for that
because topology does not matter. OSM has some very large polygons
of natural features with sizes of more than 10MB. Simplify these
polygons to keep the database and indexes smaller.

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index f3b6ab2b4e7f5eff2482eb45792a2f032f62b363..5b4a844138138fe2cbd72115b92d7a84b43a6199 100644 (file)
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -296,7 +296,9 @@ BEGIN
           extratags = NEW.extratags,
           admin_level = NEW.admin_level,
           indexed_status = 2,
           extratags = NEW.extratags,
           admin_level = NEW.admin_level,
           indexed_status = 2,

-          geometry = NEW.geometry
+          geometry = CASE WHEN existingplacex.rank_address = 0
+                          THEN simplify_large_polygons(NEW.geometry)
+                          ELSE NEW.geometry END

       WHERE place_id = existingplacex.place_id;
 
     -- Invalidate linked places: they potentially get a new name and addresses.
       WHERE place_id = existingplacex.place_id;
 
     -- Invalidate linked places: they potentially get a new name and addresses.

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 530bf541a93182ee7607f56f69b4289c1087d256..99eebe1267acd86326db972deab6802622caadaa 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -678,6 +678,12 @@ BEGIN
       NEW.country_code := NULL;
     END IF;
 
       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 %}
   END IF;
 
   {% if debug %}RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;{% endif %}

diff --git a/lib-sql/functions/utils.sql b/lib-sql/functions/utils.sql
index ff2f037d01dabdb86b40212fff372738727bfb0e..75e83e7f8da6e931fb269736f9a149ce78df13c3 100644 (file)
--- a/lib-sql/functions/utils.sql
+++ b/lib-sql/functions/utils.sql
@@ -416,6 +416,20 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 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
 
 CREATE OR REPLACE FUNCTION place_force_delete(placeid BIGINT)
   RETURNS BOOLEAN