ST_AsGeoJSONAgg which is aggregate version of ST_AsGeoJSON

[robe]

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.

[mdavis]

Seems like a nice enhancement.

[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?

[robe]

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.

[mdavis]

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`)

[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.

[robe]

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.