# HG changeset patch
# User jbe
# Date 1606760757 -3600
# Node ID 4f11ccf36fb660c5a7aee82b9e362a45f10c0105
# Parent  1cdb3da501761d317bdb6eaf65828de1a042c921
Replaced huge SQL query in GeoJSON_to_ecluster function by several smaller SQL statements to enhance performance

diff -r 1cdb3da50176 -r 4f11ccf36fb6 latlon--0.14--0.15.sql
--- a/latlon--0.14--0.15.sql	Mon Nov 30 18:50:48 2020 +0100
+++ b/latlon--0.14--0.15.sql	Mon Nov 30 19:25:57 2020 +0100
@@ -884,119 +884,133 @@
 
 CREATE OR REPLACE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat')
   RETURNS ecluster
-  LANGUAGE sql IMMUTABLE STRICT AS $$
-    SELECT CASE $1 OPERATOR(pg_catalog.->>) 'type'
-    WHEN 'Point' THEN
-      @extschema@.coords_to_epoint(
-        ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
-        ::pg_catalog.float8,
-        ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
-        ::pg_catalog.float8,
-        $2
-      )::@extschema@.ecluster
-    WHEN 'MultiPoint' THEN
-      ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg(
-          @extschema@.coords_to_epoint(
-            ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-            ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-            $2
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord"
-      )
-    WHEN 'LineString' THEN
-      ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
-          @extschema@.coords_to_epoint(
-            ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-            ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-            $2
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord"
-      )
-    WHEN 'MultiLineString' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
-              @extschema@.coords_to_epoint(
-                ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-                ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-                $2
-              )
-            ))
-            FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord"
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord_array"
-      )
-    WHEN 'Polygon' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg(
-              @extschema@.coords_to_epoint(
-                ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-                ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-                $2
-              )
-            ))
-            FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
-            AS "coord"
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord_array"
-      )
-    WHEN 'MultiPolygon' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-              ( SELECT @extschema@.ecluster_create_polygon(
-                  pg_catalog.array_agg(
-                    @extschema@.coords_to_epoint(
-                      ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-                      ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-                      $2
+  LANGUAGE plpgsql IMMUTABLE STRICT AS $$
+    DECLARE
+      "tp" TEXT = $1 OPERATOR(pg_catalog.->>) 'type';
+    BEGIN
+      IF "tp" = 'Point' THEN RETURN
+        @extschema@.coords_to_epoint(
+          ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
+          ::pg_catalog.float8,
+          ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
+          ::pg_catalog.float8,
+          $2
+        )::@extschema@.ecluster;
+      END IF;
+      raise notice 'DEBUG2';
+      IF "tp" = 'MultiPoint' THEN RETURN
+        ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg(
+            @extschema@.coords_to_epoint(
+              ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+              ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+              $2
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord"
+        );
+      END IF;
+      IF "tp" = 'LineString' THEN RETURN
+        ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
+            @extschema@.coords_to_epoint(
+              ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+              ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+              $2
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord"
+        );
+      END IF;
+      IF "tp" = 'MultiLineString' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
+                @extschema@.coords_to_epoint(
+                  ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+                  ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+                  $2
+                )
+              ))
+              FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord"
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord_array"
+        );
+      END IF;
+      IF "tp" = 'Polygon' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg(
+                @extschema@.coords_to_epoint(
+                  ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+                  ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+                  $2
+                )
+              ))
+              FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
+              AS "coord"
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord_array"
+        );
+      END IF;
+      IF "tp" = 'MultiPolygon' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+                ( SELECT @extschema@.ecluster_create_polygon(
+                    pg_catalog.array_agg(
+                      @extschema@.coords_to_epoint(
+                        ("coord" OPERATOR(pg_catalog.->>) 0)
+                        ::pg_catalog.float8,
+                        ("coord" OPERATOR(pg_catalog.->>) 1)
+                        ::pg_catalog.float8,
+                        $2
+                      )
                     )
                   )
+                  FROM @extschema@.GeoJSON_LinearRing_vertices(
+                    "coord_array", $2
+                  ) AS "coord"
                 )
-                FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
-                AS "coord"
-              )
-            ))
-            FROM pg_catalog.jsonb_array_elements("coord_array_array")
-            AS "coord_array"
-          )
-        ))
-        FROM jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord_array_array"
-      )
-    WHEN 'GeometryCollection' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          @extschema@.GeoJSON_to_ecluster("geometry", $2)
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'geometries'
-        ) AS "geometry"
-      )
-    WHEN 'Feature' THEN
-      @extschema@.GeoJSON_to_ecluster(
-        $1 OPERATOR(pg_catalog.->) 'geometry', $2
-      )
-    WHEN 'FeatureCollection' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          @extschema@.GeoJSON_to_ecluster("feature", $2)
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'features'
-        ) AS "feature"
-      )
-    ELSE
-      NULL
-    END
+              ))
+              FROM pg_catalog.jsonb_array_elements("coord_array_array")
+              AS "coord_array"
+            )
+          ))
+          FROM jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord_array_array"
+        );
+      END IF;
+      IF "tp" = 'GeometryCollection' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            @extschema@.GeoJSON_to_ecluster("geometry", $2)
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'geometries'
+          ) AS "geometry"
+        );
+      END IF;
+      IF "tp" = 'Feature' THEN RETURN
+        @extschema@.GeoJSON_to_ecluster(
+          $1 OPERATOR(pg_catalog.->) 'geometry', $2
+        );
+      END IF;
+      IF "tp" = 'FeatureCollection' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            @extschema@.GeoJSON_to_ecluster("feature", $2)
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'features'
+          ) AS "feature"
+        );
+      END IF;
+      RETURN NULL;
+    END;
   $$;
 
