一 創(chuàng)建測試表
創(chuàng)建一個測試表
CREATE TABLE lines (
gid integer primary key,
geom geometry(Linestring, 4326)
);
插入測試數(shù)據(jù)
INSERT INTO lines VALUES (1, 'SRID=4326;LINESTRING(1 1, 2 2, 3 3, 4 4)');
INSERT INTO lines VALUES (2, 'SRID=4326;LINESTRING(0 1, 0 2, 0 3, 0 4)');
二 拆分成點
SELECT gid,(pt).path as ptindex,(pt).geom
FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM lines) as foo;
拆點.png
三 拆分成線
WITH segments AS (
SELECT gid, ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY gid ORDER BY gid, (pt).path), (pt).geom) AS geom
FROM (SELECT gid, ST_DumpPoints(geom) AS pt FROM lines) as dumps
)
SELECT * FROM segments WHERE geom IS NOT NULL;
拆segment.png
