Re: [Gfoss] Spatial Join con SpatiaLite

[stefano campus]

da impaginare per bene e stampare!
grazie mille, sandro, per le tue spiegazioni sempre complete e "definitive"

s.



--
Sent from: 
http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.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.
801 iscritti al 19/07/2017

Re: [Gfoss] Spatial Join con SpatiaLite

[pigreco]

Leggo sempre le risposte di Furieri e rimango sempre stupefatto perchè ci
regala sempre delle lezioni.
Mi sono permesso di mettere in 'bella' tutti gli script sql scrivendo un
articolo su questo thread.


https://pigrecoinfinito.wordpress.com/2017/11/15/ml-gfoss-it-una-lezione-su-spatialite/

Grazie 

saluti


-
https://pigrecoinfinito.wordpress.com/
--
Sent from: 
http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.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.
801 iscritti al 19/07/2017

Re: [Gfoss] Spatial Join con SpatiaLite

[Massimiliano Moraca]

Grazie mille delucidazioni Alessandro.

Buona giornata

Il giorno 15 novembre 2017 10:13,  ha scritto:

> On Wed, 15 Nov 2017 09:36:53 +0100, Massimiliano Moraca wrote:
>
>> Nel caso specifico del db ISTAT precedentemente avevo fatto un LEFT
>> JOIN tra le geometrie ed il csv dei dati statistici, dovrei quindi
>> riportare oltre alle colonne che hai indicato anche il resto:p1, p2,
>> ...st9 etc. E' lunghetto da scrivere ma se mi dici che non conviene
>> usare SELECT* allora la strada è una. Toglimi una curiosità,
>> premetto che ho conoscenze basilari di SQL e ne faccio un uso
>> discontinuo, il non usare SELECT* è dato da una limitazione di
>> SpatiaLite (essendo una LITE) o vale per ogni DMBS?
>>
>>
> ciao Massimiliano,
>
> direi proprio che e' una buona abitudine che vale per qualsiasi
> DBMS, per un motivo abbastanza semplice.
> quando tu scrivi "SELECT *" stai autorizzando il DBMS ad
> assegnare i nomi-colonna come meglio preferisce, ed a volte
> potrebbero nascere situazioni difficili da gestire.
>
> giusto qualche esempio a caso:
> - nel caso in cui la colonna 'pippo' sia presente sia nella
>   tavola 'a' che nella tavola 'b' potresti poi scoprire che
>   la tua View contiene una colonna di nome 'a.pippo' ed
>   un'altra di nome 'b.pippo'; questi sono nomi SQL rognosi,
>   che poi ti costringeranno ad usare il quoting tra doppie
>   virgolette per mascherare il nome illegale.
>   decisamente meglio evitare di finire in situazioni di questo
>   tipo.
> - versioni diverse del DBMS potrebbero usare criteri diversi
>   per assegnare automaticamente i nomi-colonna alle View; e
>   quindi tu potresti anche scoprire che la tua View funziona
>   bene solo su alcune macchine ma non su altre.
>
> insomma, e' essenzialmente un problema di autorita' e di controllo;
> gli utenti occasionali trovano comodo affidarsi alle decisioni
> automatiche del DBMS perche' (apparentemente) costa poca fatica.
> invece gli utenti professionali specificano sempre tutto in modo
> esplicito evitando accuratamente di affidarsi ai default
> automatici perche' non gradiscono avere sorprese inattese.
>
>
> Toglimi una curiosità. ST_DISTANCE se usato in solitaria mi permette
>> di creare un buffer visibile in QGIS
>>
>>
> no; e' semplicemente un calcolo che ritorna un valore Double Precision,
> non una geometria.
>
>
> potrei pure cavarmela con il virtual layer di QGIS ma se ne può
>> creare solo uno ed a me ne servono 4: 250m, 500m, 750m, 1000.
>>
>>
> guarda che con SpatiaLite puoi risolvere questo problema in modo
> molto semplice. basta solo che tu aggiunga altre quattro colonne
> geometriche alla tua tavola "input" e QGIS le vedra' poi come
> se fossero layers distinti. prova qualcosa del genere:
>
> SELECT AddGeometryColumn('input', 'buf250', 32632, 'POLYGON', 'XY');
> SELECT AddGeometryColumn('input', 'buf500', 32632, 'POLYGON', 'XY');
> SELECT AddGeometryColumn('input', 'buf750', 32632, 'POLYGON', 'XY');
> SELECT AddGeometryColumn('input', 'buf1000', 32632, 'POLYGON', 'XY');
>
> UPDATE input SET buf250 = ST_Buffer(geom, 250.0);
> UPDATE input SET buf500 = ST_Buffer(geom, 500.0);
> UPDATE input SET buf750 = ST_Buffer(geom, 750.0);
> UPDATE input SET buf1000 = ST_Buffer(geom, 1000.0);
>
> ciao Sandro
>
>
>
___
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.
801 iscritti al 19/07/2017

