Archivio dei tag query

Eliminare i duplicati da un vettore

Lavorando sui dati di OSM mi sono trovato a dover affrontare un piccolo grande problema di sovrapposizione di vettori lineari.

Ho scaricato una grossa mole di dati relativi all’idrografia della Francia orientale usando il plugin QuickOSM e, siccome l’area era davvero vasta, l’ho dovuta suddividere in quadranti di 100km di lato. Solo in questo modo, sfruttando ogni singolo quadrante, ho potuto effettuare il download in maniera corretta e senza superare il tempo massimo di risposta del server.

Questa procedura ha però fatto nascere la problematica che mi ha portato a fare questo articolo ed il video tutorial che troverai in fondo. In pratica, ogni qualvolta scaricavo da un quadrante l’idrografia ed esso era attraversato da fiumi che a loro volta attraversavano altri quadranti, questi venivano caricati anche nel quadrante attivo in quel momenti e, visto che tutto confluiva in un unico vettore lineare presente in un GeoDB contenuto in PostGIS, mi sono ritrovato tantissimi duplicati; nello specifico 11.919! I duplicati oltre a rendere il vettore pesante lo rendevano affetto da errori topologici.

Mi sono accorto di questi duplicati perchè ho fatto una verifica topologica con il plugin Topology Checker impostando la verifica sui duplicati, così come visualizzato nell’immagine che segue.

duplicati

Effettuata la verifica il risultato è stato quello che vedi di seguito.

duplicati

Indagando la tabella attributi ho notato la presenza della colonna full_id e, guardando il feature ID delle geometrie duplicate presenti nel report di Topology Checker, ho notato che in quella colonna i duplicati erano facilmente individuabili perchè avevano lo stesso full_id.

duplicati

Istintivamente mi sono così concentrato su quella colonna e visto che volevo risolvere il tutto usando l’SQL mi sono documentato in giro sul web su come poter eliminare il problema.

Ho prima lanciato questa query:

SELECT
full_id,
COUNT(full_id) AS counter
FROM waterway_new
GROUP BY full_id
ORDER BY counter;

Individuando così un elenco di duplicati. Alcuni fiumi erano duplicati anche 7 volte!

Poi ho usato la query che segue per creare un nuovo vettore depurato dai duplicati:

CREATE TABLE waterway_new_noduplicate AS
SELECT DISTINCT ON (full_id) *
FROM waterway_new;

Tutta la procedura l’ho racchiusa nel video tutorial che trovi di seguito. Un’altra strada sarebbe potuta essere quella di usare il plugin MMQGIS senza quindi usare l’SQL.

 

Query avanzata: selezionare una parte di una stringa escludendo alcuni caratteri

Come si può selezionare una parte di una stringa escludendo alcuni caratteri?

Ad esempio se della parola “massimo” volessi selezionare solo “si” come posso farlo? E’ questo che mi sono chiesto oggi quando mi sono imbattuto in questa problematica durante una delle attività lavorative.

Ho fatto così un giro tra le stringhe di selezione del selettore di QGIS ed ho trovato quella che cercavo!

Il dato di partenza, in questo caso, è il vettore poligonale della Francia Europea.

query avanzata

Dalla tabella che segue mi interessa selezionare nella colonna “insee” solo quei poligoni il cui codice inizia per 51 e 10.

query avanzata

Ho attivato il selettore tramite espressione e cercando tra le espressioni presenti nella sezione “String” ho trovato quello che cercavo.

La query per raggiungere lo scopo è la seguente:

substr( "insee" , 1,2) is 51 or substr( "insee" , 1,2) is 10

query avanzata

in cui:

  • substr è l’operatore di selezione;
  • insee è il campo in cui l’operatore deve cercare;
  • i numeri 1 e 2 corrispondono al range di ricerca, in questo caso la ricerca deve essere fatta solo nelle prime due posizioni di ogni stringa;
  • i numeri 51 e 10 sono gli obiettivi della ricerca.

Nell’immagine precedente c’è il risultato della selezione, nell’immagine successiva c’è lo zoom all’area selezionata.

Approfondimento

Come molti sapranno, nei software GIS le query SQL sono facilitate. Dietro l’interfaccia grafica di un “Query builder” o un “Select by expression” si cela la sintassi base SQL, quel select X from Y where Z che chi è avvezzo all’uso dei DB conosce bene.

Grazie alle indicazioni di Salvatore Fiandaca, che trovate tra i commenti di questo post,  riporto di seguito la sintassi SQL completa applicabile in un qualunque DB:

select * from francia_europea
where
substr("insee",1,2) is '51' or substr("insee",1,2) is '10'

Queste tre righe sono così definite:

  • l’asterisco dopo select sta ad indicare che si vogliono selezionare tutti i campi della tabella francia_europea;
  • substr è lo stesso comando usato in QGIS.

Traducendo la query di prima si è detto al pc di selezionare(select) tutti i campi(*) della tabella francia_europea(from) in cui è verificata la condizione substr(where).

La query completa eseguita in SpatiaLite, per esempio, ci restituisce la sola selezione dei record tabellari.

Per creare una tabella dalla selezione bisogna anteporre una stringa a quanto scritto in precedenza:

create table area_lavoro as
select * from francia_europea
where
substr("insee",1,2) is '51' or substr("insee",1,2) is '10'

Questo però non è sufficiente a creare anche le geometrie, infatti una volta creata la tabella area_lavoro, bisogna eseguire una nuova query per ottenere le geometrie che possono quindi essere visualizzate in QGIS:

SELECT RecoverGeometryColumn('area_lavoro', 'geom',3857, 'MULTIPOLYGON', 'XY')


Spero che l’articolo possa tornare utile a qualcun altro oltre che a me 🙂

Ecco il video tutorial