Last active
March 22, 2026 21:07
-
-
Save geozelot/b2e5cd65dd7f85ec381aeee14e0149ae to your computer and use it in GitHub Desktop.
PostgreSQL/PostGIS - aggregate GeoJSON features into FeatureCollection
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| * A custom (moving) aggregate function set (with overloaded parameters) wrapping native ST_AsGeoJSON(RECORD), returning JSONB directly. | |
| */ | |
| SELECT ST_AsFeatureCollection( [DISTINCT] t.*[,<options>] [ORDER BY] ) [FILTER] [OVER( [PARTITION BY] [ORDER BY] )] [::TEXT] | |
| FROM <table_expression> AS t | |
| ; | |
| where <options> can be (any combination of) | |
| INT - specifying the coordinate precision | |
| TEXT - specifying the geometry column name | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_transfn(INOUT c TEXT[], IN i ANYELEMENT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1 || ST_AsGeoJSON($2) $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_transfn(INOUT c TEXT[], IN i ANYELEMENT, d INT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1 || ST_AsGeoJSON($2, maxdecimaldigits:=d) $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_transfn(INOUT c TEXT[], IN i ANYELEMENT, n TEXT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1 || ST_AsGeoJSON($2, geom_column:=n) $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_transfn(INOUT c TEXT[], IN i ANYELEMENT, n TEXT, d INT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1 || ST_AsGeoJSON($2, n, d) $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_transfn(INOUT c TEXT[], IN i ANYELEMENT, d INT, n TEXT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1 || ST_AsGeoJSON($2, n, d) $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_minvfn(INOUT c TEXT[], IN i ANYELEMENT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1[:CARDINALITY($1)-1] $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_minvfn(INOUT c TEXT[], IN i ANYELEMENT, d INT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1[:CARDINALITY($1)-1] $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_minvfn(INOUT c TEXT[], IN i ANYELEMENT, n TEXT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1[:CARDINALITY($1)-1] $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_minvfn(INOUT c TEXT[], IN i ANYELEMENT, n TEXT, d INT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1[:CARDINALITY($1)-1] $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_minvfn(INOUT c TEXT[], IN i ANYELEMENT, d INT, n TEXT) | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT $1[:CARDINALITY($1)-1] $$; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_combfn(INOUT c_a TEXT[], IN c_b TEXT[]) | |
| LANGUAGE INTERNAL IMMUTABLE STRICT | |
| AS 'text_concat'; | |
| ; | |
| CREATE OR REPLACE FUNCTION public._st_asfeaturecollection_finalfn(c TEXT[]) | |
| RETURNS JSONB | |
| LANGUAGE SQL IMMUTABLE STRICT | |
| AS $$ SELECT JSONB_BUILD_OBJECT('type', 'FeatureCollection', 'features', $1::JSONB[]) $$; | |
| ; | |
| CREATE OR REPLACE AGGREGATE public.ST_AsFeatureCollection(ANYELEMENT) ( | |
| SFUNC = public._st_asfeaturecollection_transfn, | |
| COMBINEFUNC = public._st_asfeaturecollection_combfn, | |
| STYPE = TEXT[], | |
| INITCOND = '{}', | |
| FINALFUNC = public._st_asfeaturecollection_finalfn, | |
| MSTYPE = TEXT[], | |
| MINITCOND = '{}', | |
| MSFUNC = public._st_asfeaturecollection_transfn, | |
| MINVFUNC = public._st_asfeaturecollection_minvfn, | |
| MFINALFUNC = public._st_asfeaturecollection_finalfn, | |
| PARALLEL = SAFE | |
| ); | |
| CREATE OR REPLACE AGGREGATE public.ST_AsFeatureCollection(ANYELEMENT, geom_col TEXT) ( | |
| SFUNC = public._st_asfeaturecollection_transfn, | |
| COMBINEFUNC = public._st_asfeaturecollection_combfn, | |
| STYPE = TEXT[], | |
| INITCOND = '{}', | |
| FINALFUNC = public._st_asfeaturecollection_finalfn, | |
| MSTYPE = TEXT[], | |
| MINITCOND = '{}', | |
| MSFUNC = public._st_asfeaturecollection_transfn, | |
| MINVFUNC = public._st_asfeaturecollection_minvfn, | |
| MFINALFUNC = public._st_asfeaturecollection_finalfn, | |
| PARALLEL = SAFE | |
| ); | |
| CREATE OR REPLACE AGGREGATE public.ST_AsFeatureCollection(ANYELEMENT, max_digits INT) ( | |
| SFUNC = public._st_asfeaturecollection_transfn, | |
| COMBINEFUNC = public._st_asfeaturecollection_combfn, | |
| STYPE = TEXT[], | |
| INITCOND = '{}', | |
| FINALFUNC = public._st_asfeaturecollection_finalfn, | |
| MSTYPE = TEXT[], | |
| MINITCOND = '{}', | |
| MSFUNC = public._st_asfeaturecollection_transfn, | |
| MINVFUNC = public._st_asfeaturecollection_minvfn, | |
| MFINALFUNC = public._st_asfeaturecollection_finalfn, | |
| PARALLEL = SAFE | |
| ); | |
| CREATE OR REPLACE AGGREGATE public.ST_AsFeatureCollection(ANYELEMENT, geom_col TEXT, max_digits INT) ( | |
| SFUNC = public._st_asfeaturecollection_transfn, | |
| COMBINEFUNC = public._st_asfeaturecollection_combfn, | |
| STYPE = TEXT[], | |
| INITCOND = '{}', | |
| FINALFUNC = public._st_asfeaturecollection_finalfn, | |
| MSTYPE = TEXT[], | |
| MINITCOND = '{}', | |
| MSFUNC = public._st_asfeaturecollection_transfn, | |
| MINVFUNC = public._st_asfeaturecollection_minvfn, | |
| MFINALFUNC = public._st_asfeaturecollection_finalfn, | |
| PARALLEL = SAFE | |
| ); | |
| CREATE OR REPLACE AGGREGATE public.ST_AsFeatureCollection(ANYELEMENT, max_digits INT, geom_col TEXT) ( | |
| SFUNC = public._st_asfeaturecollection_transfn, | |
| COMBINEFUNC = public._st_asfeaturecollection_combfn, | |
| STYPE = TEXT[], | |
| INITCOND = '{}', | |
| FINALFUNC = public._st_asfeaturecollection_finalfn, | |
| MSTYPE = TEXT[], | |
| MINITCOND = '{}', | |
| MSFUNC = public._st_asfeaturecollection_transfn, | |
| MINVFUNC = public._st_asfeaturecollection_minvfn, | |
| MFINALFUNC = public._st_asfeaturecollection_finalfn, | |
| PARALLEL = SAFE | |
| ); |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
A custom (moving) aggregate function set (with overloaded parameters) around
ST_AsGeoJSON(RECORD), returningJSONBdirectly.where
<options>can be (any combination of)INT- specifying the coordinate precisionTEXT- specifying the geometry column name