5

I have been trying to segment lines at specific interval (2000 m) using St_LineSubstring function. So far, I managed to accomplish that with the following code:

SELECT ST_LineSubstring(the_geom, 2000.00*n/length,
  CASE
    WHEN 2000.00*(n+1) < length THEN 2000.00*(n+1)/length
    ELSE 1
  END) As the_geom
FROM
  (SELECT ST_LineMerge(disph_teste.geom) AS the_geom,
  ST_Length(disph_teste.geom) As length
  FROM disph_teste
  ) AS t
CROSS JOIN generate_series(0,10000) AS n
WHERE n*2000.00/length < 1;

The problem is that the input line has an n attribute which must be divided by the resulting segments number and increments until it reaches the original attribute value, like the image bellow. Still can't figure out a way to do so. Any Ideas?

enter image description here

6

I wrote a set of functions to easily handle dividing lines by length and by segment count, both in PL/pgSQL as simple .sql files, and C as PostGIS core add-ons (needs you to build PostGIS yourself) and I'm shamelessly going to promote it's usage here.

The following assumes to have one of both options installed - with the PL/pgSQL option being a trivially easy option!

Run:

SELECT ln.n::FLOAT / MAX(dmp.path[1]) OVER() * dmp.path[1] AS n, dmp.geom
FROM   <lines> AS ln,
       LATERAL ST_LineSubstringsByLength(ln.geom, 2000) AS dmp
;

Example:

WITH
    ln(n, geom) AS (
        VALUES(10, ST_SetSRID('LINESTRING(0 0, 1 0, 4 0, 8 0)'::GEOMETRY(LINESTRING), 4236))
    )
SELECT n::FLOAT / MAX(dmp.path[1]) OVER() * dmp.path[1], dmp.geom
FROM   ln,
       LATERAL ST_LineSubstringsByLength(geom, 2.5) AS dmp
;

+--------+-------------------------+
|n       |st_astext                |
+--------+-------------------------+
|2.5     |LINESTRING(0 0,1 0,2.5 0)|
|5       |LINESTRING(2.5 0,4 0,5 0)|
|7.5     |LINESTRING(5 0,7.5 0)    |
|10      |LINESTRING(7.5 0,8 0)    |
+--------+-------------------------+

Alternatively, without the function set installed, run:

SELECT n::FLOAT / (MAX(f) OVER() + 1) * (f + 1),
       ST_LineSubstring(ln.geom, f * 2000/len, LEAST((f+1) * 2000/len, 1.0)) AS geom
FROM   <lines> AS ln,
       LATERAL ST_Length(ln.geom) AS len
CROSS JOIN
       GENERATE_SERIES(0, FLOOR(len / 2000)::INT) AS f
;

Example:

WITH
    ln(n, geom) AS (
        VALUES(10, ST_SetSRID('LINESTRING(0 0, 1 0, 4 0, 8 0)'::GEOMETRY(LINESTRING), 4236))
    )
SELECT n::FLOAT / (MAX(f) OVER() + 1) * (f + 1),
       ST_AsText(ST_LineSubstring(geom, f * 2.5/len, LEAST((f+1) * 2.5/len, 1.0))) AS geom
FROM   ln,
       LATERAL ST_Length(geom) AS len
CROSS JOIN
       GENERATE_SERIES(0, FLOOR(len / 2.5)::INT) AS f
;


+--------+-------------------------+
|n       |geom                     |
+--------+-------------------------+
|2.5     |LINESTRING(0 0,1 0,2.5 0)|
|5       |LINESTRING(2.5 0,4 0,5 0)|
|7.5     |LINESTRING(5 0,7.5 0)    |
|10      |LINESTRING(7.5 0,8 0)    |
+--------+-------------------------+

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.