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