Skip to content

Instantly share code, notes, and snippets.

@dschep

dschep/ST_LineChunk.sql

Last active January 10, 2021 02:43
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dschep/a6b510eb48933673a03bc9fb4e7647b7 to your computer and use it in GitHub Desktop.
Save dschep/a6b510eb48933673a03bc9fb4e7647b7 to your computer and use it in GitHub Desktop.
Download ZIP
Line chunking implementations for PostGIS
Raw
ST_LineChunk.sql
CREATE OR REPLACE FUNCTION ST_LineChunk(geom geometry, max_length float8) RETURNS SETOF geometry AS $$
WITH
points AS (
SELECT generate_series(0, CEIL(ST_Length(geom) / max_length)::int)
/ CEIL(ST_Length(geom) / max_length) "end"
),
line_points AS (SELECT LAG("end", 1) OVER (ORDER BY "end") "start", "end" FROM points)
SELECT ST_LineSubstring(geom, "start", "end")
FROM line_points
WHERE "start" IS NOT NULL AND "start" <> 1
$$ LANGUAGE 'sql' IMMUTABLE;
Raw
ST_LineChunkMeters.sql
CREATE OR REPLACE FUNCTION ST_LineChunkMeters(geom geometry, max_length float8) RETURNS SETOF geometry AS $$
WITH
points AS (
SELECT generate_series(0, CEIL(ST_LengthSpheroid(geom, 'SPHEROID["GRS_1980",6378137,298.257222101]') / max_length)::int)
/ CEIL(ST_LengthSpheroid(geom, 'SPHEROID["GRS_1980",6378137,298.257222101]') / max_length) "end"
),
line_points AS (SELECT LAG("end", 1) OVER (ORDER BY "end") "start", "end" FROM points)
SELECT ST_LineSubstring(geom, "start", "end")
FROM line_points
WHERE "start" IS NOT NULL AND "start" <> 1
$$ LANGUAGE 'sql' IMMUTABLE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment