PostgreSQL/PostGIS - aggregate GeoJSON features into FeatureCollection

ST_AsFeatureCollection.sql

DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, TEXT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, INT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, TEXT, INT);
DROP AGGREGATE IF EXISTS public.ST_AsFeatureCollection(ANYELEMENT, INT, TEXT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, TEXT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, INT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, TEXT, INT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_transfn(collection TEXT[], item ANYELEMENT, INT, TEXT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_minvfn(collection TEXT[], item ANYELEMENT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_minvfn(collection TEXT[], item ANYELEMENT, TEXT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_minvfn(collection TEXT[], item ANYELEMENT, INT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_minvfn(collection TEXT[], item ANYELEMENT, TEXT, INT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_minvfn(collection TEXT[], item ANYELEMENT, INT, TEXT);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_finalfn(collection TEXT[]);
DROP FUNCTION IF EXISTS public._st_asfeaturecollection_combfn(TEXT[], TEXT[]);


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

A custom (moving) aggregate function set (with overloaded parameters) around 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