How to cast earth datatype to json in Postgresql

Question

I want to cast the earth datatype to a json array in Postgresql (9.5).

This is what I have:

CREATE OR REPLACE FUNCTION earth_to_json (inpt earth)
RETURNS json AS $$
    SELECT json_build_array(latitude(inpt), longitude(inpt))
$$ LANGUAGE SQL;

CREATE CAST (earth AS JSON) WITH FUNCTION earth_to_json(earth) AS IMPLICIT;

When executing the query above, I get the following output:

WARNING: cast will be ignored because the source data type is a domain 
CREATE CAST 
Query returned successfully in 47 msec.

So the earth datatype is domain, according to the message above. I know earthdistance is based on the cube module, but I assumed the earth datatype is a real datatype so I should be able to cast.

So if above code should work, the code below should run fine.

SELECT ll_to_earth(10.10, 30.01)::JSON

But I get the error

 ERROR: cannot cast type earth to json LINE 1

Martin Winkel Martin Winkel · Accepted Answer · 2019-04-26T08:41:21

What I found out:

The earth datatype is not a real datatype. This is the type cube, with constraints (constraints are defined in the earth-domain). So I really need to create a cast which casts the cube datatype to json.

When you accidentally have a cube with fits in the earth domain, it will be returned as lat, lon array. You could create your own datatype to work around this. For me this works ok, as I do not use the cube datatype.

CREATE OR REPLACE FUNCTION cube_to_json (inpt cube)
RETURNS json AS $$
BEGIN
    BEGIN
        RETURN json_build_array(latitude(inpt), longitude(inpt));
    EXCEPTION WHEN SQLSTATE '23514' THEN
        RETURN CASE WHEN json_array_length(sub) = 1 THEN sub->0 ELSE sub END FROM (SELECT translate('[' || inpt::TEXT || ']', '()', '[]')::JSON AS sub) AS o;
    END;
END;
$$ LANGUAGE plpgsql;


CREATE CAST (cube AS json) WITH FUNCTION cube_to_json(cube) AS IMPLICIT
-- Now test
SELECT (ll_to_earth(10,20))::JSON; -- Outputs [10,20]
SELECT ('(5437508.36471516, 3140612.41127573), (118.44062468, 1)'::CUBE)::JSON; -- Outputs "[[5437508.36471516, 3140612.41127573], [118.44062468, 1]]"

How to cast earth datatype to json in Postgresql

1 Answers