Re: [Gfoss] Spatial Join con SpatiaLite

[stefano campus]

da impaginare per bene e stampare!

grazie sandro: sempre preziso.

s.

--
Sent from: 
http://gfoss-geographic-free-and-open-source-software-italian-mailing.3056002.n2.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.
801 iscritti al 19/07/2017

Re: [Gfoss] Spatial Join con SpatiaLite

[a . furieri]

On Wed, 15 Nov 2017 09:36:53 +0100, Massimiliano Moraca wrote:

Nel caso specifico del db ISTAT precedentemente avevo fatto un LEFT
JOIN tra le geometrie ed il csv dei dati statistici, dovrei quindi
riportare oltre alle colonne che hai indicato anche il resto:p1, p2,
...st9 etc. E' lunghetto da scrivere ma se mi dici che non conviene
usare SELECT* allora la strada è una. Toglimi una curiosità,
premetto che ho conoscenze basilari di SQL e ne faccio un uso
discontinuo, il non usare SELECT* è dato da una limitazione di
SpatiaLite (essendo una LITE) o vale per ogni DMBS?



ciao Massimiliano,

direi proprio che e' una buona abitudine che vale per qualsiasi
DBMS, per un motivo abbastanza semplice.
quando tu scrivi "SELECT *" stai autorizzando il DBMS ad
assegnare i nomi-colonna come meglio preferisce, ed a volte
potrebbero nascere situazioni difficili da gestire.

giusto qualche esempio a caso:
- nel caso in cui la colonna 'pippo' sia presente sia nella
  tavola 'a' che nella tavola 'b' potresti poi scoprire che
  la tua View contiene una colonna di nome 'a.pippo' ed
  un'altra di nome 'b.pippo'; questi sono nomi SQL rognosi,
  che poi ti costringeranno ad usare il quoting tra doppie
  virgolette per mascherare il nome illegale.
  decisamente meglio evitare di finire in situazioni di questo
  tipo.
- versioni diverse del DBMS potrebbero usare criteri diversi
  per assegnare automaticamente i nomi-colonna alle View; e
  quindi tu potresti anche scoprire che la tua View funziona
  bene solo su alcune macchine ma non su altre.

insomma, e' essenzialmente un problema di autorita' e di controllo;
gli utenti occasionali trovano comodo affidarsi alle decisioni
automatiche del DBMS perche' (apparentemente) costa poca fatica.
invece gli utenti professionali specificano sempre tutto in modo
esplicito evitando accuratamente di affidarsi ai default
automatici perche' non gradiscono avere sorprese inattese.



Toglimi una curiosità. ST_DISTANCE se usato in solitaria mi permette
di creare un buffer visibile in QGIS



no; e' semplicemente un calcolo che ritorna un valore Double Precision,
non una geometria.



potrei pure cavarmela con il virtual layer di QGIS ma se ne può
creare solo uno ed a me ne servono 4: 250m, 500m, 750m, 1000.



guarda che con SpatiaLite puoi risolvere questo problema in modo
molto semplice. basta solo che tu aggiunga altre quattro colonne
geometriche alla tua tavola "input" e QGIS le vedra' poi come
se fossero layers distinti. prova qualcosa del genere:

SELECT AddGeometryColumn('input', 'buf250', 32632, 'POLYGON', 'XY');
SELECT AddGeometryColumn('input', 'buf500', 32632, 'POLYGON', 'XY');
SELECT AddGeometryColumn('input', 'buf750', 32632, 'POLYGON', 'XY');
SELECT AddGeometryColumn('input', 'buf1000', 32632, 'POLYGON', 'XY');

UPDATE input SET buf250 = ST_Buffer(geom, 250.0);
UPDATE input SET buf500 = ST_Buffer(geom, 500.0);
UPDATE input SET buf750 = ST_Buffer(geom, 750.0);
UPDATE input SET buf1000 = ST_Buffer(geom, 1000.0);

ciao Sandro


___
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.
801 iscritti al 19/07/2017

Re: [Gfoss] Spatial Join con SpatiaLite

[Massimiliano Moraca]

Ciao Alessandro, grazie per la risposta come prima cosa :)

