Opened 4 months ago
Last modified 3 months ago
#5775 new defect
ST_AsGeoJSONAgg which is aggregate version of ST_AsGeoJSON
Reported by: | robe | Owned by: | pramsey |
---|---|---|---|
Priority: | medium | Milestone: | PostGIS Fund Me |
Component: | postgis | Version: | master |
Keywords: | Cc: |
Description
I was thinking about this some, and realized when I want to use ST_AsGeoJSON, I usually combine it with something like jsonb_buildobject or json_buildobject, cause ST_AsGeoJSON has no version that supports aggregates like ST_AsMVT has.
Wouldn't it be nice if one could do this:
SELECT ST_AsGeoJSONAgg(t.*, id_column => 'id')::json FROM ( VALUES (1, 'one', 'POINT(1 1)'::geometry), (2, 'two', 'POINT(2 2)'), (3, 'three', 'POINT(3 3)') ) as t(id, name, geom);
and it would output the same result as what this does
SELECT json_build_object( 'type', 'FeatureCollection', 'features', json_agg(ST_AsGeoJSON(t.*, id_column => 'id')::json) ) FROM ( VALUES (1, 'one', 'POINT(1 1)'::geometry), (2, 'two', 'POINT(2 2)'), (3, 'three', 'POINT(3 3)') ) as t(id, name, geom);
Now sadly we can't overload ST_AsGeoJSON here, for the same reason we couldn't overload ST_Union, cause the above would be ambiguous as to if you want to aggregate or do row by row.
Change History (7)
comment:1 by , 4 months ago
follow-up: 3 comment:2 by , 4 months ago
I mean, it removes one level of functions, but it will be at core mostly just copying pgsql core code. Why is the pgsql solution so unacceptable?
comment:3 by , 4 months ago
Replying to pramsey:
I mean, it removes one level of functions, but it will be at core mostly just copying pgsql core code. Why is the pgsql solution so unacceptable?
It's not that unacceptable I guess, just hard to remember.
Much easier to remember
SELECT ST_AsGeoJSONAgg(t, id_column => 'id') FROM t;
But yah it has the annoyance of adding an extra function is my only issue. But it would mean it would be in line with the others
ST_AsMVT , ST_AsFlatGeoBuf
cause honestly everytime I am doing ST_AsGeoJSON, what I really want is an aggregate function, not this row thing that requires me to do more work.
comment:4 by , 4 months ago
Milestone: | PostGIS 3.5.0 → PostGIS Fund Me |
---|
comment:5 by , 4 months ago
Whatever happens about this, don't remove the ability to convert a geometry to a GeoJSON geometry object. That is very useful, since it's doing a lot of work that other JSON generators can't do. It's used in pg_featureserv
(for example).
Possibly if we could do this over it would make sense to have an ST_AsGeoJSONGeom
function that just handles geometry conversion (a la ST_AsMVTGeom`)
follow-up: 7 comment:6 by , 4 months ago
But I do see Paul's point that Postgres already has good support for converting rows to JSON, so this code would be duplicating that functionality.
comment:7 by , 3 months ago
Replying to mdavis:
But I do see Paul's point that Postgres already has good support for converting rows to JSON, so this code would be duplicating that functionality.
Yah I wasn't thinking of getting rid of geom. Just hate having to remember that json_build_object nonsense. It's even more annoying than the ST_SetSRID(ST_Point(1,2), 4326) that necessitated us to add srid as an extra arg to ST_Point. About as annoying as that thing we used to do before we had ST_MakeEnvelope. It just irks me that I have never had a need for using ST_AsGeoJSON (passing in a row) except for case where I want to aggregate. But it can wait I guess and perhaps not a great idea.
Seems like a nice enhancement.