Skip to content

Instantly share code, notes, and snippets.

@geozelot
geozelot / ST_AsFeatureCollection.sql
Last active Jun 14, 2021
PostgreSQL/PostGIS - aggregate GeoJSON features into FeatureCollection
View 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);
@geozelot
geozelot / ST_PointBufferWedge.sql
Last active Jun 14, 2021
PostgreSQL/PostGIS - Create a wedge of a buffer around a point, with optional inner radius
View ST_PointBufferWedge.sql
/*
* @params
* pnt - center POINT geometry
* sdeg - wedge sector start value; in degrees
* edeg - wedge sector end value; in degrees
* irad - radius around @pnt for the inner extend of the wedge; in meter
* orad - radius around @pnt for the outer extend of the wedge; in meter
* qsegs - vertices per full quarter circle; at least two vertices will be created
*
* The function will calculate the angular difference in clockwise direction, from @sdeg to @edeg.
@geozelot
geozelot / cw_ccw_angle.sql
Last active Sep 25, 2020
PostgreSQL - Get absolute difference between azimuths (angular distance) in degrees depending on direction
View cw_ccw_angle.sql
-- clockwise angular distance in degrees
CREATE OR REPLACE FUNCTION CWAngle(
IN sdeg FLOAT,
IN edeg FLOAT,
OUT ddeg FLOAT
) LANGUAGE SQL AS
$$
SELECT CASE (sdeg <= edeg)
WHEN TRUE THEN
edeg - sdeg