6

I have a line geometry in PostGIS and I am wondering how I would break the line into 100m segments. Is there a function for this?

1

2 Answers 2

8

The example in the PostGIS ST_LineSubstring doc is a bit dated. Modern Postgres has the JOIN LATERAL feature, which allows for more compact and efficient SQL. Here's a simple example:

WITH
data AS (
    SELECT * FROM (VALUES
        ( 'A', 'LINESTRING( 0 0, 200 0)'::geometry ),
        ( 'B', 'LINESTRING( 0 100, 350 100)'::geometry ),
        ( 'C', 'LINESTRING( 0 200, 50 200)'::geometry )
    ) AS t(id, geom)
)
SELECT ST_LineSubstring( d.geom, substart, 
    CASE WHEN subend > 1 THEN 1 ELSE subend END ) geom
FROM (SELECT id, geom, ST_Length(geom) len, 100 sublen FROM data) AS d
CROSS JOIN LATERAL (
    SELECT i,  
            (sublen * i)/len AS substart,
            (sublen * (i+1)) / len AS subend
        FROM generate_series(0, 
            floor( d.len / sublen )::integer ) AS t(i)
        WHERE (sublen * i)/len <> 1.0  
    ) AS d2;
1
  • Very nice; works perfectly.
    – D_C
    Feb 20, 2020 at 23:16
4

Use the linear referencing functions http://postgis.net/docs/manual-2.1/reference.html#Linear_Referencing

ST_LineSubstring is probably the one that you want to use http://postgis.net/docs/manual-2.1/ST_Line_Substring.html

Before using ST_Line_Substring you must query the total length of the linestring and build SQL that calculates a fraction that corresponds with 100 m.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.