[Gfoss] Spatialite creare tabella con join ed escludere colonne nulle

Local Chapters GFOSS.it
1

Rieccomi qua, ho letto un po' di cose sul web ma non trovo la soluzione

Ho scaricato l'interessante tool spatialite_gui e vi dico la verità è la
prima volta che lo uso. Però sembra molto intuitivo. Ora vi spiego cosa
vorrei fare.

In Spatialite_gui ho caricato due file:
1. layer shape A (quindi contiene informazioni geometriche)
2. dbf B (molto vasto)

Devo creare una nuova tabella che comprenderà tutte le colonne sia di A che
di B, facendo il LEFT JOIN attraverso la colonna comune IdCatasto.
Se oltre a fare ciò dovrei fare in modo di non includere le colonne di B che
sono interamente nulle, come faccio? Mi sa che mi sono perso nei meandri dei
codici SQL.. :stuck_out_tongue:

Avevo, nel frattempo, provato a salvare i risultati ottenuti con un comando
del genere

Select *
from A As F1
Left Join B As F2 On (F1.ID_CATASTO = F2.A0_Id_Catasto);

evidenziando tutta la tabella creata e salvandola come shape, ma quando vado
ad aprire gli attributi della tabella mancano molte informazioni che prima
c'erano! Molti campi sono diventati vuoti. Come mai?

Grazie!

--
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-creare-tabella-con-join-ed-escludere-colonne-nulle-tp7587522.html
Sent from the Gfoss -- Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.

2

On Fri, 28 Mar 2014 05:54:53 -0700 (PDT), NicoPez wrote:

Rieccomi qua, ho letto un po' di cose sul web ma non trovo la soluzione

Ho scaricato l'interessante tool spatialite_gui e vi dico la verità è la
prima volta che lo uso. Però sembra molto intuitivo. Ora vi spiego cosa
vorrei fare.

In Spatialite_gui ho caricato due file:
1. layer shape A (quindi contiene informazioni geometriche)
2. dbf B (molto vasto)

Devo creare una nuova tabella che comprenderà tutte le colonne sia di A che
di B, facendo il LEFT JOIN attraverso la colonna comune IdCatasto.
Se oltre a fare ciò dovrei fare in modo di non includere le colonne di B che
sono interamente nulle, come faccio? Mi sa che mi sono perso nei meandri dei
codici SQL.. :stuck_out_tongue:

scusa, me perche' hai deciso di usare proprio LEFT JOIN invece di JOIN ?
hai qualche buon motivo e lo hai fatto a ragion veduta ?

giusto come richiamo:
- JOIN: include nel resultset *solo* le righe che esistono su entrambe
   le tavole, sia in quella "lato sinistro" che in quella "lato destro"
   (per capirsi meglio: nel tuo caso A e' "sinisto", e B e' "destro").
- LEFT JOIN: include nel resulset *anche* tutte le righe che esistono
   solo "lato sinistro" e che non esistono affatto "lato destro";
   ovviamente in questo caso le colonne che corrispondono a "lato destro"
   rimarrano tutte con valori NULL, visto che non esistono affatto.

venendo al tuo caso: pare evidente che su "layer shape A" ci sono
tantissime righe che dichiarano un codice ID_CATASTO che non esiste
affatto in "dbf B"
ed e' proprio da quelle che nascono tutti i NULL di cui ti lamenti

attenzione: a volte il codice c'e', ma magari e' mal formattato.
p.es. e' sporcato da spazi in testa o in coda, da una parte e'
in tutte maiuscole mentre dall'altra parte usa le maiuscole etc.
verifica bene se questo e' il tuo caso, non dovrebbe essere
troppo difficile.

Avevo, nel frattempo, provato a salvare i risultati ottenuti con un comando
del genere

Select *
from A As F1
Left Join B As F2 On (F1.ID_CATASTO = F2.A0_Id_Catasto);

prova piuttosto:

Select *
from A As F1
Join B As F2 On (F1.ID_CATASTO = F2.A0_Id_Catasto);

mancano molte informazioni che prima c'erano!
Molti campi sono diventati vuoti. Come mai?

verifica meglio; non puo' essere cosi', perche SQL non fa sparire le
informazioni (quando esistono).
piuttosto, se hai dei codici ID_CATASTO che per qualsiasi motivo non
riescono a trovare un match corrispondente, allora ti rimarrano
naturalmente dei valori NULL. ma non e' che "spariscono".
e' piuttosto che "non esistono proprio" :wink:

e se vuoi identificare meglio quali sono le righe che non vanno in match:

