scrip_SQL_split_lines_with_points.sql

--
-- split lines with point, the Spatialite way
-- di Salvatore Fiandaca
-- e-mail: pigrecoinfinito@gmail.com
--
CREATE TABLE "points_over_lines" AS
SELECT a.id AS id,ST_ClosestPoint(ST_Union(b.geom), a.geom) AS geom
FROM civici a, strade b
GROUP BY a.geom,a.id;
SELECT RecoverGeometryColumn('points_over_lines','geom',3004,'POINT','XY');

CREATE TABLE "lines_nodes_densified" AS
SELECT pk AS id, ST_Union(ST_DissolvePoints(st_segmentize(geom,1))) AS geom
FROM strade;
SELECT RecoverGeometryColumn('lines_nodes_densified','geom',3004,'MULTIPOINT','XY');

CREATE TABLE "points_snapped" AS
SELECT b.id AS id, ST_snap(ST_Union(b.geom),a.geom, ST_Distance(a.geom,b.geom)*1.01) AS geom
FROM "lines_nodes_densified" a, "points_over_lines" b
GROUP BY a.geom, b.geom, b.id;
SELECT RecoverGeometryColumn('points_snapped','geom',3004,'POINT','XY');

CREATE TABLE "lines_split" AS
SELECT a.id AS id, ST_LinesCutAtNodes(st_segmentize(a.geom,1),ST_Union(b.geom)) AS geom
FROM strade a, "points_snapped" b
GROUP BY a.id,a.geom;
SELECT RecoverGeometryColumn('lines_split','geom',3004,'MULTILINESTRING','XY');

CREATE TABLE "lines_split_dump" AS
SELECT t.id AS multi_id, e.item_no,e.geometry
FROM "lines_split" t
JOIN ElementaryGeometries e ON (e.f_table_name = 'lines_split' AND e.origin_rowid = t.id);
SELECT RecoverGeometryColumn('lines_split_dump','geometry',3004,'LINESTRING','XY');
--
-- cancello le tabelle inutili, i primi 4 step
--
drop table points_over_lines;
drop table lines_nodes_densified;
drop table points_snapped;
drop table lines_split;
--
-- aggiorno statistiche e VACUUM
--
UPDATE geometry_columns_statistics set last_verified = 0;
SELECT UpdateLayerStatistics('geometry_table_name');
VACUUM;