[Gfoss] SQL Update con SQLite / spatialite

Local Chapters GFOSS.it
1

Salve a tutti,

sto facendo i primi passi con Spatialite, ed in generale con sqlite. Ho fatto gli esercizi presenti sul cookbook di Alessandro Furieri, ma mi sto impantanando alle prime variazioni su tema.

Per esser chiaro, ho scaricato dal sito dell'istat gli shapefile del censimento 2011, e li ho utilizzati per seguire la procedura del cookbook. Poichè il censimento 2011 è in corso, manca il campo "pop2011" nella table (come d'altronde negli shape del 2008 e 2010, forse solo per aggiornamento dei confini comunali e l'inserimento di nuove, utilissime provincie!!!).

Allora 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.

Ho provato con questo (metto solo il tentativo che mi pare più vicino ad una buona soluzione, tra i mille fatti):

UPDATE comuni2011
SET pop2001 = com2001_g.pop2001
WHERE comuni2011.nome_com=com2001_g.nome_com;

Mi restituisce il seguente errore:

SQL error: no such column: com2001_g.pop2001

Se invece uso:

UPDATE comuni2011
SET pop2001 = com2001_g.pop2001
FROM com2001_g
WHERE comuni2011.nome_com=com2001_g.nome_com;

mi dice:

SQLite error: near ".": syntax error

Premetto che ho caricato i dati istat come virtual shapefile.

Sbaglio qualcosa o ci sono delle limitazioni di sqlite?

Grazie,

Carlo

2

Grazie gabriele,

ho provato ma dopo 90 secondi Spatialite ha crashato …

3

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 :smiley:

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