Opened 4 years ago

Closed 18 months ago

Last modified 2 months 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:


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

CREATE FUNCTION geometry_json_cast (geometry) RETURNS json 

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 Changed 4 years ago by 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?

comment:2 Changed 4 years ago by 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.

    (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 Changed 4 years ago by 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.

comment:4 Changed 4 years ago by strk

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

comment:5 Changed 3 years ago by robe

Milestone: PostGIS 2.4.0PostGIS 2.5.0

comment:6 Changed 2 years ago by komzpa

Milestone: PostGIS 2.5.0PostGIS 3.0.0

comment:7 Changed 18 months ago by pramsey

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 Changed 18 months ago by pramsey

In 17417:

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

comment:9 Changed 2 months ago by jawj

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

I've found the following manual additions helpful:


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

Note: See TracTickets for help on using tickets.