Opened 7 years ago

Closed 5 years ago

Last modified 4 years ago

#3687 closed enhancement (fixed)

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

Reported by: dbaston Owned by: dbaston
Priority: medium Milestone: PostGIS 3.0.0
Component: postgis Version: master
Keywords: Cc:

Description

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.

Change History (9)

comment:1 by robe, 7 years ago

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?

comment:2 by dbaston, 7 years ago

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.

comment:3 by robe, 7 years ago

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.

comment:4 by strk, 7 years ago

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

comment:5 by robe, 7 years ago

Milestone: PostGIS 2.4.0PostGIS 2.5.0

comment:6 by komzpa, 6 years ago

Milestone: PostGIS 2.5.0PostGIS 3.0.0

comment:7 by pramsey, 5 years ago

Resolution: fixed
Status: newclosed

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

comment:8 by pramsey, 5 years ago

In 17417:

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

comment:9 by jawj, 4 years ago

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.

Note: See TracTickets for help on using tickets.