7

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.

enter image description here

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.

  • Can you share maybe a little of what you tried in terms of SQL? That would probably help you find a solution. Have you tried a combination of ST_DumpPoints and ST_Split? – SaultDon Dec 19 '15 at 2:59
16

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.

  • Does this not work for you in some way? If you leave a comment, I can attempt to fix. – John Powell Dec 24 '15 at 9:30
  • What does CTE stand for in this context? – Eva Großmann Feb 6 '18 at 11:19
  • 2
    @EvaGroßmann, CTE means common table expression, it is the official term for queries starting with WITH, see the CTE docs. – John Powell Feb 6 '18 at 11:58

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.