diff -r 1cdb3da50176 -r 4f11ccf36fb6 latlon--0.15.sql
--- a/latlon--0.15.sql	Mon Nov 30 18:50:48 2020 +0100
+++ b/latlon--0.15.sql	Mon Nov 30 19:25:57 2020 +0100
@@ -1692,119 +1692,133 @@
 
 CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat')
   RETURNS ecluster
-  LANGUAGE sql IMMUTABLE STRICT AS $$
-    SELECT CASE $1 OPERATOR(pg_catalog.->>) 'type'
-    WHEN 'Point' THEN
-      @extschema@.coords_to_epoint(
-        ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
-        ::pg_catalog.float8,
-        ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
-        ::pg_catalog.float8,
-        $2
-      )::@extschema@.ecluster
-    WHEN 'MultiPoint' THEN
-      ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg(
-          @extschema@.coords_to_epoint(
-            ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-            ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-            $2
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord"
-      )
-    WHEN 'LineString' THEN
-      ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
-          @extschema@.coords_to_epoint(
-            ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-            ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-            $2
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord"
-      )
-    WHEN 'MultiLineString' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
-              @extschema@.coords_to_epoint(
-                ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-                ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-                $2
-              )
-            ))
-            FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord"
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord_array"
-      )
-    WHEN 'Polygon' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg(
-              @extschema@.coords_to_epoint(
-                ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-                ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-                $2
-              )
-            ))
-            FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
-            AS "coord"
-          )
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord_array"
-      )
-    WHEN 'MultiPolygon' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-              ( SELECT @extschema@.ecluster_create_polygon(
-                  pg_catalog.array_agg(
-                    @extschema@.coords_to_epoint(
-                      ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
-                      ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
-                      $2
+  LANGUAGE plpgsql IMMUTABLE STRICT AS $$
+    DECLARE
+      "tp" TEXT = $1 OPERATOR(pg_catalog.->>) 'type';
+    BEGIN
+      IF "tp" = 'Point' THEN RETURN
+        @extschema@.coords_to_epoint(
+          ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0)
+          ::pg_catalog.float8,
+          ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1)
+          ::pg_catalog.float8,
+          $2
+        )::@extschema@.ecluster;
+      END IF;
+      raise notice 'DEBUG2';
+      IF "tp" = 'MultiPoint' THEN RETURN
+        ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg(
+            @extschema@.coords_to_epoint(
+              ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+              ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+              $2
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord"
+        );
+      END IF;
+      IF "tp" = 'LineString' THEN RETURN
+        ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
+            @extschema@.coords_to_epoint(
+              ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+              ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+              $2
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord"
+        );
+      END IF;
+      IF "tp" = 'MultiLineString' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg(
+                @extschema@.coords_to_epoint(
+                  ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+                  ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+                  $2
+                )
+              ))
+              FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord"
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord_array"
+        );
+      END IF;
+      IF "tp" = 'Polygon' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg(
+                @extschema@.coords_to_epoint(
+                  ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8,
+                  ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8,
+                  $2
+                )
+              ))
+              FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
+              AS "coord"
+            )
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord_array"
+        );
+      END IF;
+      IF "tp" = 'MultiPolygon' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+                ( SELECT @extschema@.ecluster_create_polygon(
+                    pg_catalog.array_agg(
+                      @extschema@.coords_to_epoint(
+                        ("coord" OPERATOR(pg_catalog.->>) 0)
+                        ::pg_catalog.float8,
+                        ("coord" OPERATOR(pg_catalog.->>) 1)
+                        ::pg_catalog.float8,
+                        $2
+                      )
                     )
                   )
+                  FROM @extschema@.GeoJSON_LinearRing_vertices(
+                    "coord_array", $2
+                  ) AS "coord"
                 )
-                FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2)
-                AS "coord"
-              )
-            ))
-            FROM pg_catalog.jsonb_array_elements("coord_array_array")
-            AS "coord_array"
-          )
-        ))
-        FROM jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'coordinates'
-        ) AS "coord_array_array"
-      )
-    WHEN 'GeometryCollection' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          @extschema@.GeoJSON_to_ecluster("geometry", $2)
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'geometries'
-        ) AS "geometry"
-      )
-    WHEN 'Feature' THEN
-      @extschema@.GeoJSON_to_ecluster(
-        $1 OPERATOR(pg_catalog.->) 'geometry', $2
-      )
-    WHEN 'FeatureCollection' THEN
-      ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
-          @extschema@.GeoJSON_to_ecluster("feature", $2)
-        ))
-        FROM pg_catalog.jsonb_array_elements(
-          $1 OPERATOR(pg_catalog.->) 'features'
-        ) AS "feature"
-      )
-    ELSE
-      NULL
-    END
+              ))
+              FROM pg_catalog.jsonb_array_elements("coord_array_array")
+              AS "coord_array"
+            )
+          ))
+          FROM jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'coordinates'
+          ) AS "coord_array_array"
+        );
+      END IF;
+      IF "tp" = 'GeometryCollection' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            @extschema@.GeoJSON_to_ecluster("geometry", $2)
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'geometries'
+          ) AS "geometry"
+        );
+      END IF;
+      IF "tp" = 'Feature' THEN RETURN
+        @extschema@.GeoJSON_to_ecluster(
+          $1 OPERATOR(pg_catalog.->) 'geometry', $2
+        );
+      END IF;
+      IF "tp" = 'FeatureCollection' THEN RETURN
+        ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg(
+            @extschema@.GeoJSON_to_ecluster("feature", $2)
+          ))
+          FROM pg_catalog.jsonb_array_elements(
+            $1 OPERATOR(pg_catalog.->) 'features'
+          ) AS "feature"
+        );
+      END IF;
+      RETURN NULL;
+    END;
   $$;