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?


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.


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:

data AS (
        ( '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
    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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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