- primo errore: per arrivare a creare una View non si deve mai
>   partire creando direttamente la View stessa, perche' in questo
>   modo se qualcosa va storto non capirai piu' nulla e non sarai
>   in grado di identificare e correggere i tuoi errori.
>   si parte sempre definendo (e testando accuratamente) lo
>   statement SELECT, che una volta messo opportunamente a punto
>   sara' poi facilissimo trasformare in una View.


Hai ragione, in realtà è un errore voluto/non voluto nel senso che prima di
creare una view o una table faccio sempre come indichi, ho voluto però
postare l'intera query per completezza.

- secondo errore: mai usare "SELECT *" in una View; tu pensi in
>   questo modo di risparmiare tempo e fatica, ma finirari invece
>   per perdere un sacco di tempo e per faticare un sacco rincorrendo
>   errori "misteriosi".
>   buona regola da usare _SEMPRE_ con le View: tutte le colonne
>   vanno identificate esplicitamente con il proprio nome qualificato
>   (specificando cioe' a quale tavola appartiene ciascuna colonna),
>   ed e' sempre opportuno specificare esplicitamente tutti gli
>   alias names di colonna per evitare poi spiacevoli sorprese al
>   momento dell'esecuzione.


Ok, questo non lo sapevo, considera che lo faccio sempre... :(
Nel caso specifico del db ISTAT precedentemente avevo fatto un LEFT JOIN
tra le geometrie ed il csv dei dati statistici, dovrei quindi riportare
oltre alle colonne che hai indicato anche il resto:p1, p2, ...st9 etc. E'
lunghetto da scrivere ma se mi dici che non conviene usare SELECT* allora
la strada è una. Toglimi una curiosità, premetto che ho conoscenze basilari
di SQL e ne faccio un uso discontinuo, il non usare SELECT* è dato da una
limitazione di SpatiaLite (essendo una LITE) o vale per ogni DMBS?

- terzo errore: (vedo che lo segnali tu stesso nella tua
>   mail successiva) ST_Contains() non e' l'operatore spaziale
>   che devi usare, perche' richiede che la prima geometria
>   copra interamente la seconda. Ma nel nostro caso e' molto
>   piu' verosimile che le Sezioni di Censimento ricadano solo
>   parzialmente all'interno del Buffer, ragion per cui e' molto
>   piu' opportuno usare la ST_Intersects()


Si, non ho capito perchè mi ero impuntato con ST_CONTAINS quando eseguendo
la stessa cosa in QGIS utilizzavo l'intersezionepurtroppo sono tornato
sano di mente quando già avevo inviato il messaggio :D

- quarto errore (veniale): quando usi un alias-name per le
>   tavole, cerca di usare una singola lettera, altrimenti poi
>   ti troverai a dovere gestire nomi completi esageratamente
>

  lunghi ed inutilmente complicati.


Questo è un altro errore voluto/non voluto. Volevo esplicitare sempre per
completezza chi era il target del join e chi il buffer ma in genere uso
sempre a, b, c, ect come alias.

Ora non posso provare le query che hai scritto ma spero di farlo in serata,
le ho lette e devo dire che sicuramente mi servirà velocizzare la selezione
visto che ho sia le geometrie che i dati censuari di tutta Italia in una
unica tabella :)

Toglimi una curiosità. ST_DISTANCE se usato in solitaria mi permette di
creare un buffer visibile in QGIS? (Appena posso proverò) Perchè usando
ST_BUFFER per creare la view a cui si aggiungono le geometrie con INSERT
INTO il vettore creato viene visto da QGIS come un punto e non un buffer.
Usando la stessa sintassi del buffer con un virtual layer invece ho la
visualizzazione del buffer. Mi serviva vedere il buffer per un purissimo
fatto visivo e non sono interessato ad inserirlo in nessun processo
ulteriore; potrei pure cavarmela con il virtual layer di QGIS ma se ne può
creare solo uno ed a me ne servono 4: 250m, 500m, 750m, 1000.
___
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.
801 iscritti al 19/07/2017

Re: [Gfoss] Spatial Join con SpatiaLite

[a . furieri]

On Sun, 12 Nov 2017 11:14:00 +0100, Massimiliano Moraca wrote:

Buongiorno e buona domenica a tutti.
Sto provando uno spatial join sfruttando un buffer a 250 m da un 
punto, ma

non riesco a capire perchè non funziona.

Come dato di base sto sfruttando le celle censuarie ISTAT che potete
scaricare da qui

,
le ho chiamate *test_celle*.  Poi c'è un punto che ha come attributo 
il

solo ID e di cui faccio(vorrei fare) un buffer a 250m.
Il tutto voglio farlo confluire in una view chiamata *spatial_join*.



ciao Massimiliano,

scusami per la risposta poco tempestiva ma nei giorni scorsi
ero impegnato su tutt'altri versanti.
ho provato a replicare il tuo esempio a partire dalle Sezioni
di Censimento ISTAT 2011 della Campania.
come punto di riferimento (visto che tu non hai fornito esempi)
ho usato la posizione della Cappella San Severo nel cuore del
centro storico di Napoli. piu' sotto troverai i miei commenti.



Inserisco il codice che ho usato:

CREATE VIEW spatial_join AS
SELECT *
FROM
test_celle AS target,
(SELECT ST_BUFFER(geom, 250) AS geom
FROM input) AS buffer
WHERE ST_CONTAINS (target.geom, buffer.geom)



- primo errore: per arrivare a creare una View non si deve mai
  partire creando direttamente la View stessa, perche' in questo
  modo se qualcosa va storto non capirai piu' nulla e non sarai
  in grado di identificare e correggere i tuoi errori.
  si parte sempre definendo (e testando accuratamente) lo
  statement SELECT, che una volta messo opportunamente a punto
  sara' poi facilissimo trasformare in una View.

- secondo errore: mai usare "SELECT *" in una View; tu pensi in
  questo modo di risparmiare tempo e fatica, ma finirari invece
  per perdere un sacco di tempo e per faticare un sacco rincorrendo
  errori "misteriosi".
  buona regola da usare _SEMPRE_ con le View: tutte le colonne
  vanno identificate esplicitamente con il proprio nome qualificato
  (specificando cioe' a quale tavola appartiene ciascuna colonna),
  ed e' sempre opportuno specificare esplicitamente tutti gli
  alias names di colonna per evitare poi spiacevoli sorprese al
  momento dell'esecuzione.

- terzo errore: (vedo che lo segnali tu stesso nella tua
  mail successiva) ST_Contains() non e' l'operatore spaziale
  che devi usare, perche' richiede che la prima geometria
  copra interamente la seconda. Ma nel nostro caso e' molto
  piu' verosimile che le Sezioni di Censimento ricadano solo
  parzialmente all'interno del Buffer, ragion per cui e' molto
  piu' opportuno usare la ST_Intersects()

- quarto errore (veniale): quando usi un alias-name per le
  tavole, cerca di usare una singola lettera, altrimenti poi
  ti troverai a dovere gestire nomi completi esageratamente
  lunghi ed inutilmente complicati.

ed ecco qua la prima versione della nostra SELECT riscritta
in modo piu' ragionevole:

SELECT t.pk_uid AS rowid, t.cod_reg AS cod_reg, t.cod_istat AS 
cod_istat,

   t.pro_com AS pro_com, t.sez2011 AS sez201, t.sez AS sez,
   t.loc2011 AS loc2011, t.cod_loc AS cod_loc, t.geometry AS geom
FROM test_celle AS t,
 (SELECT ST_Buffer(geom, 250) AS geom FROM input) AS b
WHERE ST_Intersects (t.geometry, b.geom) = 1;


questa prima query identifica 46 sezioni di censimento in
circa 1 secondo e 460 millisecondi; possiamo migliorare la
velocita' di esecuzione ?


SELECT t.pk_uid AS rowid, t.cod_reg AS cod_reg, t.cod_istat AS 
cod_istat,

   t.pro_com AS pro_com, t.sez2011 AS sez201, t.sez AS sez,
   t.loc2011 AS loc2011, t.cod_loc AS cod_loc, t.geometry AS geom
FROM test_celle AS t,
 (SELECT geom FROM input) AS b
WHERE ST_Distance (t.geometry, b.geom) <= 250;


basta evitare di usare la ST_Buffer + ST_Intersects, che nel
caso di un singolo punto rappresenta un'inutile complicazione
che impone un sacco di calcoli complessi di cui possiamo fare
tranquillamente a meno.
basta usare semplicemente la ST_Distance per ottenere il medesimo
risulato con tempi di esecuzione decisamente migliori.
questa seconda query impiega soli 600 milis, cioe' meno della meta'
del tempo richiesto dalla precedente.
comunque possiamo arrivare ad una ottimizzazione ancora piu' spinta.


SELECT CreateSpatialIndex('test_celle', 'geometry');

SELECT t.pk_uid AS rowid, t.cod_reg AS cod_reg, t.cod_istat AS 
cod_istat,

   t.pro_com AS pro_com, t.sez2011 AS sez201, t.sez AS sez,
   t.loc2011 AS loc2011, t.cod_loc AS cod_loc, t.geometry AS geom
FROM test_celle AS t,
 (SELECT geom FROM input) AS b
WHERE ST_Distance (t.geometry, b.geom) <= 250 AND t.ROWID IN
 (SELECT rowid FROM SpatialIndex
  WHERE f_table_name = 'test_celle' AND search_frame =
BuildCircleMbr(ST_X(b.geom), ST_Y(b.geom), 250));


per prima cosa andiamo a creare uno Spatial Index che supporti
le sezioni di censimento.
e poi riscriviamo la query precedente in modo tale che ora
prenda in considerazione lo Spatial Index.
ora siamo scesi ad un tempo di 

Re: [Gfoss] Spatial Join con SpatiaLite

[Massimiliano Moraca]

Forse ho trovato il primo errore: ST_CONTAINS al posto di ST_INTERSECTS.
Vi aggiorno...

Il giorno 12 novembre 2017 11:14, Massimiliano Moraca <
massimilianomor...@gmail.com> ha scritto:

> Buongiorno e buona domenica a tutti.
> Sto provando uno spatial join sfruttando un buffer a 250 m da un punto, ma
> non riesco a capire perchè non funziona.
>
> Come dato di base sto sfruttando le celle censuarie ISTAT che potete
> scaricare da qui
> ,
> le ho chiamate *test_celle*.  Poi c'è un punto che ha come attributo il
> solo ID e di cui faccio(vorrei fare) un buffer a 250m.
> Il tutto voglio farlo confluire in una view chiamata *spatial_join*.
> Inserisco il codice che ho usato:
>
>> CREATE VIEW spatial_join AS
>>
>> SELECT *
>>
>> FROM
>>
>> test_celle AS target,
>>
>> (SELECT ST_BUFFER(geom, 250) AS geom
>>
>> FROM input) AS buffer
>>
>> WHERE ST_CONTAINS (target.geom, buffer.geom)
>>
>>
> Per aggirare il problema volevo eseguire lo spatial_join in QGIS avendo
> preventivamente creato una view con il buffer a 250m. Usando questa
> sintassi in un virtual layer:
>
>> SELECT st_buffer(geometry, 250)
>>
>>  FROM input
>>
>> ottengo correttamente un buffer dinamico ma in SpatiaLite ottengo invece
> un vettore puntuale il cui punto non è nemmeno visibile in QGIS(quando lo
> carico in QGIS mi dice che è un vettore MULTIPOINT).
>
> Dove sbaglio?
>
> In allegato c'è uno screenshot; come vedete tutti i field delel celle
> censuarie sembrano correttamente importati ma se voglio visualizzare la
> view non ho risultati.
> 
>
> Massimiliano Moraca 
>
___
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.
801 iscritti al 19/07/2017

[Gfoss] Spatial Join con SpatiaLite

[Massimiliano Moraca]

Buongiorno e buona domenica a tutti.
Sto provando uno spatial join sfruttando un buffer a 250 m da un punto, ma
non riesco a capire perchè non funziona.

Come dato di base sto sfruttando le celle censuarie ISTAT che potete
scaricare da qui
,
le ho chiamate *test_celle*.  Poi c'è un punto che ha come attributo il
solo ID e di cui faccio(vorrei fare) un buffer a 250m.
Il tutto voglio farlo confluire in una view chiamata *spatial_join*.
Inserisco il codice che ho usato:

> CREATE VIEW spatial_join AS
>
> SELECT *
>
> FROM
>
> test_celle AS target,
>
> (SELECT ST_BUFFER(geom, 250) AS geom
>
> FROM input) AS buffer
>
> WHERE ST_CONTAINS (target.geom, buffer.geom)
>
>
Per aggirare il problema volevo eseguire lo spatial_join in QGIS avendo
preventivamente creato una view con il buffer a 250m. Usando questa
sintassi in un virtual layer:

> SELECT st_buffer(geometry, 250)
>
>  FROM input
>
> ottengo correttamente un buffer dinamico ma in SpatiaLite ottengo invece
un vettore puntuale il cui punto non è nemmeno visibile in QGIS(quando lo
carico in QGIS mi dice che è un vettore MULTIPOINT).

Dove sbaglio?

In allegato c'è uno screenshot; come vedete tutti i field delel celle
censuarie sembrano correttamente importati ma se voglio visualizzare la
view non ho risultati.


Massimiliano Moraca 
___
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.
801 iscritti al 19/07/2017