| rev |
line source |
|
jbe@39
|
1
|
|
jbe@39
|
2 CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat')
|
|
jbe@39
|
3 RETURNS SETOF jsonb
|
|
jbe@39
|
4 LANGUAGE sql IMMUTABLE STRICT AS $$
|
|
jbe@39
|
5 SELECT "result" FROM
|
|
jbe@39
|
6 ( SELECT jsonb_array_length($1) - 1 ) AS "lastindex_row" ("lastindex")
|
|
jbe@39
|
7 CROSS JOIN LATERAL jsonb_array_elements(
|
|
jbe@39
|
8 CASE WHEN
|
|
jbe@39
|
9 coords_to_epoint(
|
|
jbe@39
|
10 ($1->0->>0)::float8,
|
|
jbe@39
|
11 ($1->0->>1)::float8,
|
|
jbe@39
|
12 $2
|
|
jbe@39
|
13 ) = coords_to_epoint(
|
|
jbe@39
|
14 ($1->"lastindex"->>0)::float8,
|
|
jbe@39
|
15 ($1->"lastindex"->>1)::float8,
|
|
jbe@39
|
16 $2
|
|
jbe@39
|
17 )
|
|
jbe@39
|
18 THEN
|
|
jbe@39
|
19 $1 - "lastindex"
|
|
jbe@39
|
20 ELSE
|
|
jbe@39
|
21 $1
|
|
jbe@39
|
22 END
|
|
jbe@39
|
23 ) AS "result_row" ("result")
|
|
jbe@39
|
24 $$;
|
|
jbe@39
|
25
|
|
jbe@39
|
26 -- NOTE: drop function to throw error if indices depend on it
|
|
jbe@39
|
27 -- (they need to be rebuilt)
|
|
jbe@39
|
28 DROP FUNCTION GeoJSON_to_ecluster(jsonb, text);
|
|
jbe@39
|
29
|
|
jbe@39
|
30 CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat')
|
|
jbe@39
|
31 RETURNS ecluster
|
|
jbe@39
|
32 LANGUAGE sql IMMUTABLE STRICT AS $$
|
|
jbe@39
|
33 SELECT CASE $1->>'type'
|
|
jbe@39
|
34 WHEN 'Point' THEN
|
|
jbe@39
|
35 coords_to_epoint(
|
|
jbe@39
|
36 ($1->'coordinates'->>0)::float8,
|
|
jbe@39
|
37 ($1->'coordinates'->>1)::float8,
|
|
jbe@39
|
38 $2
|
|
jbe@39
|
39 )::ecluster
|
|
jbe@39
|
40 WHEN 'MultiPoint' THEN
|
|
jbe@39
|
41 ( SELECT ecluster_create_multipoint(array_agg(
|
|
jbe@39
|
42 coords_to_epoint(
|
|
jbe@39
|
43 ("coord"->>0)::float8,
|
|
jbe@39
|
44 ("coord"->>1)::float8,
|
|
jbe@39
|
45 $2
|
|
jbe@39
|
46 )
|
|
jbe@39
|
47 ))
|
|
jbe@39
|
48 FROM jsonb_array_elements($1->'coordinates') AS "coord"
|
|
jbe@39
|
49 )
|
|
jbe@39
|
50 WHEN 'LineString' THEN
|
|
jbe@39
|
51 ( SELECT ecluster_create_path(array_agg(
|
|
jbe@39
|
52 coords_to_epoint(
|
|
jbe@39
|
53 ("coord"->>0)::float8,
|
|
jbe@39
|
54 ("coord"->>1)::float8,
|
|
jbe@39
|
55 $2
|
|
jbe@39
|
56 )
|
|
jbe@39
|
57 ))
|
|
jbe@39
|
58 FROM jsonb_array_elements($1->'coordinates') AS "coord"
|
|
jbe@39
|
59 )
|
|
jbe@39
|
60 WHEN 'MultiLineString' THEN
|
|
jbe@39
|
61 ( SELECT ecluster_concat(array_agg(
|
|
jbe@39
|
62 ( SELECT ecluster_create_path(array_agg(
|
|
jbe@39
|
63 coords_to_epoint(
|
|
jbe@39
|
64 ("coord"->>0)::float8,
|
|
jbe@39
|
65 ("coord"->>1)::float8,
|
|
jbe@39
|
66 $2
|
|
jbe@39
|
67 )
|
|
jbe@39
|
68 ))
|
|
jbe@39
|
69 FROM jsonb_array_elements("coord_array") AS "coord"
|
|
jbe@39
|
70 )
|
|
jbe@39
|
71 ))
|
|
jbe@39
|
72 FROM jsonb_array_elements($1->'coordinates') AS "coord_array"
|
|
jbe@39
|
73 )
|
|
jbe@39
|
74 WHEN 'Polygon' THEN
|
|
jbe@39
|
75 ( SELECT ecluster_concat(array_agg(
|
|
jbe@39
|
76 ( SELECT ecluster_create_polygon(array_agg(
|
|
jbe@39
|
77 coords_to_epoint(
|
|
jbe@39
|
78 ("coord"->>0)::float8,
|
|
jbe@39
|
79 ("coord"->>1)::float8,
|
|
jbe@39
|
80 $2
|
|
jbe@39
|
81 )
|
|
jbe@39
|
82 ))
|
|
jbe@39
|
83 FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
|
|
jbe@39
|
84 )
|
|
jbe@39
|
85 ))
|
|
jbe@39
|
86 FROM jsonb_array_elements($1->'coordinates') AS "coord_array"
|
|
jbe@39
|
87 )
|
|
jbe@39
|
88 WHEN 'MultiPolygon' THEN
|
|
jbe@39
|
89 ( SELECT ecluster_concat(array_agg(
|
|
jbe@39
|
90 ( SELECT ecluster_concat(array_agg(
|
|
jbe@39
|
91 ( SELECT ecluster_create_polygon(array_agg(
|
|
jbe@39
|
92 coords_to_epoint(
|
|
jbe@39
|
93 ("coord"->>0)::float8,
|
|
jbe@39
|
94 ("coord"->>1)::float8,
|
|
jbe@39
|
95 $2
|
|
jbe@39
|
96 )
|
|
jbe@39
|
97 ))
|
|
jbe@39
|
98 FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord"
|
|
jbe@39
|
99 )
|
|
jbe@39
|
100 ))
|
|
jbe@39
|
101 FROM jsonb_array_elements("coord_array_array") AS "coord_array"
|
|
jbe@39
|
102 )
|
|
jbe@39
|
103 ))
|
|
jbe@39
|
104 FROM jsonb_array_elements($1->'coordinates') AS "coord_array_array"
|
|
jbe@39
|
105 )
|
|
jbe@39
|
106 WHEN 'GeometryCollection' THEN
|
|
jbe@39
|
107 ( SELECT ecluster_concat(array_agg(
|
|
jbe@39
|
108 GeoJSON_to_ecluster("geometry", $2)
|
|
jbe@39
|
109 ))
|
|
jbe@39
|
110 FROM jsonb_array_elements($1->'geometries') AS "geometry"
|
|
jbe@39
|
111 )
|
|
jbe@39
|
112 WHEN 'Feature' THEN
|
|
jbe@39
|
113 GeoJSON_to_ecluster($1->'geometry', $2)
|
|
jbe@39
|
114 WHEN 'FeatureCollection' THEN
|
|
jbe@39
|
115 ( SELECT ecluster_concat(array_agg(
|
|
jbe@39
|
116 GeoJSON_to_ecluster("feature", $2)
|
|
jbe@39
|
117 ))
|
|
jbe@39
|
118 FROM jsonb_array_elements($1->'features') AS "feature"
|
|
jbe@39
|
119 )
|
|
jbe@39
|
120 ELSE
|
|
jbe@39
|
121 NULL
|
|
jbe@39
|
122 END
|
|
jbe@39
|
123 $$;
|
|
jbe@39
|
124
|