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.
Best Answer
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:
The return from this is:
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.