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.
No comments:
Post a Comment