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 mdavis, 4 months ago

Seems like a nice enhancement.

comment:2 by pramsey, 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?

in reply to:  2 comment:3 by robe, 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.

Last edited 4 months ago by robe (previous) (diff)

comment:4 by robe, 4 months ago

Milestone: PostGIS 3.5.0PostGIS Fund Me

comment:5 by mdavis, 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`)

comment:6 by mdavis, 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.

in reply to:  6 comment:7 by robe, 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.

Note: See TracTickets for help on using tickets.