Gfoss -- Geographic Free and Open Source Software - Italian mailing list

Fwd: SQL Update con SQLite / spatialite

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

7 messages Options

Carlo Cormio

Fwd: SQL Update con SQLite / spatialite

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

_______________________________________________
Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
[hidden email]
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
Non inviate messaggi commerciali.
I messaggi di questa lista non rispecchiano necessariamente
le posizioni dell'Associazione GFOSS.it.
527 iscritti al 7.7.2011

gabriele

Re: Fwd: SQL Update con SQLite / spatialite

> 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
>
> _______________________________________________
> Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
> [hidden email]
> http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
> Questa e' una lista di discussione pubblica aperta a tutti.
> Non inviate messaggi commerciali.
> I messaggi di questa lista non rispecchiano necessariamente
> le posizioni dell'Associazione GFOSS.it.
> 527 iscritti al 7.7.2011

Visto che la mail precedente l'ho inviata solo a Carlo, riposto la
risposta in lista...

Forse prendo un granchio, ma secondo me la sintassi giusta è la seguente:

UPDATE comuni2011
SET pop2001 = (select com2001_g.pop2001
FROM com2001_g
WHERE comuni2011.nome_com=com2001_g.nome_com);

Ciao,
Gabriele

_______________________________________________
Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
[hidden email]
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
Non inviate messaggi commerciali.
I messaggi di questa lista non rispecchiano necessariamente
le posizioni dell'Associazione GFOSS.it.
527 iscritti al 7.7.2011

Carlo Cormio

Re: Fwd: SQL Update con SQLite / spatialite

In reply to this post by Carlo Cormio

Grazie gabriele,

ho provato ma dopo 90 secondi Spatialite ha crashato ...

Il 30/09/2011 10:48, Gabriele Matteelli ha scritto:

Il 30/09/2011 10:39, Carlo Cormio ha scritto:

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

_______________________________________________
Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
[hidden email]
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
Non inviate messaggi commerciali.
I messaggi di questa lista non rispecchiano necessariamente
le posizioni dell'Associazione GFOSS.it.
527 iscritti al 7.7.2011

Forse prendo un granchio ma secondo me la sintassi giusta è la seguente:

UPDATE comuni2011
SET pop2001 = (select com2001_g.pop2001
FROM com2001_g
WHERE comuni2011.nome_com=com2001_g.nome_com);

Ciao,
Gabriele

_______________________________________________
Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
[hidden email]
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
Non inviate messaggi commerciali.
I messaggi di questa lista non rispecchiano necessariamente
le posizioni dell'Associazione GFOSS.it.
527 iscritti al 7.7.2011

Andrea Peri

Re: Fwd: SQL Update con SQLite / spatialite

In reply to this post by Carlo Cormio

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

questa sintassi a me pare errata.
In quale DBMS sarebbe supportata ?

--
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------

_______________________________________________
Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
[hidden email]
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
Non inviate messaggi commerciali.
I messaggi di questa lista non rispecchiano necessariamente
le posizioni dell'Associazione GFOSS.it.
527 iscritti al 7.7.2011

a.furieri

Re: Fwd: SQL Update con SQLite / spatialite

In reply to this post by Carlo Cormio

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

_______________________________________________
Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
[hidden email]
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
Non inviate messaggi commerciali.
I messaggi di questa lista non rispecchiano necessariamente
le posizioni dell'Associazione GFOSS.it.
527 iscritti al 7.7.2011

Carlo Cormio

Re: Fwd: SQL Update con SQLite / spatialite

In reply to this post by Carlo Cormio

Ciao,

@ Andrea:>UPDATE comuni2011

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

questa sintassi a me pare errata.
In quale DBMS sarebbe supportata ?

pare che in postgreSQL questa sintassi funzioni, secondo quanto
suggerito da un amico, ma non usandolo non l'ho testato.

@Alessandro:

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);

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);

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

ovviamente ha funzionato :) , immaginavo ci volesse una join,
chiarissimo il perchè della left join.

Grazie mille!!!

Carlo

_______________________________________________
Iscriviti all'associazione GFOSS.it: http://www.gfoss.it/drupal/iscrizione
[hidden email]
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e' una lista di discussione pubblica aperta a tutti.
Non inviate messaggi commerciali.
I messaggi di questa lista non rispecchiano necessariamente
le posizioni dell'Associazione GFOSS.it.
527 iscritti al 7.7.2011

Leonardodavinci

Re: Fwd: SQL Update con SQLite / spatialite

In reply to this post by Carlo Cormio

Salve anche io ho un problema con update in sqlite e penso proprio di sintassi!!!
in Mysql avrei scritto così quello che voglio fare:

UPDATE
TabAgeFattProdutt
INNER JOIN
TabTestaFattAG
ON
TabAgeFattProdutt.NumFattAG = TabTestaFattAG.NumFatt
SET
TabAgeFattProdutt.Stato ='I',
TabTestaFattAG.StatoFatt = 'Chiusa'
WHERE
TabTestaFattAG.NumFatt = '23/12'

con sqlite sto provando così ma nulla mi da errore : Near "," syntax error

UPDATE
TabAgeFattProdutt , TabTestaFattAG
SET
TabAgeFattProdutt.Stato ='I',
TabTestaFattAG.StatoFatt = 'Chiusa'
WHERE
TabAgeFattProdutt.NumFattAG = '23/12' ,
TabTestaFattAG.NumFatt= '23/12'

sono molto graditi suggerimenti grazie
Alessandro