[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