Skip to content

Instantly share code, notes, and snippets.

View geozelot's full-sized avatar

André Siefken geozelot

View GitHub Profile
@geozelot
geozelot / point_set_minimum_spanning_tree.sql
Last active October 28, 2022 09:40
PostgreSQL/PostGIS - Fast MST over a set of POINT geometries.
/*
* Generates the minimum spanning tree (MST) over a set
* of POINT geometries. In pure SQL, this is about the most
* performant way for the specific case of point sets,
* given proper spatial indexing of the <graph> table/MV.
*
* Assumes the presence of an <id> and <geom> column in <graph>.
*/
WITH RECURSIVE
@geozelot
geozelot / ST_GeoHashNeighbors.sql
Last active March 25, 2024 15:13
PostgreSQL/PostGIS - Grid-based derivation of (8) neighborhood cells for a given GeoHash identifier.
/*
* @in_params
* center_hash - GeoHash identifier to find adjacent cells for
*
* @out_params (column description)
* hash - GeoHash identifier for this adjacent cell
* centroid - Centroid geometry (POINT) for this adjacent cell
* bound - Cell bounds geometry (POLYGON) for this adjacent cell
*
* The function returns a SETOF RECORD containing the 8 direct
@geozelot
geozelot / ST_ToblerSpeed.sql
Last active June 17, 2022 12:12
PostgreSQL/PostGIS/pgRouting - Implementation of Tobler's Hiking function for approximate hiking speeds with regard to terrain slope.
/*
* Public signatures common INPUT params:
* @track_mode - travel mode { 1: on_path | 2: off_path | 3: horse_back }; default 1
* @max_speed - maximum travel speed: default 1.67 [m/s -> 6km/h]
*
* GEOMETRY X|Y components are assumed to be in meter!
* GEOMETRY|GEOGRAPHY Z components are assumed to be in meter!
*
* Public signatures common OUTPUT fields:
* @slope - [degrees] inclination
@geozelot
geozelot / ST_SurfaceToDegree.sql
Last active February 13, 2023 19:16
PostgreSQL/PostGIS - Close approximation of distance in degrees for a given surface distance in meter at a given location and in a given direction
/*
* Distance in degree to the curve of an ellipse defined by the factor of latitudinal distance at a given
* longitude [x-axis] and 1.0 [y-axis], representing the varying factor by which longitudinal degrees scale
* to latitudinal degrees in relation to surface distance and varying angular direction of measurement.
*
* Returns a very close approximation of distance in degree of a sphere at given @latitude that corresponds to
* @distance meter surface distance, in the direction of north based @azimuth in degree, from the center of the ellipse.
* Angularity defaults to 90.0 (eastward) and shortcuts execution - corresponds to the simple factor of
* reduced surface distance per degree of longitude at increasing latitudes.
*/
@geozelot
geozelot / ST_MakeAGon.sql
Last active March 27, 2022 13:10
PostgreSQL/PostGIS - Create regular (equilateral & equiangular) Polygons (i.e. Hexagons, Pentagons, ...).
/*
* @in_params
* center - center POINT geometry
* radius - circumradius [in CRS units] (cirlce that inscribes the *gon)
* sides - desired side count (e.g. 6 for Hexagon)
* skew - rotation offset, clockwise [in degree]; DEFAULT 0.0 (corresponds to planar NORTH)
*
* @out_params
* agon - resulting *gon POLYGON geometry
*
@geozelot
geozelot / transaction_stats.sql
Last active August 17, 2021 11:24
PostgreSQL - Transaction Stats View
DROP VIEW transaction_stats;
DROP TYPE LOCK_MODE;
CREATE TYPE LOCK_MODE AS ENUM (
'AccessShareLock',
'RowShareLock',
'RowExclusiveLock',
'ShareUpdateExclusiveLock',
'ShareLock',
'ShareRowExclusiveLock',
@geozelot
geozelot / ST_AsFeatureCollection.sql
Last active June 6, 2023 09:15
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);
@geozelot
geozelot / ST_PointBufferWedge.sql
Last active January 7, 2022 20:08
PostgreSQL/PostGIS - Create a wedge of a buffer around a point, with optional inner radius
/*
* @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 October 5, 2022 13:47
PostgreSQL - Get absolute difference between azimuths (angular distance) in degrees depending on direction
-- 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