[Gfoss] Aggregare operazioni con SQL

[a.furieri a lqt.it]

On Tue, 24 Oct 2017 14:53:44 +0200, a.furieri a lqt.it wrote:
> vedi l'esempio allegato (che si basa sul tuo DB campione).
>

sorry, pare che la mailing lista non accetta neppure 3KB
di allegato.

copio lo SQL script a seguire nel corpo della mail
S

--------------------------------------------------------
--
-- abbiamo appena creato un nuovo DB "MAIN", che e' ancora 
completamente vuoto.
-- ora "attacchiamo" al "MAIN" il DB "vecchio" che contiene i dati da 
importare
--
ATTACH DATABASE './campania_test.sqlite' AS vecchio;

--
-- ora utilizziamo la funzione CreateClonedTable per creare le tavole
-- nel "MAIN" ricopiando tal quali le definizioni corrispondenti
-- dichiarate nel DB "vecchio".
-- NOTA: cosi' siamo sicuri che verrano riprodotte fedelmente le
-- definizione dalla Primary Key, degli Spatial Index etc
--
SELECT CreateClonedTable('vecchio', 'comuni', 'comuni', 1);
SELECT CreateClonedTable('vecchio', '2_rete_ferroviaria', 
'2_rete_ferroviaria', 1);
SELECT CreateClonedTable('vecchio', 'complessi_idrogeologici', 
'complessi_idrogeologici', 1);

--
-- ora andiamo a copiare il solo Comune di Napoli dal "vecchio" al 
"MAIN"
--
INSERT INTO main.comuni (id, pk2_28, campan2_id, comune, pr, geom)
SELECT id, pk2_28, campan2_id, comune, pr, geom
FROM vecchio.comuni
WHERE comune = 'Napoli';

--
-- ora copiamo gli elementi ferroviari
-- in questo caso dovremo calcolare l'intersezione tra le
-- geometrie da importare ed il Buffer basato sul Comune.
-- in questo caso NON utilizzeremo lo Spatial Index.
--
-- nota: occorre un cast esplicitio al tipo MultiLinestring,
-- perche' le intersezioni saranno facilmente Linestring
-- elementari.
--
-- nota #2: battezzare una tavola con un nome che inizia con
-- una cifra e' assolutamente indecente :-PACKAGE
-- vedi tutta la marea di double quoting che implica per
-- rendere legale il nome secondo la sintassi SQL
--
INSERT INTO main."2_rete_ferroviaria" (id, pk2_72, tipo, opzioni, 
descrizion, geom)
SELECT v.id, v.pk2_72, v.tipo, v.opzioni, v.descrizion,
       CastToMultiLinestring(ST_Intersection(v.geom, n.geom))
FROM vecchio."2_rete_ferroviaria" AS v,
(SELECT ST_Buffer(geom, 1000.0) AS geom FROM main.comuni) AS n
WHERE ST_Intersects(v.geom, n.geom) = 1;

--
-- ora infine copiamo i complessi idrogeologici
-- piu' o meno e' come il caso precedente, ma questa volta
-- utilizziamo anche lo Spatial Index.
--
-- note: dobbiamo fare un cast al tipo MultiPolygon perche'
-- spesso le intertsezioni saranno Poligoni semplici.
--
INSERT INTO main.complessi_idrogeologici (id, pk2_26, stringa, area, 
perimeter,
     id_tipo_co, tipo_compl, id_tipo_fo, id_tipo_pe, id_grado_p, note, 
geom)
SELECT v.id, v.pk2_26, v.stringa, v.area, v.perimeter, v.id_tipo_co, 
v.tipo_compl,
     v.id_tipo_fo, v.id_tipo_pe, v.id_grado_p, v.note,
	CastToMultiPolygon(ST_Intersection(v.geom, n.geom))
FROM vecchio.complessi_idrogeologici AS v,
(SELECT ST_Buffer(geom, 1000.0) AS geom FROM main.comuni) AS n
WHERE ST_Intersects(v.geom, n.geom) = 1 AND v.ROWID IN (
     SELECT rowid FROM SpatialIndex
     WHERE f_table_name = 'DB=vecchio.complessi_idrogeologici'
           AND search_frame = n.geom);