SLELECT *
FROM A AS f1
LEFT JOIN B AS f2 ON (fI.id_catasto = f2.a0_id_catasto)
WHERE f2.a0_id_catasto IS NULL;

ciao Sandro

3

Ciao Sandro, grazie della risposta. :slight_smile:

Il Left Join è proprio mirato. Ho bisogno di non perdere nessuna delle righe
del layer shape A anche se queste non ci sono in dbf B. Ovviamente quando
farò questo join avrò delle righe nulle dove non c'è lo stesso IdCatasto in
entrambi. Questo era preventivato.

Però facendo il join (che mi va quindi ad includere tutte le tabelle del dbf
B) potrei avere che alcune colonne rimangano completamente nulle. Questo
perché in tutte le righe del layer A potrebbero non avere le informazioni in
una certa colonna del dbf di B. Forse è meglio fare un esempio :slight_smile:

/Layer Shape A/
AField1 AField2 IDCatasto Geometry
1 326 100326 BLOB sz=308 GEOMETRY
1 80 10080 BLOB sz=148 GEOMETRY
1 327 100327 BLOB sz=468 GEOMETRY
1 328 100328 BLOB sz=148 GEOMETRY

/Dbf B/
IDCatasto Field1 Field2 Field3
100326 NULL 5 7
10080 NULL 15 NULL
100327 NULL 35 55
100380 2580 2 NULL

Facendo il LEFT JOIN avrò
AField1 AField2 IDCatasto Geometry BField1
BField2 BField3
1 326 100326 BLOB sz=308 GEOMETRY NULL 5 7
1 80 10080 BLOB sz=148 GEOMETRY NULL 15 NULL
1 327 100327 BLOB sz=468 GEOMETRY NULL 35 55
1 328 100328 BLOB sz=148 GEOMETRY NULL NULL NULL

Questo è il risultato che voglio, ma escludendo la colonna BField1 visto che
ha tutti campi nulli.
Ora, qui è semplice. Ma se ho 500 colonne e ne devo eliminare 123, per
esempio, non è che posso andarmene a spulciare una per una. C'è un qualche
modo per farlo?

Grazie :slight_smile:

--
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-creare-tabella-con-join-ed-escludere-colonne-nulle-tp7587522p7587528.html
Sent from the Gfoss -- Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.

4

On Fri, 28 Mar 2014 11:51:23 -0700 (PDT), NicoPez wrote:

Questo è il risultato che voglio, ma escludendo la colonna BField1 visto che
ha tutti campi nulli.
Ora, qui è semplice. Ma se ho 500 colonne e ne devo eliminare 123, per
esempio, non è che posso andarmene a spulciare una per una. C'è un qualche
modo per farlo?

guarda che SQL funziona tutto nell'altro verso :wink:

cioe' ti filta le *righe* in base ai criteri che passi p.es. nella
clausola WHERE, ma si aspetta sempre che tu dichiari in anticipo quali
sono le *colonne* che ti interessano.

non esiste nessun meccanismo SQL per "scartare" a posteriori una
colonna in base alle sue caratteristiche (p.es. contenere solo NULL);
sei sempre tu che devi specificare a priori quali sono le colonne
che ti interessa vedere nel resultset.

insomma; se hai 500 colonne e ne devi eliminare 123, devi proprio
andare a spulciartele pazientemente una per una :wink:

ciao Sandro

5

Il 28/03/2014 19:51, NicoPez ha scritto:

Ciao Sandro, grazie della risposta. :slight_smile:

Il Left Join è proprio mirato. Ho bisogno di non perdere nessuna delle righe
del layer shape A anche se queste non ci sono in dbf B. Ovviamente quando
farò questo join avrò delle righe nulle dove non c'è lo stesso IdCatasto in
entrambi. Questo era preventivato.

Però facendo il join (che mi va quindi ad includere tutte le tabelle del dbf
B) potrei avere che alcune colonne rimangano completamente nulle. Questo
perché in tutte le righe del layer A potrebbero non avere le informazioni in
una certa colonna del dbf di B. Forse è meglio fare un esempio :slight_smile:

/Layer Shape A/
AField1 AField2 IDCatasto Geometry
1 326 100326 BLOB sz=308 GEOMETRY
1 80 10080 BLOB sz=148 GEOMETRY
1 327 100327 BLOB sz=468 GEOMETRY
1 328 100328 BLOB sz=148 GEOMETRY

/Dbf B/
IDCatasto Field1 Field2 Field3
100326 NULL 5 7
10080 NULL 15 NULL
100327 NULL 35 55
100380 2580 2 NULL

