Add casts for (geometry, geography) -> (json, jsonb)

[dbaston]

Any reason not to do this? It seems as simple as

CREATE FUNCTION geometry_json_cast (geometry) RETURNS json 
AS $$ SELECT ST_AsGeoJSON($1)::json $$ LANGUAGE SQL;

CREATE CAST (geometry AS json) WITH FUNCTION geometry_json_cast(geometry);

Built-in functions like row_to_json will automatically pick up this cast and use it when converting geometries to JSON values.

[robe]

I think I had a reason before but can't remember it now.

I think when I had thought about it at the time json was not a built in type and then when it was a type, just create a cast for it, did not make row_to_json do the right thing, in that it only worked with built in casts, not user defined ones. I'm trying to find the thread where I was dicussing this with Andrew Dunstan and I can't find it now.

That said, row_to_json might be smarter these days. Have you tried it and does it pick it up?

[dbaston]

Yes, row_to_json and to_json/to_jsonb pick it up, at least in the 9.6 that I'm running. The cast doesn't event have to be declared IMPLICIT, apparently.

WITH TEST AS 
    (SELECT 1::int AS id, st_makepoint(random(), random()) as geom)
SELECT row_to_json(q) FROM (SELECT * from test) q;

In the 9.5 docs:

Returns the value as json or jsonb. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.

[robe]

okay then lets go for it. Even if it doesn't work in 9.4 or lower, I think it's still work it. I guess only concern I have if there are people who actually rely on the geometry coming out as bytea text rep in the json format, but I guess we can mark as a breaking change for those folks who are probably few.

[strk]

Please add automated tests for it, with appropriate conditions based on PostgreSQL version and such…

[pramsey]

In 17416:

Casts from geometry to json and jsonb
Allows to_json and to_jsonb to serialize geometry columns into json
for more intuitive json handling.
Closes #3687

[pramsey]

In 17417:

Add missing files from previous commits
References #1833 and #3687

[jawj]

Is there a reason not also to add the reverse casts?

I've found the following manual additions helpful:

CREATE CAST (json AS geometry) WITH FUNCTION ST_GeomFromGeoJSON(json) AS ASSIGNMENT;
CREATE CAST (jsonb AS geometry) WITH FUNCTION ST_GeomFromGeoJSON(jsonb) AS ASSIGNMENT;

So that I can insert JSON values back in to a table.