[Gfoss] stored procedure e messaggio finale NULL

Totò Fiandaca pigrecoinfinito a gmail.com
Ven 11 Giu 2021 15:40:18 CEST


Buonasera a tuttə,
ho studiato le stored procedure di spatialite 5 [0] e ho un problema che
non riesco a risolvere da solo.

Lavoro con spatialite_gui 2.1.0 beta1 con spatialite 5.0.0 e sqlite 3.33.0

Ho creato un mio database usando la gui e importato una sola tabella
(vettore MultiLineString 32632),
su questa tabella faccio una serie di query, circa 53, tutte a cascata.

Ho usato le stored procedure, ovvero ho sostituito al posto del nome della
tabella (es:pippo) la variabile @toto@ e ho salvato l'intero script SQL in
un file, es: my_script_sql.sql

da spatialite_gui lancio la seguente query:

SELECT SqlProc_SetLogfile(
'C:\Users\pigre\Desktop\db_prova_route\logfile.txt', 1);
SELECT
SqlProc_Execute(SqlProc_FromFile('D:\Gitlab\civ_xxxxx_of\ my_script_sql.sql
'),'@toto@=pippo');

tutto procede bene, ma alla fine ottengo:

SqlProc_Execute(SqlProc_FromFile('D:\Gitlab\civ_xxxxx_of\ my_script_sql.sql
'),'@toto@=pippo')
--------
NULL

ma ottengo l'output corretto, cosa significa questo NULL? devo preoccuparmi?

allego il logfile.txt


[0] https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Stored+Procedures


-- 
*Ing. Salvatore Fiandaca*
*mobile*.:+39 327.493.8955
*m*: *pigrecoinfinito a gmail.com <pigrecoinfinito a gmail.com>*
*C.F*.: FNDSVT71E29Z103G
*P.IVA*: 06597870820
*membro QGIS Italia - http://qgis.it/ <http://qgis.it/>*
*socio GFOSS.it - *http://gfoss.it/
*blog:*
* https://pigrecoinfinito.com/ <https://pigrecoinfinito.com/> FB: Co-admin
- https://www.facebook.com/qgis.it/ <https://www.facebook.com/qgis.it/>**
<https://www.facebook.com/qgis.it/> *
*TW:  <http://goog_95411464>**https://twitter.com/totofiandaca
<https://twitter.com/totofiandaca>*

43°51'0.54"N  10°34'27.62"E - EPSG:4326

“Se la conoscenza deve essere aperta a tutti,
perchè mai limitarne l’accesso?”
R. Stallman

Questo documento, allegati inclusi, contiene informazioni di proprietà di
FIANDACA SALVATORE e deve essere utilizzato esclusivamente dal destinatario
in relazione alle finalità per le quali è stato ricevuto. E' vietata
qualsiasi forma di riproduzione o divulgazione senza l'esplicito consenso
di FIANDACA SALVATORE. Qualora fosse stato ricevuto per errore si prega di
informare tempestivamente il mittente e distruggere la copia in proprio
possesso.
-------------- parte successiva --------------
--=========================================================================================
--==     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
--=========================================================================================




Maggiori informazioni sulla lista Gfoss