epsg28992.sql

----------------------------------------------------------------------------
-- 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;