From 56201feb28d8393f26d683e65a42b4daaea8f895 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Wed, 14 Feb 2024 11:26:33 +0100
Subject: [PATCH] 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.
---
 lib-sql/functions/place_triggers.sql  |  4 +++-
 lib-sql/functions/placex_triggers.sql |  6 ++++++
 lib-sql/functions/utils.sql           | 14 ++++++++++++++
 3 files changed, 23 insertions(+), 1 deletion(-)

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index f3b6ab2b..5b4a8441 100644
--- 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,
-          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.
diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 530bf541..99eebe12 100644
--- 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;
 
+    -- 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 %}
diff --git a/lib-sql/functions/utils.sql b/lib-sql/functions/utils.sql
index ff2f037d..75e83e7f 100644
--- a/lib-sql/functions/utils.sql
+++ b/lib-sql/functions/utils.sql
@@ -416,6 +416,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
-- 
2.39.5