--=========================================================================================
--==     SQL session end   =   2021-06-11 13:10:49   =   53 statements were executed
--=========================================================================================


--=========================================================================================
--==     SQL session start   =   2021-06-11 13:13:44
--=========================================================================================
DROP TABLE IF EXISTS "nodes";
--=== -- Execution time: 0.000

CREATE TABLE nodes (pk INTEGER PRIMARY KEY,id INTEGER, tipo TEXT);
--=== -- Execution time: 0.015

SELECT AddGeometryColumn('nodes', 'geom', 32632, 'POINT', 'XY');
-- --------------------------------------------------------
-- AddGeometryColumn('nodes', 'geom', 32632, 'POINT', 'XY')
-- --------------------------------------------------------
-- 0
--=== 1 row === -- Execution time: 0.016

INSERT INTO nodes (pk,id,tipo, geom)
SELECT null,id,'S' AS tipo, ST_StartPoint(geom) AS geom FROM ebw_route_202103 
UNION ALL
SELECT null,id,'E' AS tipo,ST_EndPoint(geom) AS geom FROM ebw_route_202103;
--=== -- Execution time: 0.812

SELECT CreateSpatialIndex('nodes','geom');
-- ----------------------------------
-- CreateSpatialIndex('nodes','geom')
-- ----------------------------------
-- 0
--=== 1 row === -- Execution time: 0.000

DROP TABLE IF EXISTS "T";
--=== -- Execution time: 0.016

CREATE TABLE T AS
SELECT id, CastToMultiPoint(geom) AS geom
FROM nodes
WHERE geom IN (
SELECT zz.geom
FROM nodes zz, ebw_route_202103 a
WHERE zz.id != a.id -- linee diverse
AND round (ST_Distance (zz.geom, a.geom),4)  = 0.0
AND a.pk_uid IN (
        SELECT rowid
        FROM SpatialIndex
        WHERE f_table_name = 'ebw_route_202103'
           AND search_frame = BuildCircleMbr(ST_X(zz.geom), ST_Y(zz.geom), 1))
GROUP BY zz.geom HAVING count(zz.geom) = 1);
--=== -- Execution time: 22.955

SELECT RecoverGeometryColumn('T','geom',32632,'MULTIPOINT','XY');
-- ---------------------------------------------------------
-- RecoverGeometryColumn('T','geom',32632,'MULTIPOINT','XY')
-- ---------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.125

DROP TABLE IF EXISTS "route_T";
--=== -- Execution time: 0.000

CREATE TABLE route_T AS
SELECT a.id as id, CastToMultiLineString(a.geom) AS geom
FROM T zz, ebw_route_202103 a
WHERE zz.id != a.id -- linee diverse
AND round (ST_Distance (zz.geom, a.geom),4)  = 0.0
AND a.pk_uid IN (
        SELECT rowid
        FROM SpatialIndex
        WHERE f_table_name = 'ebw_route_202103'
           AND search_frame = BuildCircleMbr(ST_X(zz.geom), ST_Y(zz.geom), 1))
GROUP BY zz.geom HAVING count(zz.geom) = 1;
--=== -- Execution time: 0.000

SELECT RecoverGeometryColumn('route_T','geom',32632,'MULTILINESTRING','XY');
-- --------------------------------------------------------------------
-- RecoverGeometryColumn('route_T','geom',32632,'MULTILINESTRING','XY')
-- --------------------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.109

DROP TABLE IF EXISTS "route_finaleT";
--=== -- Execution time: 0.000

SELECT CloneTable('MAIN', 'ebw_route_202103', 'route_finaleT', 1);
-- ----------------------------------------------------------
-- CloneTable('MAIN', 'ebw_route_202103', 'route_finaleT', 1)
-- ----------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 1.790

DELETE FROM route_finaleT
WHERE id IN (
SELECT a.id as id
FROM T zz, ebw_route_202103 a       
WHERE zz.id != a.id -- linee diverse
AND round (ST_Distance (zz.geom, a.geom),4)  = 0.0
AND a.pk_uid IN (
        SELECT rowid
        FROM SpatialIndex
        WHERE f_table_name = 'ebw_route_202103'
           AND search_frame = BuildCircleMbr(ST_X(zz.geom), ST_Y(zz.geom), 1))
GROUP BY zz.geom HAVING count(zz.geom) = 1);
--=== -- Execution time: 0.156

DROP TABLE IF EXISTS "T2";
--=== -- Execution time: 0.000

CREATE TABLE T2 AS
SELECT zz.id AS id, a.id AS id2, zz.geom
FROM T AS zz, route_T a
WHERE zz.id != a.id AND round (ST_Distance (zz.geom, a.geom),4)  = 0.0;
--=== -- Execution time: 0.000

SELECT RecoverGeometryColumn('T2','geom',32632,'MULTIPOINT','XY');
-- ----------------------------------------------------------
-- RecoverGeometryColumn('T2','geom',32632,'MULTIPOINT','XY')
-- ----------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.172

