In PostGIS: how to split linestrings into their individual segments?

Question

I have one table with many linestrings and I want to make a new table containing the individual segments of all the linestrings, no matter if they are grouped line per line or not.

This is my table, really simple:

table myline (
  the_geom geometry(LineString,4326),
  id integer,
)

I already tried with no results code like this:

SELECT ST_AsText( ST_MakeLine(sp,ep) )
FROM
-- extract the endpoints for every 2-point line segment for each linestring
(SELECT
  ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
  ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
FROM
   -- extract the individual linestrings
  (SELECT (ST_Dump(ST_Boundary(ll.the_geom))).geom
   FROM mylines ll
   -- eliminate 0 length linestring 
    where st_x(st_startpoint(ll.the_geom))<> st_x(st_endpoint(ll.the_geom))
   ) AS linestrings
) AS segments;

and this:

select st_astext(st_makeline(st_pointn(a.line, a.idx), st_pointn(a.line, a.idx+1))) as txt
from (select the_lines as line, traj_id as idx from mylines) as a

I have to say that I created the lines from a set of GPS points I have from the dataset, so another solution could be create segment lines from the points directly.

Answer 1

You are going about it the right way, using ST_PointN and generate_series. One way of doing this, using dummy data (substitute your own in initial CTE) would be:

WITH 
  sample(geom, id) AS 
     (VALUES
       (ST_MakePoint(0,0), 1), 
       (ST_MakeLine(
            ARRAY[ST_MakePoint(0, 0), ST_MakePoint(10,10),
                  ST_MakePoint(50, 50), ST_MakePoint(100,100)]), 2)
     ),
  line_counts (cts, id) AS 
        (SELECT ST_NPoints(geom) -1 , id FROM sample),
  series (num , id) AS 
        (SELECT generate_series(1, cts), id FROM line_counts)
  SELECT ST_MakeLine(
               ST_PointN(geom, num), 
               ST_PointN(geom, num + 1)) as geom, 
        sample.id 
  FROM series 
  INNER JOIN sample ON series.id = sample.id;

1. Create some geometries
2. Count the number of segments per line segment (points minus 1) per geometry id
3. Use generate_series to create series up to one less than the number of points in line segments
4. Create the segments for each geometry using id from input geometry (sample, step 1) and series generated (step 3).

The return from this is:

geom | id

LINESTRING(0 0,10 10) | 2

LINESTRING(10 10,50 50) | 2

LINESTRING(50 50,100 100) | 2

as expected, nothing from the point, and 3 line segments from the original linestring with 4 points.

There are many other ways of doing this, but I prefer this approach with just one call to generate_series for the indexing into points.

Answer 2

I found this high performance alternative:

WITH segments AS (
SELECT gid, ST_AsText(ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY gid ORDER BY gid, (pt).path), (pt).geom)) AS geom
  FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM lines) as dumps
)
SELECT * FROM segments WHERE geom IS NOT NULL;

source: http://blog.cleverelephant.ca/2015/02/breaking-linestring-into-segments.html

Answer 3

Pre-PostGIS 3.2 solution

WITH sample_data AS (
    SELECT 1 id, ST_GeomFromText('LINESTRING(0 0,1 0,1 1,7 3)') geom
    UNION
    SELECT 2, ST_GeomFromText('LINESTRING(2 0,2 2,0 2,9 5)')
)
SELECT id,(ST_Dump(ST_Union(geom,geom))).geom
FROM sample_data

Here ST_Union turns the LINESTRING into a MULTILINESTRING which subsequentially gets dumped into segments by ST_Dump. I do not know why this works. I found it by an accident.

Answer 4

As of PostGIS version 3.2.0 there is a built in function for this:

geometry_dump[] ST_DumpSegments(geometry geom);

You can find complete description with examples in the official documentation - ST_DumpSegments.