Merge pull request #3463 from lonvia/sqlalchemy14-with-psycopg

[nominatim.git] / lib-sql / functions / utils.sql

diff --git a/lib-sql/functions/utils.sql b/lib-sql/functions/utils.sql
index ff2f037d01dabdb86b40212fff372738727bfb0e..f8b365c582cce8cae569564bc9655335b8b69e1d 100644 (file)
--- a/lib-sql/functions/utils.sql
+++ b/lib-sql/functions/utils.sql
@@ -73,6 +73,26 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 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.
 -- Copy 'name' to or from the default language.
 --
 -- \param country_code     Country code of the object being named.


@@ -416,6 +436,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