[Gfoss] Fwd: SQL Update con SQLite / spatialite

[a.furieri a lqt.it]

On Fri, 30 Sep 2011 10:39:53 +0200, Carlo Cormio wrote
> Salve a tutti,
> 
> sto facendo i primi passi con Spatialite, ed in generale con sqlite
> ... ho scaricato dal sito dell'istat gli shapefile del censimento 2011
> ... ho pensato che poteva essere utile inserire una colonna 
> "pop2001" nella mia tabella spaziale già popolata, ed infilarci i 
> dati di popolazione presenti nella table dello shapefile com2001_g.
> 

la SQL UPDATE non si presta per nulla bene a trasferire
i dati tra due tavole diverse.
UPDATE table SET column = value WHERE clause;
N.B.: formalmente è prevista un'unica tavola, non due :-D

potete naturalmente provare a girare attorno al problema 
con qualche sub-query nidificata, ma riuscirete solo ad
incartarvi in una sintassi assiro-babilonese che non 
funziona.

in questi casi aiuta sicuramente utilizzare un linguaggio
di alto livello che "piloti" opportunamente SQL: p.es.
C, ma va benissimo anche Python o cosa altro preferite.

in ogni caso, anche usando SQL 'puro' c'è una strada molto 
più semplice e liscia che vi consente di "miscelare" i dati 
provenienti da due tavole diverse.

io probabilmente ho utilizzato shp istat diversi da quelli 
di Carlo (ma quante versioni degli SHP esistono ?), ma il
succo è identico.
ho caricato sia lo shp 2011 che quello 2001 come vere
tavole (non come virtual shp): altrimenti su circa 9000
righe per tavole gira con lentezza mortale.

------------------------------------------------
CREATE INDEX idx_2011 ON com2011 (COD_REG, COD_PRO, PRO_COM);
CREATE INDEX idx_2001 ON com2001_s (COD_REG, COD_PRO, PRO_COM);

#1] ovviamente non abbiamo nulla che assomigli ad un Primary
  Key affidabile per entrambe le tavole; quindi ci affidiamo
  ai codici istat, e creiamo due indici per efficienza.

------------------------------------------------
CREATE TABLE comuni_2011 AS
SELECT a.PK_UID AS PK_UID, a.COD_REG AS COD_REG, 
  a.COD_PRO as COD_PRO, a.PRO_COM AS PRO_COM,
  a.NOME_COM AS NOME_COM, a.NOME_TED AS NOME_TED,
  b.POP2001 AS POP2001, a.Geometry AS Geometry
FROM com2011 AS a
LEFT JOIN com2001_s AS b ON (
  a.COD_REG = b.COD_REG AND a.COD_PRO = b.COD_PRO 
  AND a.PRO_COM = b.PRO_COM);

#2] ora creiamo una *terza* tavola (comuni_2011)
  dove registreremo il risultato del JOIN tra le
  altre due (com2011 e com2011_s)
  N.B.: LEFT JOIN, vedremo dopo perchè

---------------------------------------------------
SELECT RecoverGeometryColumn('comuni_2011', 
  'Geometry', 23032, 'MULTIPOLYGON', 'XY');

#3] infine 'aggiustiamo' la geometria, in modo
  tale che venga correttamente riconosciuta
  come tale da SpatiaLite

--------------------------------------------------
SELECT * FROM comuni_2011
WHERE POP2001 IS NULL:

#4] come potete verificare, circa 240 comuni
  presentano POP2001 come NULL.
  facile da spiegarsi: sono tutti i comuni che
  per svariati motivi hanno cambiato codifica
  Istat tra il 2001 ed il 2011 (nuove province,
  comuni della Valmarecchia trasferiti da Pesaro
  a Rimini, qualche fusione, qualche scissione).

ed ecco spiegato perchè abbiamo utilizzato una
LEFT JOIN, anzichè una JOIN 'liscia'

ciao Sandro