Skip to content

Instantly share code, notes, and snippets.

@geozelot
Last active June 6, 2023 09:15
Show Gist options
  • Save geozelot/b2e5cd65dd7f85ec381aeee14e0149ae to your computer and use it in GitHub Desktop.
Save geozelot/b2e5cd65dd7f85ec381aeee14e0149ae to your computer and use it in GitHub Desktop.
PostgreSQL/PostGIS - aggregate GeoJSON features into FeatureCollection
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
);
@geozelot
Copy link
Author

geozelot commented Sep 29, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment