pgLatLon
changeset 76:4f11ccf36fb6 v0.15
Replaced huge SQL query in GeoJSON_to_ecluster function by several smaller SQL statements to enhance performance
author | jbe |
---|---|
date | Mon Nov 30 19:25:57 2020 +0100 (2020-11-30) |
parents | 1cdb3da50176 |
children | a707dc3e896c |
files | latlon--0.14--0.15.sql latlon--0.15.sql |
line diff
1.1 --- a/latlon--0.14--0.15.sql Mon Nov 30 18:50:48 2020 +0100 1.2 +++ b/latlon--0.14--0.15.sql Mon Nov 30 19:25:57 2020 +0100 1.3 @@ -884,119 +884,133 @@ 1.4 1.5 CREATE OR REPLACE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat') 1.6 RETURNS ecluster 1.7 - LANGUAGE sql IMMUTABLE STRICT AS $$ 1.8 - SELECT CASE $1 OPERATOR(pg_catalog.->>) 'type' 1.9 - WHEN 'Point' THEN 1.10 - @extschema@.coords_to_epoint( 1.11 - ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) 1.12 - ::pg_catalog.float8, 1.13 - ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) 1.14 - ::pg_catalog.float8, 1.15 - $2 1.16 - )::@extschema@.ecluster 1.17 - WHEN 'MultiPoint' THEN 1.18 - ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg( 1.19 - @extschema@.coords_to_epoint( 1.20 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.21 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.22 - $2 1.23 - ) 1.24 - )) 1.25 - FROM pg_catalog.jsonb_array_elements( 1.26 - $1 OPERATOR(pg_catalog.->) 'coordinates' 1.27 - ) AS "coord" 1.28 - ) 1.29 - WHEN 'LineString' THEN 1.30 - ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 1.31 - @extschema@.coords_to_epoint( 1.32 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.33 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.34 - $2 1.35 - ) 1.36 - )) 1.37 - FROM pg_catalog.jsonb_array_elements( 1.38 - $1 OPERATOR(pg_catalog.->) 'coordinates' 1.39 - ) AS "coord" 1.40 - ) 1.41 - WHEN 'MultiLineString' THEN 1.42 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.43 - ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 1.44 - @extschema@.coords_to_epoint( 1.45 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.46 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.47 - $2 1.48 - ) 1.49 - )) 1.50 - FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord" 1.51 - ) 1.52 - )) 1.53 - FROM pg_catalog.jsonb_array_elements( 1.54 - $1 OPERATOR(pg_catalog.->) 'coordinates' 1.55 - ) AS "coord_array" 1.56 - ) 1.57 - WHEN 'Polygon' THEN 1.58 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.59 - ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg( 1.60 - @extschema@.coords_to_epoint( 1.61 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.62 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.63 - $2 1.64 - ) 1.65 - )) 1.66 - FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) 1.67 - AS "coord" 1.68 - ) 1.69 - )) 1.70 - FROM pg_catalog.jsonb_array_elements( 1.71 - $1 OPERATOR(pg_catalog.->) 'coordinates' 1.72 - ) AS "coord_array" 1.73 - ) 1.74 - WHEN 'MultiPolygon' THEN 1.75 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.76 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.77 - ( SELECT @extschema@.ecluster_create_polygon( 1.78 - pg_catalog.array_agg( 1.79 - @extschema@.coords_to_epoint( 1.80 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.81 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.82 - $2 1.83 + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 1.84 + DECLARE 1.85 + "tp" TEXT = $1 OPERATOR(pg_catalog.->>) 'type'; 1.86 + BEGIN 1.87 + IF "tp" = 'Point' THEN RETURN 1.88 + @extschema@.coords_to_epoint( 1.89 + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) 1.90 + ::pg_catalog.float8, 1.91 + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) 1.92 + ::pg_catalog.float8, 1.93 + $2 1.94 + )::@extschema@.ecluster; 1.95 + END IF; 1.96 + raise notice 'DEBUG2'; 1.97 + IF "tp" = 'MultiPoint' THEN RETURN 1.98 + ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg( 1.99 + @extschema@.coords_to_epoint( 1.100 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.101 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.102 + $2 1.103 + ) 1.104 + )) 1.105 + FROM pg_catalog.jsonb_array_elements( 1.106 + $1 OPERATOR(pg_catalog.->) 'coordinates' 1.107 + ) AS "coord" 1.108 + ); 1.109 + END IF; 1.110 + IF "tp" = 'LineString' THEN RETURN 1.111 + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 1.112 + @extschema@.coords_to_epoint( 1.113 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.114 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.115 + $2 1.116 + ) 1.117 + )) 1.118 + FROM pg_catalog.jsonb_array_elements( 1.119 + $1 OPERATOR(pg_catalog.->) 'coordinates' 1.120 + ) AS "coord" 1.121 + ); 1.122 + END IF; 1.123 + IF "tp" = 'MultiLineString' THEN RETURN 1.124 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.125 + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 1.126 + @extschema@.coords_to_epoint( 1.127 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.128 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.129 + $2 1.130 + ) 1.131 + )) 1.132 + FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord" 1.133 + ) 1.134 + )) 1.135 + FROM pg_catalog.jsonb_array_elements( 1.136 + $1 OPERATOR(pg_catalog.->) 'coordinates' 1.137 + ) AS "coord_array" 1.138 + ); 1.139 + END IF; 1.140 + IF "tp" = 'Polygon' THEN RETURN 1.141 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.142 + ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg( 1.143 + @extschema@.coords_to_epoint( 1.144 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 1.145 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 1.146 + $2 1.147 + ) 1.148 + )) 1.149 + FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) 1.150 + AS "coord" 1.151 + ) 1.152 + )) 1.153 + FROM pg_catalog.jsonb_array_elements( 1.154 + $1 OPERATOR(pg_catalog.->) 'coordinates' 1.155 + ) AS "coord_array" 1.156 + ); 1.157 + END IF; 1.158 + IF "tp" = 'MultiPolygon' THEN RETURN 1.159 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.160 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.161 + ( SELECT @extschema@.ecluster_create_polygon( 1.162 + pg_catalog.array_agg( 1.163 + @extschema@.coords_to_epoint( 1.164 + ("coord" OPERATOR(pg_catalog.->>) 0) 1.165 + ::pg_catalog.float8, 1.166 + ("coord" OPERATOR(pg_catalog.->>) 1) 1.167 + ::pg_catalog.float8, 1.168 + $2 1.169 + ) 1.170 ) 1.171 ) 1.172 + FROM @extschema@.GeoJSON_LinearRing_vertices( 1.173 + "coord_array", $2 1.174 + ) AS "coord" 1.175 ) 1.176 - FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) 1.177 - AS "coord" 1.178 - ) 1.179 - )) 1.180 - FROM pg_catalog.jsonb_array_elements("coord_array_array") 1.181 - AS "coord_array" 1.182 - ) 1.183 - )) 1.184 - FROM jsonb_array_elements( 1.185 - $1 OPERATOR(pg_catalog.->) 'coordinates' 1.186 - ) AS "coord_array_array" 1.187 - ) 1.188 - WHEN 'GeometryCollection' THEN 1.189 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.190 - @extschema@.GeoJSON_to_ecluster("geometry", $2) 1.191 - )) 1.192 - FROM pg_catalog.jsonb_array_elements( 1.193 - $1 OPERATOR(pg_catalog.->) 'geometries' 1.194 - ) AS "geometry" 1.195 - ) 1.196 - WHEN 'Feature' THEN 1.197 - @extschema@.GeoJSON_to_ecluster( 1.198 - $1 OPERATOR(pg_catalog.->) 'geometry', $2 1.199 - ) 1.200 - WHEN 'FeatureCollection' THEN 1.201 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.202 - @extschema@.GeoJSON_to_ecluster("feature", $2) 1.203 - )) 1.204 - FROM pg_catalog.jsonb_array_elements( 1.205 - $1 OPERATOR(pg_catalog.->) 'features' 1.206 - ) AS "feature" 1.207 - ) 1.208 - ELSE 1.209 - NULL 1.210 - END 1.211 + )) 1.212 + FROM pg_catalog.jsonb_array_elements("coord_array_array") 1.213 + AS "coord_array" 1.214 + ) 1.215 + )) 1.216 + FROM jsonb_array_elements( 1.217 + $1 OPERATOR(pg_catalog.->) 'coordinates' 1.218 + ) AS "coord_array_array" 1.219 + ); 1.220 + END IF; 1.221 + IF "tp" = 'GeometryCollection' THEN RETURN 1.222 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.223 + @extschema@.GeoJSON_to_ecluster("geometry", $2) 1.224 + )) 1.225 + FROM pg_catalog.jsonb_array_elements( 1.226 + $1 OPERATOR(pg_catalog.->) 'geometries' 1.227 + ) AS "geometry" 1.228 + ); 1.229 + END IF; 1.230 + IF "tp" = 'Feature' THEN RETURN 1.231 + @extschema@.GeoJSON_to_ecluster( 1.232 + $1 OPERATOR(pg_catalog.->) 'geometry', $2 1.233 + ); 1.234 + END IF; 1.235 + IF "tp" = 'FeatureCollection' THEN RETURN 1.236 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 1.237 + @extschema@.GeoJSON_to_ecluster("feature", $2) 1.238 + )) 1.239 + FROM pg_catalog.jsonb_array_elements( 1.240 + $1 OPERATOR(pg_catalog.->) 'features' 1.241 + ) AS "feature" 1.242 + ); 1.243 + END IF; 1.244 + RETURN NULL; 1.245 + END; 1.246 $$; 1.247
2.1 --- a/latlon--0.15.sql Mon Nov 30 18:50:48 2020 +0100 2.2 +++ b/latlon--0.15.sql Mon Nov 30 19:25:57 2020 +0100 2.3 @@ -1692,119 +1692,133 @@ 2.4 2.5 CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat') 2.6 RETURNS ecluster 2.7 - LANGUAGE sql IMMUTABLE STRICT AS $$ 2.8 - SELECT CASE $1 OPERATOR(pg_catalog.->>) 'type' 2.9 - WHEN 'Point' THEN 2.10 - @extschema@.coords_to_epoint( 2.11 - ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) 2.12 - ::pg_catalog.float8, 2.13 - ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) 2.14 - ::pg_catalog.float8, 2.15 - $2 2.16 - )::@extschema@.ecluster 2.17 - WHEN 'MultiPoint' THEN 2.18 - ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg( 2.19 - @extschema@.coords_to_epoint( 2.20 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.21 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.22 - $2 2.23 - ) 2.24 - )) 2.25 - FROM pg_catalog.jsonb_array_elements( 2.26 - $1 OPERATOR(pg_catalog.->) 'coordinates' 2.27 - ) AS "coord" 2.28 - ) 2.29 - WHEN 'LineString' THEN 2.30 - ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 2.31 - @extschema@.coords_to_epoint( 2.32 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.33 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.34 - $2 2.35 - ) 2.36 - )) 2.37 - FROM pg_catalog.jsonb_array_elements( 2.38 - $1 OPERATOR(pg_catalog.->) 'coordinates' 2.39 - ) AS "coord" 2.40 - ) 2.41 - WHEN 'MultiLineString' THEN 2.42 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.43 - ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 2.44 - @extschema@.coords_to_epoint( 2.45 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.46 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.47 - $2 2.48 - ) 2.49 - )) 2.50 - FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord" 2.51 - ) 2.52 - )) 2.53 - FROM pg_catalog.jsonb_array_elements( 2.54 - $1 OPERATOR(pg_catalog.->) 'coordinates' 2.55 - ) AS "coord_array" 2.56 - ) 2.57 - WHEN 'Polygon' THEN 2.58 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.59 - ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg( 2.60 - @extschema@.coords_to_epoint( 2.61 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.62 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.63 - $2 2.64 - ) 2.65 - )) 2.66 - FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) 2.67 - AS "coord" 2.68 - ) 2.69 - )) 2.70 - FROM pg_catalog.jsonb_array_elements( 2.71 - $1 OPERATOR(pg_catalog.->) 'coordinates' 2.72 - ) AS "coord_array" 2.73 - ) 2.74 - WHEN 'MultiPolygon' THEN 2.75 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.76 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.77 - ( SELECT @extschema@.ecluster_create_polygon( 2.78 - pg_catalog.array_agg( 2.79 - @extschema@.coords_to_epoint( 2.80 - ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.81 - ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.82 - $2 2.83 + LANGUAGE plpgsql IMMUTABLE STRICT AS $$ 2.84 + DECLARE 2.85 + "tp" TEXT = $1 OPERATOR(pg_catalog.->>) 'type'; 2.86 + BEGIN 2.87 + IF "tp" = 'Point' THEN RETURN 2.88 + @extschema@.coords_to_epoint( 2.89 + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 0) 2.90 + ::pg_catalog.float8, 2.91 + ($1 OPERATOR(pg_catalog.->) 'coordinates' OPERATOR(pg_catalog.->>) 1) 2.92 + ::pg_catalog.float8, 2.93 + $2 2.94 + )::@extschema@.ecluster; 2.95 + END IF; 2.96 + raise notice 'DEBUG2'; 2.97 + IF "tp" = 'MultiPoint' THEN RETURN 2.98 + ( SELECT @extschema@.ecluster_create_multipoint(pg_catalog.array_agg( 2.99 + @extschema@.coords_to_epoint( 2.100 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.101 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.102 + $2 2.103 + ) 2.104 + )) 2.105 + FROM pg_catalog.jsonb_array_elements( 2.106 + $1 OPERATOR(pg_catalog.->) 'coordinates' 2.107 + ) AS "coord" 2.108 + ); 2.109 + END IF; 2.110 + IF "tp" = 'LineString' THEN RETURN 2.111 + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 2.112 + @extschema@.coords_to_epoint( 2.113 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.114 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.115 + $2 2.116 + ) 2.117 + )) 2.118 + FROM pg_catalog.jsonb_array_elements( 2.119 + $1 OPERATOR(pg_catalog.->) 'coordinates' 2.120 + ) AS "coord" 2.121 + ); 2.122 + END IF; 2.123 + IF "tp" = 'MultiLineString' THEN RETURN 2.124 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.125 + ( SELECT @extschema@.ecluster_create_path(pg_catalog.array_agg( 2.126 + @extschema@.coords_to_epoint( 2.127 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.128 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.129 + $2 2.130 + ) 2.131 + )) 2.132 + FROM pg_catalog.jsonb_array_elements("coord_array") AS "coord" 2.133 + ) 2.134 + )) 2.135 + FROM pg_catalog.jsonb_array_elements( 2.136 + $1 OPERATOR(pg_catalog.->) 'coordinates' 2.137 + ) AS "coord_array" 2.138 + ); 2.139 + END IF; 2.140 + IF "tp" = 'Polygon' THEN RETURN 2.141 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.142 + ( SELECT @extschema@.ecluster_create_polygon(pg_catalog.array_agg( 2.143 + @extschema@.coords_to_epoint( 2.144 + ("coord" OPERATOR(pg_catalog.->>) 0)::pg_catalog.float8, 2.145 + ("coord" OPERATOR(pg_catalog.->>) 1)::pg_catalog.float8, 2.146 + $2 2.147 + ) 2.148 + )) 2.149 + FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) 2.150 + AS "coord" 2.151 + ) 2.152 + )) 2.153 + FROM pg_catalog.jsonb_array_elements( 2.154 + $1 OPERATOR(pg_catalog.->) 'coordinates' 2.155 + ) AS "coord_array" 2.156 + ); 2.157 + END IF; 2.158 + IF "tp" = 'MultiPolygon' THEN RETURN 2.159 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.160 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.161 + ( SELECT @extschema@.ecluster_create_polygon( 2.162 + pg_catalog.array_agg( 2.163 + @extschema@.coords_to_epoint( 2.164 + ("coord" OPERATOR(pg_catalog.->>) 0) 2.165 + ::pg_catalog.float8, 2.166 + ("coord" OPERATOR(pg_catalog.->>) 1) 2.167 + ::pg_catalog.float8, 2.168 + $2 2.169 + ) 2.170 ) 2.171 ) 2.172 + FROM @extschema@.GeoJSON_LinearRing_vertices( 2.173 + "coord_array", $2 2.174 + ) AS "coord" 2.175 ) 2.176 - FROM @extschema@.GeoJSON_LinearRing_vertices("coord_array", $2) 2.177 - AS "coord" 2.178 - ) 2.179 - )) 2.180 - FROM pg_catalog.jsonb_array_elements("coord_array_array") 2.181 - AS "coord_array" 2.182 - ) 2.183 - )) 2.184 - FROM jsonb_array_elements( 2.185 - $1 OPERATOR(pg_catalog.->) 'coordinates' 2.186 - ) AS "coord_array_array" 2.187 - ) 2.188 - WHEN 'GeometryCollection' THEN 2.189 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.190 - @extschema@.GeoJSON_to_ecluster("geometry", $2) 2.191 - )) 2.192 - FROM pg_catalog.jsonb_array_elements( 2.193 - $1 OPERATOR(pg_catalog.->) 'geometries' 2.194 - ) AS "geometry" 2.195 - ) 2.196 - WHEN 'Feature' THEN 2.197 - @extschema@.GeoJSON_to_ecluster( 2.198 - $1 OPERATOR(pg_catalog.->) 'geometry', $2 2.199 - ) 2.200 - WHEN 'FeatureCollection' THEN 2.201 - ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.202 - @extschema@.GeoJSON_to_ecluster("feature", $2) 2.203 - )) 2.204 - FROM pg_catalog.jsonb_array_elements( 2.205 - $1 OPERATOR(pg_catalog.->) 'features' 2.206 - ) AS "feature" 2.207 - ) 2.208 - ELSE 2.209 - NULL 2.210 - END 2.211 + )) 2.212 + FROM pg_catalog.jsonb_array_elements("coord_array_array") 2.213 + AS "coord_array" 2.214 + ) 2.215 + )) 2.216 + FROM jsonb_array_elements( 2.217 + $1 OPERATOR(pg_catalog.->) 'coordinates' 2.218 + ) AS "coord_array_array" 2.219 + ); 2.220 + END IF; 2.221 + IF "tp" = 'GeometryCollection' THEN RETURN 2.222 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.223 + @extschema@.GeoJSON_to_ecluster("geometry", $2) 2.224 + )) 2.225 + FROM pg_catalog.jsonb_array_elements( 2.226 + $1 OPERATOR(pg_catalog.->) 'geometries' 2.227 + ) AS "geometry" 2.228 + ); 2.229 + END IF; 2.230 + IF "tp" = 'Feature' THEN RETURN 2.231 + @extschema@.GeoJSON_to_ecluster( 2.232 + $1 OPERATOR(pg_catalog.->) 'geometry', $2 2.233 + ); 2.234 + END IF; 2.235 + IF "tp" = 'FeatureCollection' THEN RETURN 2.236 + ( SELECT @extschema@.ecluster_concat(pg_catalog.array_agg( 2.237 + @extschema@.GeoJSON_to_ecluster("feature", $2) 2.238 + )) 2.239 + FROM pg_catalog.jsonb_array_elements( 2.240 + $1 OPERATOR(pg_catalog.->) 'features' 2.241 + ) AS "feature" 2.242 + ); 2.243 + END IF; 2.244 + RETURN NULL; 2.245 + END; 2.246 $$; 2.247