Facendo il LEFT JOIN avrò
AField1 AField2 IDCatasto Geometry BField1
BField2 BField3
1 326 100326 BLOB sz=308 GEOMETRY NULL 5 7
1 80 10080 BLOB sz=148 GEOMETRY NULL 15 NULL
1 327 100327 BLOB sz=468 GEOMETRY NULL 35 55
1 328 100328 BLOB sz=148 GEOMETRY NULL NULL NULL

Questo è il risultato che voglio, ma escludendo la colonna BField1 visto che
ha tutti campi nulli.
Ora, qui è semplice. Ma se ho 500 colonne e ne devo eliminare 123, per
esempio, non è che posso andarmene a spulciare una per una. C'è un qualche
modo per farlo?

Dopo aver fatto il LEFT JOIN, se intendi eliminare la colonna BField1,
visto che nella stragrande maggioranza dei record presenta valori NULL,
potresti seguire l'esempio contenuto qui:
http://sqlite.org/faq.html#q11
In alternativa, quando effettui il join, ti puoi escludere a priori il
campo indesiderato, selezionando solo i campi utili dal dbf. Ad esempio,
ti crei prima una vista sul dbf:

CREATE VIEW C
AS
     SELECT IDCatasto, Field2, Field3 -- NB: non seleziono Field1
     FROM B;

e poi esegui il LEFT JOIN usando la vista C:

SELECT *
FROM A As F1
LEFT JOIN C AS F2
ON (F1.ID_CATASTO = F2.A0_Id_Catasto);

ciao
Antonio

--
Antonio Falciano
http://www.linkedin.com/in/antoniofalciano

6

ahaha :smiley:

Si si.. era per sapere se si potesse fare qualcosa anche con le colonne :slight_smile:

Allora troverò un altro modo.. grazie!

--
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-creare-tabella-con-join-ed-escludere-colonne-nulle-tp7587522p7587531.html
Sent from the Gfoss -- Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.

7

Fossi in te farei creare a python la query in base alle tue necessità, così avresti la tabella solo con i campi “riempiti”.

···

Il giorno 28 marzo 2014 20:36, NicoPez <nico.pezzotta@gmail.com> ha scritto:

ahaha :smiley:

Si si… era per sapere se si potesse fare qualcosa anche con le colonne :slight_smile:

Allora troverò un altro modo… grazie!

–
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-creare-tabella-con-join-ed-escludere-colonne-nulle-tp7587522p7587531.html

Sent from the Gfoss – Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.

Gfoss@lists.gfoss.it
http://lists.gfoss.it/cgi-bin/mailman/listinfo/gfoss
Questa e’ una lista di discussione pubblica aperta a tutti.
I messaggi di questa lista non hanno relazione diretta con le posizioni dell’Associazione GFOSS.it.
666 iscritti al 22.7.2013

8

Il 29/03/2014 21:26, Luca Mandolesi ha scritto:

Fossi in te farei creare a python la query in base alle tue necessità,
così avresti la tabella solo con i campi "riempiti".

Sono d'accordo. Viste le note limitazioni di SQLite (dal creare una
vista sul pragma table_info(...) al droppare una colonna), conviene
affrontare il problema programmaticamente. Ad esempio, considerando solo
i campi con almeno il 25% di valori non NULL (a meno di sviste domenicali):

import sqlite3
conn = sqlite3.connect('test.sqlite')
c = conn.cursor()

# creo un vettore dei nomi delle colonne
c.execute("PRAGMA table_info(foo);")
fieldNames=
for row in c.fetchall():
     fieldNames.append(row[1])
fieldNames.remove('PK_UID')
#fieldNames.remove('Geometry')

# conto tutti i record e i record non NULL per ogni colonna
query = "SELECT count(*)"
for fieldName in fieldNames:
     query += ", count(" + fieldName + ")"
query += " FROM foo;"
c.execute(query)
counts = c.fetchall()[0]

# soglia di valori non NULL
soglia = 0.25
query = "SELECT PK_UID"
for i in range(len(fieldNames)):
     if counts[i+1] > soglia * counts[0]:
         query += ", " + fieldNames[i]
query += " FROM foo;"
c.execute(query)
print c.fetchall()

# ecc. ecc.

ciao
Antonio

--
Antonio Falciano
http://www.linkedin.com/in/antoniofalciano

9

Grazie Antonio.. appena mi studio un po' di programmazione mi ci metto su. :slight_smile:

--
View this message in context: http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.nabble.com/Spatialite-creare-tabella-con-join-ed-escludere-colonne-nulle-tp7587522p7587588.html
Sent from the Gfoss -- Geographic Free and Open Source Software - Italian mailing list mailing list archive at Nabble.com.