UPDATE route_T SET geom=
        CastToMulti(
                RemoveRepeatedPoints(
                                    ST_Snap( 
                                            route_T.geom,
                                            (SELECT CastToMultipoint(st_collect(b.geom)) 
                                            FROM T2 as b
                                            WHERE b.id2 = route_T.id 
                                            GROUP BY b.id2) , 0.001 
                                            ), 0.001 
                                    )
        )                    
WHERE EXISTS(
            SELECT 1 FROM T2 as b
            WHERE b.id2 = route_T.id limit 1
            );
--=== -- Execution time: 0.000

UPDATE route_T SET geom=
    CastToMulti(
                ST_Split(
                        route_T.geom,
                        (SELECT CastToMultiPoint(st_collect(b.geom)) 
                        FROM T2 as b
                        WHERE b.id2 = route_T.id 
                        GROUP BY b.id2)
                        )
                )
WHERE EXISTS(
            SELECT 1 FROM T2 as b
            WHERE b.id2 = route_T.id limit 1
            );
--=== -- Execution time: 0.015

DROP TABLE IF EXISTS "route_T_elem";
--=== -- Execution time: 0.000

SELECT Elementarygeometries( 'route_T' ,
                             'geom' , 'route_T_elem' ,'out_pk_uid' , 'out_multi_id', 1 ) as num,
							 'lines splitted' as label;
-- ---+-----
-- num|label
-- ---+-----
-- 4|lines splitted
--=== 1 row === -- Execution time: 0.031

INSERT INTO route_finaleT ("pk_uid", "id", "geom")
SELECT NULL,id, CastToMultiLineString("geom") AS geom
FROM route_T_elem;
--=== -- Execution time: 0.000

DROP TABLE IF EXISTS "route_finaleTall";
--=== -- Execution time: 0.000

SELECT CreateClonedTable('MAIN', 'ebw_route_202103', 'route_finaleTall', 1);
-- --------------------------------------------------------------------
-- CreateClonedTable('MAIN', 'ebw_route_202103', 'route_finaleTall', 1)
-- --------------------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.032

INSERT INTO route_finaleTall ("pk_uid","id", "note", "illuminazi", "nome", "minit_1014", "id_tratta", "lungh_calc", "altezza",
"cavi_tot", "tipo", "larghezza", "stato_costr", "ente", "minit_1012", "dett_ente", "proprietar",
"tvinfratel", "ebwtvinfr", "num_tubi", "lungh_mis", "ebw_utiliz", "posa_aerea", "categoria","geom")
SELECT NULL, c."id", a."note", a."illuminazi", a."nome", a."minit_1014", a."id_tratta", a."lungh_calc", a."altezza",
a."cavi_tot", a."tipo", a."larghezza", a."stato_costr", a."ente", a."minit_1012", a."dett_ente", a."proprietar",
a."tvinfratel", a."ebwtvinfr", a."num_tubi", a."lungh_mis", a."ebw_utiliz", a."posa_aerea", a."categoria", CastToMultiLineString(c."geom") AS geom
FROM ebw_route_202103 a, route_finaleT c
WHERE a.id = c.id;
--=== -- Execution time: 2.859

DROP TABLE IF EXISTS nodes;
--=== -- Execution time: 0.063

DROP TABLE IF EXISTS T2;
--=== -- Execution time: 0.015

DROP TABLE IF EXISTS route_T;
--=== -- Execution time: 0.000

DROP TABLE IF EXISTS route_T_elem;
--=== -- Execution time: 0.016

DROP TABLE IF EXISTS "X";
--=== -- Execution time: 0.015

CREATE TABLE X AS
SELECT t1.id as id,t2.id as id2, CastToMulti(st_intersection(t1.geom,t2.geom)) as geom
FROM route_finaleTall t1, route_finaleTall t2
WHERE ST_Crosses (t1.geom, t2.geom) = 1
AND t1.pk_uid IN (
        SELECT rowid
        FROM SpatialIndex
        WHERE f_table_name = 'route_finaleTall'
           AND search_frame = t2.geom);
--=== -- Execution time: 27.234

SELECT RecoverGeometryColumn('X','geom',32632,'MULTIPOINT','XY');
-- ---------------------------------------------------------
-- RecoverGeometryColumn('X','geom',32632,'MULTIPOINT','XY')
-- ---------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.109

DROP TABLE IF EXISTS "route_X";
--=== -- Execution time: 0.000

CREATE TABLE route_X AS
SELECT t1.id as id, CastToMultiLineString(t1.geom) AS geom
FROM route_finaleTall t1, route_finaleTall t2
WHERE ST_Crosses (t1.geom, t2.geom) = 1
AND t1.pk_uid IN (
        SELECT rowid
        FROM SpatialIndex
        WHERE f_table_name = 'route_finaleTall'
           AND search_frame = t2.geom)
GROUP BY 1;
--=== -- Execution time: 28.481

