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;
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.