# HG changeset patch # User jbe # Date 1477047097 -7200 # Node ID 2890e859c8a8caf3e9c4a79fd30b960088c86b62 # Parent 9cd46e55d5764c8ea3a76c17e790c94bf3f14b30 Bugfixes in GeoJSON_to_epoint and GeoJSON_to_ecluster functions diff -r 9cd46e55d576 -r 2890e859c8a8 latlon--0.7--0.8.sql --- a/latlon--0.7--0.8.sql Thu Oct 20 21:44:17 2016 +0200 +++ b/latlon--0.7--0.8.sql Fri Oct 21 12:51:37 2016 +0200 @@ -0,0 +1,124 @@ + +CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat') + RETURNS SETOF jsonb + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT "result" FROM + ( SELECT jsonb_array_length($1) - 1 ) AS "lastindex_row" ("lastindex") + CROSS JOIN LATERAL jsonb_array_elements( + CASE WHEN + coords_to_epoint( + ($1->0->>0)::float8, + ($1->0->>1)::float8, + $2 + ) = coords_to_epoint( + ($1->"lastindex"->>0)::float8, + ($1->"lastindex"->>1)::float8, + $2 + ) + THEN + $1 - "lastindex" + ELSE + $1 + END + ) AS "result_row" ("result") + $$; + +-- NOTE: drop function to throw error if indices depend on it +-- (they need to be rebuilt) +DROP FUNCTION GeoJSON_to_ecluster(jsonb, text); + +CREATE FUNCTION GeoJSON_to_ecluster(jsonb, text = 'epoint_lonlat') + RETURNS ecluster + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT CASE $1->>'type' + WHEN 'Point' THEN + coords_to_epoint( + ($1->'coordinates'->>0)::float8, + ($1->'coordinates'->>1)::float8, + $2 + )::ecluster + WHEN 'MultiPoint' THEN + ( SELECT ecluster_create_multipoint(array_agg( + coords_to_epoint( + ("coord"->>0)::float8, + ("coord"->>1)::float8, + $2 + ) + )) + FROM jsonb_array_elements($1->'coordinates') AS "coord" + ) + WHEN 'LineString' THEN + ( SELECT ecluster_create_path(array_agg( + coords_to_epoint( + ("coord"->>0)::float8, + ("coord"->>1)::float8, + $2 + ) + )) + FROM jsonb_array_elements($1->'coordinates') AS "coord" + ) + WHEN 'MultiLineString' THEN + ( SELECT ecluster_concat(array_agg( + ( SELECT ecluster_create_path(array_agg( + coords_to_epoint( + ("coord"->>0)::float8, + ("coord"->>1)::float8, + $2 + ) + )) + FROM jsonb_array_elements("coord_array") AS "coord" + ) + )) + FROM jsonb_array_elements($1->'coordinates') AS "coord_array" + ) + WHEN 'Polygon' THEN + ( SELECT ecluster_concat(array_agg( + ( SELECT ecluster_create_polygon(array_agg( + coords_to_epoint( + ("coord"->>0)::float8, + ("coord"->>1)::float8, + $2 + ) + )) + FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord" + ) + )) + FROM jsonb_array_elements($1->'coordinates') AS "coord_array" + ) + WHEN 'MultiPolygon' THEN + ( SELECT ecluster_concat(array_agg( + ( SELECT ecluster_concat(array_agg( + ( SELECT ecluster_create_polygon(array_agg( + coords_to_epoint( + ("coord"->>0)::float8, + ("coord"->>1)::float8, + $2 + ) + )) + FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord" + ) + )) + FROM jsonb_array_elements("coord_array_array") AS "coord_array" + ) + )) + FROM jsonb_array_elements($1->'coordinates') AS "coord_array_array" + ) + WHEN 'GeometryCollection' THEN + ( SELECT ecluster_concat(array_agg( + GeoJSON_to_ecluster("geometry", $2) + )) + FROM jsonb_array_elements($1->'geometries') AS "geometry" + ) + WHEN 'Feature' THEN + GeoJSON_to_ecluster($1->'geometry', $2) + WHEN 'FeatureCollection' THEN + ( SELECT ecluster_concat(array_agg( + GeoJSON_to_ecluster("feature", $2) + )) + FROM jsonb_array_elements($1->'features') AS "feature" + ) + ELSE + NULL + END + $$; + diff -r 9cd46e55d576 -r 2890e859c8a8 latlon--0.8.sql --- a/latlon--0.8.sql Thu Oct 20 21:44:17 2016 +0200 +++ b/latlon--0.8.sql Fri Oct 21 12:51:37 2016 +0200 @@ -1520,7 +1520,7 @@ -- other data storage formats -- -------------------------------- -CREATE FUNCTION coords_to_epoint(float8, float8, text = 'epoint_lonlat') +CREATE FUNCTION coords_to_epoint(float8, float8, text = 'epoint') RETURNS epoint LANGUAGE plpgsql IMMUTABLE STRICT AS $$ DECLARE @@ -1539,14 +1539,38 @@ END; $$; +CREATE FUNCTION GeoJSON_LinearRing_vertices(jsonb, text = 'epoint_lonlat') + RETURNS SETOF jsonb + LANGUAGE sql IMMUTABLE STRICT AS $$ + SELECT "result" FROM + ( SELECT jsonb_array_length($1) - 1 ) AS "lastindex_row" ("lastindex") + CROSS JOIN LATERAL jsonb_array_elements( + CASE WHEN + coords_to_epoint( + ($1->0->>0)::float8, + ($1->0->>1)::float8, + $2 + ) = coords_to_epoint( + ($1->"lastindex"->>0)::float8, + ($1->"lastindex"->>1)::float8, + $2 + ) + THEN + $1 - "lastindex" + ELSE + $1 + END + ) AS "result_row" ("result") + $$; + CREATE FUNCTION GeoJSON_to_epoint(jsonb, text = 'epoint_lonlat') RETURNS epoint LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT CASE WHEN $1->>'type' = 'Point' THEN coords_to_epoint( + ($1->'coordinates'->>0)::float8, ($1->'coordinates'->>1)::float8, - ($1->'coordinates'->>0)::float8, $2 ) WHEN $1->>'type' = 'Feature' THEN @@ -1562,15 +1586,15 @@ SELECT CASE $1->>'type' WHEN 'Point' THEN coords_to_epoint( + ($1->'coordinates'->>0)::float8, ($1->'coordinates'->>1)::float8, - ($1->'coordinates'->>0)::float8, $2 )::ecluster WHEN 'MultiPoint' THEN ( SELECT ecluster_create_multipoint(array_agg( coords_to_epoint( + ("coord"->>0)::float8, ("coord"->>1)::float8, - ("coord"->>0)::float8, $2 ) )) @@ -1579,8 +1603,8 @@ WHEN 'LineString' THEN ( SELECT ecluster_create_path(array_agg( coords_to_epoint( + ("coord"->>0)::float8, ("coord"->>1)::float8, - ("coord"->>0)::float8, $2 ) )) @@ -1590,8 +1614,8 @@ ( SELECT ecluster_concat(array_agg( ( SELECT ecluster_create_path(array_agg( coords_to_epoint( + ("coord"->>0)::float8, ("coord"->>1)::float8, - ("coord"->>0)::float8, $2 ) )) @@ -1604,12 +1628,12 @@ ( SELECT ecluster_concat(array_agg( ( SELECT ecluster_create_polygon(array_agg( coords_to_epoint( + ("coord"->>0)::float8, ("coord"->>1)::float8, - ("coord"->>0)::float8, $2 ) )) - FROM jsonb_array_elements("coord_array") AS "coord" + FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord" ) )) FROM jsonb_array_elements($1->'coordinates') AS "coord_array" @@ -1619,12 +1643,12 @@ ( SELECT ecluster_concat(array_agg( ( SELECT ecluster_create_polygon(array_agg( coords_to_epoint( + ("coord"->>0)::float8, ("coord"->>1)::float8, - ("coord"->>0)::float8, $2 ) )) - FROM jsonb_array_elements("coord_array") AS "coord" + FROM GeoJSON_LinearRing_vertices("coord_array", $2) AS "coord" ) )) FROM jsonb_array_elements("coord_array_array") AS "coord_array" @@ -1632,6 +1656,12 @@ )) FROM jsonb_array_elements($1->'coordinates') AS "coord_array_array" ) + WHEN 'GeometryCollection' THEN + ( SELECT ecluster_concat(array_agg( + GeoJSON_to_ecluster("geometry", $2) + )) + FROM jsonb_array_elements($1->'geometries') AS "geometry" + ) WHEN 'Feature' THEN GeoJSON_to_ecluster($1->'geometry', $2) WHEN 'FeatureCollection' THEN