SELECT RecoverGeometryColumn('route_X','geom',32632,'MULTILINESTRING','XY');
-- --------------------------------------------------------------------
-- RecoverGeometryColumn('route_X','geom',32632,'MULTILINESTRING','XY')
-- --------------------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.125

DROP TABLE IF EXISTS "route_finale";
--=== -- Execution time: 0.000

SELECT CloneTable('MAIN', 'route_finaleTall', 'route_finale', 1,':cast2multi::geom');
-- -----------------------------------------------------------------------------
-- CloneTable('MAIN', 'route_finaleTall', 'route_finale', 1,':cast2multi::geom')
-- -----------------------------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 1.868

DELETE FROM route_finale
WHERE pk_uid IN (
SELECT t1.pk_uid as pk_uid
FROM route_finaleTall t1, route_finaleTall t2
WHERE ST_Crosses (t1.geom, t2.geom) = 1
AND t1.pk_uid IN (
        SELECT rowid
        FROM SpatialIndex
        WHERE f_table_name = 'route_finaleTall'
           AND search_frame = t2.geom)
GROUP BY 1);
--=== -- Execution time: 30.611

UPDATE route_X SET geom=
        CastToMulti(
                RemoveRepeatedPoints(
                                    ST_Snap( 
                                            route_X.geom,
                                            (SELECT CastToMultipoint(st_collect(b.geom)) 
                                            FROM X as b
                                            WHERE b.id = route_X.id 
                                            GROUP BY b.id) , 0.001 
                                            ), 0.001 
                                    )
        )                    
WHERE EXISTS(
            SELECT 1 FROM X as b
            WHERE b.id = route_X.id limit 1
            );
--=== -- Execution time: 4.924

UPDATE route_X SET geom=
    CastToMulti(
                ST_Split(
                        route_X.geom,
                        (SELECT CastToMultiPoint(st_collect(b.geom)) 
                        FROM X as b
                        WHERE b.id = route_X.id 
                        GROUP BY b.id)
                        )
                )
WHERE EXISTS(
            SELECT 1 FROM X as b
            WHERE b.id = route_X.id limit 1
            );
--=== -- Execution time: 5.437

DROP TABLE IF EXISTS "route_X_elem";
--=== -- Execution time: 0.000

SELECT Elementarygeometries( 'route_X' ,
                             'geom' , 'route_X_elem' ,'out_pk_uid' , 'out_multi_id', 1 ) as num,
							 'lines splitted' as label;
-- ---+-----
-- num|label
-- ---+-----
-- 9943|lines splitted
--=== 1 row === -- Execution time: 0.094

INSERT INTO route_finale ("pk_uid", "id", "geom")
SELECT NULL,id, CastToMultiLineString("geom") AS geom
FROM route_X_elem;
--=== -- Execution time: 0.062

DROP TABLE IF EXISTS "route_split_finale";
--=== -- Execution time: 0.110

SELECT CreateClonedTable('MAIN', 'ebw_route_202103', 'route_split_finale', 1);
-- ----------------------------------------------------------------------
-- CreateClonedTable('MAIN', 'ebw_route_202103', 'route_split_finale', 1)
-- ----------------------------------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.015

INSERT INTO route_split_finale ("pk_uid","id", "note", "illuminazi", "nome", "minit_1014", "id_tratta", "lungh_calc", "altezza",
"cavi_tot", "tipo", "larghezza", "stato_costr", "ente", "minit_1012", "dett_ente", "proprietar",
"tvinfratel", "ebwtvinfr", "num_tubi", "lungh_mis", "ebw_utiliz", "posa_aerea", "categoria","geom")
SELECT NULL, c."id", a."note", a."illuminazi", a."nome", a."minit_1014", a."id_tratta", a."lungh_calc", a."altezza",
a."cavi_tot", a."tipo", a."larghezza", a."stato_costr", a."ente", a."minit_1012", a."dett_ente", a."proprietar",
a."tvinfratel", a."ebwtvinfr", a."num_tubi", a."lungh_mis", a."ebw_utiliz", a."posa_aerea", a."categoria", CastToMultiLineString(c."geom") AS geom
FROM ebw_route_202103 a, route_finale c
WHERE a.id = c.id;
--=== -- Execution time: 3.108

DROP TABLE IF EXISTS route_X_elem;
--=== -- Execution time: 0.015

DROP TABLE IF EXISTS route_X;
--=== -- Execution time: 0.016

DROP TABLE IF EXISTS route_finale;
--=== -- Execution time: 0.110

DROP TABLE IF EXISTS route_finaleT;
--=== -- Execution time: 0.124

DROP TABLE IF EXISTS route_finaleTall;
--=== -- Execution time: 0.110

SELECT UpdateLayerStatistics('route_split_finale');
-- -------------------------------------------
-- UpdateLayerStatistics('route_split_finale')
-- -------------------------------------------
-- 1
--=== 1 row === -- Execution time: 0.015

VACUUM
--=== -- Execution time: 5.562

--=========================================================================================
--==     SQL session end   =   2021-06-11 13:16:01   =   53 statements were executed
--=========================================================================================