Wednesday, 6 February 2019

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


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.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...