Skip to content

Instantly share code, notes, and snippets.

@tomtor
Last active Jun 17, 2016
Embed
What would you like to do?
----------------------------------------------------------------------------
-- CRS conversion functions for GeoJSON coordinates and lat/long pairs
-- in PostgreSQL/PostGIS:
--
-- from (Google) Web Mercator (OpenLayers:900913, EPSG:3785 and EPSG:3857 (prefered))
-- and WGS-84 Lat (y) Long (x) (EPSG:4326)
--
-- to EPSG:28992 (RD new)
--
-- Needs the PostGIS extension (surprise ;-)
----------------------------------------------------------------------------
-- Convert a Longitude (x= 5.0) and Latitude(y= 52.0)
select st_astext(st_transform(ST_SetSRID(ST_MakePoint(5.0, 52.0),4326),28992));
-- "POINT(128410.08537081 445806.508833154)"
-- Just get the Long (x) or Lat(y)
select st_x(st_transform(ST_SetSRID(ST_MakePoint(5.0, 52.0),4326),28992));
select st_y(st_transform(ST_SetSRID(ST_MakePoint(5.0, 52.0),4326),28992));
-- 128410.08537081
-- 445806.508833154
----------------------------------------------------------------------
-- transform a GeoJSON coordinates array from Web Mercator to RD-New:
----------------------------------------------------------------------
CREATE OR REPLACE FUNCTION trans_coord(coord json) RETURNS json AS $$
BEGIN
if char_length(coord->0->>0) > 0 then
-- LineString or Polygon (Array of Linestrings)
return array_to_json(array_agg(trans_coord(t))) from json_array_elements(coord) t;
else
-- Point
RETURN json_build_array(
st_x(st_transform(ST_SetSRID(ST_MakePoint((coord->>0)::numeric, (coord->>1)::numeric),3857),28992)),
st_y(st_transform(ST_SetSRID(ST_MakePoint((coord->>0)::numeric, (coord->>1)::numeric),3857),28992))
);
end if;
END;
$$ LANGUAGE plpgsql;
select trans_coord('[550000.0, 6800000.0]'::json),
trans_coord('[[550000.0, 6800000.0], [550000.1, 6800000.1]]'::json),
trans_coord('[[[550000.0, 6800000.0], [550000.1, 6800000.1]],[[550000.01, 6800000.01], [550000.11, 6800000.11]]]'::json);
-- "[124339.7469048, 445752.662921963]";
-- "[[124339.7469048, 445752.662921963],[124339.808972159, 445752.724074961]]";
-- "[[[124339.7469048, 445752.662921963],[124339.808972159, 445752.724074961]],[[124339.753111536, 445752.669037264],[124339.815178895, 445752.730190262]]]"
--------------------------------------------------------------------------
-- We need two JSON set/update functions from:
-- https://gist.github.com/tomtor/1db6d88da319343dbce21ea2accf5f2f
--------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION "json_object_set_key"(
"json" json,
"key_to_set" TEXT,
"value_to_set" anyelement
)
RETURNS json
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
FROM (SELECT *
FROM json_each("json")
WHERE "key" <> "key_to_set"
UNION ALL
SELECT "key_to_set", to_json("value_to_set")) AS "fields"
$function$;
CREATE OR REPLACE FUNCTION "json_object_set_path"(
"json" json,
"key_path" TEXT[],
"value_to_set" anyelement
)
RETURNS json
LANGUAGE sql
IMMUTABLE
STRICT
AS $function$
SELECT CASE COALESCE(array_length("key_path", 1), 0)
WHEN 0 THEN to_json("value_to_set")
WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
ELSE "json_object_set_key"(
"json",
"key_path"[l],
"json_object_set_path"(
COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
"key_path"[l+1:u],
"value_to_set"
)
)
END
FROM array_lower("key_path", 1) l,
array_upper("key_path", 1) u
$function$;
-------------end of JSON Helper functions-----------------------------
---------------------------------------------------------
-- demonstrate conversion in a select example:
---------------------------------------------------------
SELECT coord from (select json_object_set_path(f, '{"geometry","coordinates"}',
trans_coord(f->'geometry'->'coordinates'))
from json_array_elements('{ "type": "FeatureCollection",
"features": [
{ "type": "Feature",
"geometry": {"type": "Point", "coordinates": [550000, 6800000]}
},
{ "type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [
[550000.1, 6800000.1], [550000.2, 6800000.2]]
}}
]
}'::json->'features') as f) as coord;
------------------------------------------------------------
----- from a test table:
------------------------------------------------------------
create table if not exists gjtest(gj json);
delete from gjtest;
insert into gjtest values('{ "type": "FeatureCollection",
"features": [
{ "type": "Feature",
"geometry": {"type": "Point", "coordinates": [550000, 6800000]}
},
{ "type": "Feature",
"geometry": {
"type": "LineString",
"coordinates": [
[550000.1, 6800000.1], [550000.2, 6800000.2]]
}}
]
}');
---------------------------------------------------------------
-- test with a select:
---------------------------------------------------------------
select array_to_json(array_agg(coord.ngj))
from (select json_object_set_path(f, '{"geometry","coordinates"}',
trans_coord(f->'geometry'->'coordinates')) as ngj
from gjtest as gjt,json_array_elements(gjt.gj->'features') as f) as coord;
-----------------------------------------------------------------
-- update the data:
-----------------------------------------------------------------
update gjtest set gj = json_object_set_key(gj, 'features',
(select array_to_json(array_agg(coord.ngj))
from (select json_object_set_path(f, '{"geometry","coordinates"}',
trans_coord(f->'geometry'->'coordinates')) as ngj
from json_array_elements(gjtest.gj->'features') as f) as coord));
-----------------------------------------------------------------
-- check the result:
-----------------------------------------------------------------
select * from gjtest;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment