split line with points usando spatilite >4.4 KNN, CloneTable e ElementaryGeometries - by M. Trevisani

split_lines_with_point_KNN_v2.sql

--QUESTA FORMULAZIONE VERIFICA SE ESISTE E LO PRODUCE SOLO SE NON ESISTESSE 
--(ENTRAMBE LE FUNZIONI RESTITUISCONO 1, MA SE LA PRIMA RITORNA 1, LA SECONDA NON VIENE ESEGUITA)
SELECT 'Creazione indice spaziale su ', 'nodes_all','geom',
coalesce(checkspatialindex('nodes_all','geom'),CreateSpatialIndex('nodes_all','geom'));
SELECT 'Creazione indice spaziale su ', 'strade','geom',
coalesce(checkspatialindex('strade','geom'),CreateSpatialIndex('strade','geom'));
--INDIVIDUA, PER CIASCUN NODO, LA LINESTRING DI STRADE PIU' VICINA
SELECT dropgeotable('nearest_strade_to_nodes_all');
CREATE TABLE nearest_strade_to_nodes_all as
            SELECT c.pk_uid, c.id_all, c.id_nodes, d.distance as dist, d.fid as strade_pk
            FROM nodes_all as c 
        JOIN 
            (SELECT a.fid as fid, a.distance as distance, zz.pk_uid as pk_uid
            FROM knn as a JOIN nodes_all as zz
            WHERE f_table_name = 'strade' AND f_geometry_column = 'geom' AND ref_geometry = zz.geom AND max_items = 1) as d
        ON (d.pk_uid =c.pk_uid ) 
            ORDER BY c.pk_uid;
--CALCOLO DELLA PROIEZIONE DEL NODO SULLA STRADA
SELECT addgeometrycolumn('nearest_strade_to_nodes_all','geom',
        (SELECT cast(srid as integer)
        FROM geometry_columns 
        WHERE lower(f_table_name) = lower('strade') 
        AND lower(f_geometry_column) = lower('geom')),'point', 'xy');
--PER CIASCUN NODO DI nodes_all CALCOLA IL ClosestPoint SULLA LINESTRING DI strade PIU' VICINA
UPDATE nearest_strade_to_nodes_all SET geom = 
    (SELECT ST_ClosestPoint(a.geom, b.geom)
    FROM strade as a, nodes_all as b 
    WHERE a.pk=nearest_strade_to_nodes_all.strade_pk 
    AND b.pk_uid=nearest_strade_to_nodes_all.pk_uid);
SELECT 'Creazione indice spaziale su ', 'nearest_strade_to_nodes_all','geom',
coalesce(checkspatialindex('nearest_strade_to_nodes_all','geom'),
CreateSpatialIndex('nearest_strade_to_nodes_all','geom'));

-- A QUESTO PUNTO LA TABELLA nearest_strade_to_nodes_all CONTIENE NELLA COLONNA geom LA PROIEZIONE SULLA STRADA PIU' VICINA,
-- IN CORRISPONDENZA DI CASCUN PUNTO DI nodes_all. TALI PUNTI, CHE DUNQUE SONO SICURAMENTE SULLE LINESTRINGS DI STRADE, 
-- POSSONO ESSRE USATI PER TAGLIARE LE LINESTRINGS DI strade CON LA ST_SPLIT
--GENERA UNA TABELLA DI APPOGGIO DOVE CONSERVARE LE MULTILINESTRING PRODOTTE DALLA ST_SPLIT 
--OCCORRE QUINDI SNAPPARE strade ALLE PROIEZIONI DI nodes_all PRODUCENDO UNA NUOVA VERSIONE strade_snapped_to_projections_of_nodes_all
--LA CLONETABLE CREA UNA COPIA DI UNA TABELLA
--(IN QUESTA FORMA FORZANDO A MULTILINESTRING LA GEOMETRIA ORIGINALE DI strade CHE ERA LINESTRING)
SELECT DropGeoTable('strade_snapped_to_projections_of_nodes_all');
SELECT CloneTable('main', 'strade', 'strade_snapped_to_projections_of_nodes_all', 1,'::cast2multi::geom');
--LA ST_SNAP PROVOCA LA AGGIUNTA DI UN VERTICE A strade_snapped_to_projections_of_nodes_all IN CORRISPONDENZA 
--DEI PUNTI DI nearest_strade_to_nodes_all CHE RICADONO ENTRO LA TOLLERANZA (IO HO IMPOSTATO UN CENTIMETRO)
UPDATE strade_snapped_to_projections_of_nodes_all SET geom=
    CastToMulti(
                RemoveRepeatedPoints(
                                    ST_Snap( 
                                            strade_snapped_to_projections_of_nodes_all.geom,
                                            (SELECT casttomultipoint(st_collect(b.geom)) 
                                            FROM nearest_strade_to_nodes_all as b
                                            WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk 
                                            GROUP BY b.strade_pk) , 0.01 
                                            ), 0.01 
                                    )
                ) 
WHERE EXISTS(
            SELECT 1 FROM nearest_strade_to_nodes_all as b
            WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk limit 1
            );
--LA ST_SPLIT PROVOCA LA SPEZZATURA DELLE LINESTRING DI  strade_snapped_to_projections_of_nodes_all
--IN CORRISPONDENZA DEI PUNTI DI nearest_strade_to_nodes_all CHE RICADONO IN CORRISPONDENZA DI SUOI VERTICI
--(AGGIUNTI IN PRECEDENZA CON LA ST_SNAP)
UPDATE strade_snapped_to_projections_of_nodes_all SET geom=
    CastToMulti(
                ST_Split(
                        strade_snapped_to_projections_of_nodes_all.geom,
                        (SELECT CastToMultiPoint(st_collect(b.geom)) 
                        FROM nearest_strade_to_nodes_all as b
                        WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk 
                        GROUP BY b.strade_pk)
                        )
                )
WHERE EXISTS(
            SELECT 1 FROM nearest_strade_to_nodes_all as b
            WHERE b.strade_pk = strade_snapped_to_projections_of_nodes_all.pk limit 1
            );
--A QUESTO PUNTO POSSIAMO GENERARE LE COMPONENTI ELEMENTARI TRAMITE LA ElementaryGeometries CHE PRODUCE LA lines_split
--ESPLODENDO LE LINESTRINGS ORIGINARIE DI strade SNAPPATE E SPLITTATE ALLE PROIEZIONI DEI PUNTI DI nodes_all SULLE strade STESSE
SELECT DropGeoTable('lines_split');
SELECT ElementaryGeometries( 'strade_snapped_to_projections_of_nodes_all' ,
                             'geom' , 'lines_split' ,'out_pk' , 'out_multi_id', 1 ) as num, 'lines splitted' as label;
SELECT 'Creazione indice spaziale su ', 'strade','geom',
coalesce(checkspatialindex('lines_split','geom'),CreateSpatialIndex('lines_split','geom'));

SELECT UpdateLayerStatistics('lines_split');
SELECT DropGeoTable('strade_snapped_to_projections_of_nodes_all');
SELECT DropGeoTable('nearest_strade_to_nodes_all');