postgresql

array_to_string in Postgresql ovvero il problema della “battaglia navale”

Premetto come per ogni post su questo blog che quanto scrivo è frutto di tentativi e ricerche per risolvere un problema pratico che di volta in volta mi si presenta. Non è detto quindi che sia il modo migliore ed il più elegante; ogni feedback e/o consiglio è pertanto graditissimo.

Parto dall’inizio: dispongo di un grafo stradale (stradario comunale) archiviato in db Postgresql con estensione spaziale Postgis. La tabella contenente gli archi stradali si chiama “grafo_new”; in essa ogni arco di strada è spezzato all’intersezione con altri tratti. Questo comporta che una strada denomimata “Via Roma” sia composta da tanti archi. Inoltre il db contiene una griglia regolare di passo quadrato che identifica i quadranti del territorio comunale. Il problema da risolvere è: “Come faccio ad ottenere un elenco delle strade (in ordine alfabetico) che indichi anche il quadrante o i quadrantiall’interno dei quali la strada ricade”. Mi ricorda moltissimo il funzionamento della battaglia navale quando per colpire una nave avversaria di indicavano le coordinate: A7: acqua; B5: colpito; B6: colpito e affondato.

Un po’ come fa l’ottimo servizio maposmatic (http://www.maposmatic.org)

Ecco i passaggi che ho seguito:

1. creazione di una view in postgres che aggrega tutti i tratti di via con lo stesso nome:

#= CREATE VIEW view_grafo_union AS SELECT nome, ele_desc, ele_tipo, gid, ST_Multi(ST_Union(f.the_geom)) AS the_geom FROM grafo_new AS f GROUP BY nome ORDER BY nome;

2.creazione di una seconda view di intersezione spaziale tra il grafo e la griglia di quadranti:

#= CREATE VIEW view_grafo_union_quadrante AS SELECT ST_Intersection(r.the-geom, m.the_geom) AS intersection_geom, m.codice, r.nome FROM view_grafo_union AS r, quadrante_stradario AS m WHERE ST_Intersects(r.the_geom, m.the_geom);

3. Creazione di una query “array_to_string” che  fornisca l’elenco di tutti i quadranti di instersezione per ogni tratto di strada:

#= SELECT DISTINCT a.nome, array_to_string(array(SELECT codice FROM view_grafo_union_quadrante AS b WHERE b.nome = a.nome),’,’) FROM view_grafo_union_quadrante AS a ORDER BY nome;

Per salvare il risultato dell’ultima query su un file basta impartire prima della query il seguente comando:

#= \o /percorso/al/file/di/output.csv

Il CSV potrà poi essere aperto e gestito con Calc  o altro editor di testo.

To be continued con snapshot e altri dettagli….

 

PostreSQL | trigger

Un trigger definisce una funzione che agisce prima o dopo un’altra azione su una tabella.

In sostanza un trigger richiama una determinata funzione (che deve essere creata a priori) che esegue operazioni sui valori di una tabella.

Nel caso particolare di questo esempio disponiamo di una tabella chiamata “valori” che contiene 5 colonne:

valore1, valore2, valore3, valore4 e somma (tutti di tipo integer).

I valori delle prime 4 colonne vengono inserite dall’utente; vogliamo fare in modo che la quinta colonna (somma) venga popolata dalla funzione richiamata dal trigger una volta che i primi 4 valori sono inseriti. Procediamo:

– definiamo la funzione:

nome_db=> CREATE OR REPLACE FUNCTION somma_valori() RETURNS trigger AS $somma_valori$ DECLARE new_somma integer;
BEGIN IF (TG_OP=’INSERT’) THEN UPDATE valori SET somma=valore1+valore2+valore3+valore4; END IF; RETURN NEW; END; $somma_valori$ language plpgsql;

– definiamo ora il trigger che richiama la funzione ad ogni INSERT che avviene nella tabella;

nome_db=> CREATE TRIGGER tr_somma_valori AFTER INSERT ON valori FOR EACH ROW EXECUTE PROCEDURE somma_valori();

In questo modo ogni volta che vengono inseriti i primi 4 valori vedremo popolarsi automagicamente anche il quinto.

Postgresql | replace

Ho importato un file CSV in Postgresql in cui i testi erano racchiusi tra doppio apice (“).

Il risultato si traduce in campi (quelli testuali) tutti belli incapsulati in doppie apici che, a dirla tutta, non sono il massimo…soprattuto se si devono fare SELECT o UPDATE via psql (client a riga di comando comodissimo e potente). Ho tentato allora di approfondire cercando una soluzione (in Postgresql) che permettesse di fare un bel FIND AND REPLACE (come siamo soliti fare con openoffice/libreoffice e/o qualsiasi altro editor di testo).

Alla fine ho scovato la funzione REPLACE di Postgresql che ha la seguente sintassi:

replace(string text, from text,to text)

che riportata in concreto diventa:

nome_db =# UPDATE nome_tabella SET nome_campo=replace(nome_campo, ‘valore_vecchio’,’valore_nuovo’);

Vediamo nel caso pratico del mio db. Si tratta di una tabella contente dati relativi a ditte. Facendo una SELECT sul campo “denominazione” prima della modifica con REPLACE si otteneva:

nome_db =# SELECT denominazione FROM ditte WHERE numero=2;

denominazione
—————–
“NOME DITTA A CASO s.n.c.”
(1 riga)

Aggiorniamo il campo denominazione togliendo i doppi apici:

nome_db =# UPDATE ditte SET denominazione=replace(denominazione,'”‘,”);

e alla fine la query di SELECT precedente torna il valore “pulito”:

nome_db =# SELECT denominazione FROM ditte WHERE numero=2;

denominazione
—————–
NOME DITTA A CASO s.n.c.
(1 riga)

Altro esempio: supponiamo di avere un campo che memorizza il percorso di salvataggio di una immagine. Il campo si chiama “foto” e dobiamo aggiornare l’indirizzo IP del server sul quale risiedono le immagini: si passa da server con IP “100.0.4.231” a IP”192.168.4.50″.
Se interroghiamo il DB prima dell’aggiornamento otteniamo:

nome_db =# SELECT foto FROM ditte WHERE numero=2;

foto
—————–
http://100.0.4.231/percorso_al_file_immagine/002.jpg
(1 riga)

Modifichiamo allora solo la parte di campo relativa all’indirizzo IP:

nome_db =# UPDATE ditte SET foto=replace(foto,’100.0.4.231,’192.168.4.50′) WHERE numero=;

UPDATE 1

ed il risultato della SELECT sarà:

nome_db =# SELECT foto FROM ditte WHERE numero=2;

foto
—————–
http://192.168.4.50/percorso_al_file_immagine/002.jpg
(1 riga)

script per backup periodici di database postgresql/postgis

Una delle cose belle di Linux e’ che ti spinge a curiosare nel suo “mondo”; si scoprono cose davvero fantastiche per quanto semplici.

Per esempio: avevo necessita’ di eseguire un backup di alcuni database potgresql/postgis in maniera continua (su HD removibile HP RDX 320 GB)

Ho creato uno script per fare tutto cio’; in un secondo momento ho modificato il CRONTAB della macchina per fare in modo che questo script venga eseguito ad una certa ora del giorno.

Partiamo dall’inizio:

– con l’editor “nano” creiamo il file “backup.sh” nella directory “/usr/bin/”

– cd /usr/bin

– nano backup.sh

e lo compiliamo nel modo seguente:

#!/bin/bash

export PGPASSWORD=”mia_password_del_db” && pg_dump -h localhost -U user -c -d -R -v nome_database -n nome_schema > /percorso/dove/salvare/il/file.sql

L’istruzione “export PGPASSWORD=”mia_password_del_db” permette di dichiarare la password stessa senza doverla inserire ogni volta che lo script viene lanciato (questo renderebbe inutile un backup notturno in quanto il prompt si aspetterebbe l’immissione della pwd a mano).

Chiudiamo nano e salviamo il file. Rendiamolo eseguibile (da root):
# chmod +x /usr/bin/backup.sh

Modifichiamo il crontab per eseguirlo ad una certa ora del giorno:

@ crontab -e

e inseriamo le seguent righe:

00 04 * * * /usr/bin/backup.sh

In questo modo il backup viene lanciato ogni giorno alle 4 di mattina.

pgrouting | calcolo percorso minimo tra due punti

Prima di tutto e’ necessario creare un database Postgresql ed aggiungerci l’estensione spaziale Postgis.

come utente postgres si entra in un database esistente o in un template:

# psql template1;

– Creiamo il database e impostiamo la proprietà all’utente (nel nostro caso “sit”):

template1=# CREATE DATABASE routing OWNER sit template template_gis;

il template_gis e’ stato creato a priori (database con estensioni spaziali postgis incorporate; questo per evitare ogni volta di dover aggiungere le tabelle “geometry_column” e “spatial_ref_sys”);

– Ci colleghiamo al database appena creato:

template1=# /connect routing;

e diamo i permessi necessari all’utente “sit” sulla tabella spaziali:

routing=# GRANT ALL ON geometry_columns to sit;
routing=# GRANT SELECT ON spatial_ref_sys to sit;

– Aggiungiamo le funzioni di routing (dopo essere usciti dal database ed esserci autenticati come utente “postgres”):

# psql -d routing -f /usr/share/postlbs/routing_core.sql

# psql -d routing -f /usr/share/postlbs/routing_wrappers.sql

# psql -d routing -f /usr/share/postlbs/routing_topology.sql

– Reperiamo i dati: nel mio caso ho reperito il planet italiano dal sito http://download.geofabrik.de/osm/europe/. Ho scaricato gli SHP. Dopo aver fatto un clip dei dati sul confine regionale di interesse (Veneto) medianti i preziosissimi ftools di qgis ho ottenuto il grafo stradale del territorio veneto.

– Importiamo i dati del database mediante il modulo shp2pgsql. Ecco la sintassi del comando (con SRID settato a 4326, i dati sono in lat-long WGS84).

$ shp2pgsql -S -s 4326 /home/sit/geodatabase/shp/stradario_regione/stradario_regione_seg.shp stradario_regione routing > /home/sit/sql/stradario_regione.sql

$ psql -h localhost -U postgres -d routing -f /home/sit/sql/stradario_regione.sql

Per i passi successivi ho seguito questo ottimo tutorial ):

– Aggiungiamo 3 colonne alla tabella: una per memorizzare gli ID dei nodi iniziali, una per gli ID dei nodi finali e la’ltra per la lunghezza:

routing=> ALTER TABLE stradario_regione ADD COLUMN source integer;

routing=> ALTER TABLE stradario_regione ADD COLUMN target integer;

routing=> ALTER TABLE stradario_regione ADD COLUMN length double precision;

– Creiamo la topologia e aggiungiamo il valore della lunghezza al campo appena creato:

routing=> SELECT assign_vertex_id(‘stradario_regione’, 0.0001, ‘the_geom’, ‘gid’);

routing=> UPDATE stradario_regione SET length = length(the_geom);

– Creiamo gli indici per le colonne “source”, “target” e “the_geom”;

routing=> CREATE INDEX source_idx ON stradario_regione(source);

routing=> CREATE INDEX target_idx ON stradario_regione(target);

routing=> CREATE INDEX geom_idx ON stradario_regione USING GIST(the_geom GIST_GEOMETRY_OPS);

– Impostiamo la query di routing e salviamo il tutto in una nuova tabella chiamata “dijkstra_resust” (prima di tutto cancelliamo una eventuale tabella omonima precedentemente creata):

routing=> DROP TABLE IF EXISTS dijsktra_result;

routing=> CREATE TABLE dijsktra_result(gid int4) with oids;

routing=> SELECT AddGeometryColumn(‘dijsktra_result’, ‘the_geom’, ‘4326’, ‘MULTILINESTRING’, 2);

routing=> INSERT INTO dijsktra_result(the_geom) SELECT the_geom FROM dijkstra_sp(‘stradario_regione’, 73441, 13547);

Questa query trova il percorso minimo tra due vertici (con ID pari a 73441 e 133547).

In prima battuta, nel mio caso, ho ottenuto un messaggio d’errore per violazione di un CONSTRAINT:

ERROR: new row for relation “dijkstra_result” violates check constraint “enforce_geotype_the_geom”

Ho cancellato questo constraint (via pgadmin3) e re-impartito la query ottenendo la nuova tabella dijkstra popolata. Il risultato si può visualzzare in qgis.

pgrouting | compilazione in debian Lenny

Ho provato ad installare l’estensione di postgresql-postgis per il routing chiamata pgRouting. Si tratta di una estensione che consente di effettuare analisi di reti.

Ho tentato la compilazione (in Debian Lenny)  in quanto non esiste come pacchetto precompilato per debian. Questo perche’ la compilazione “canonica” dei sorgenti mi ritornava un errore in sede di “make”. Ho notato (alla fine di tutta la storia) che la creazione dei .deb e la loro installazione può risultare propedeutica alla compilazione canonica (se il lettore ha notizie certe che possono confermare o demolire questa ipotesi posti un commento al post; sarei molto felice di approfondire la questione). La creazione dei .deb che vediamo tra poco consente di installare solo la parte “core” delle librerie. Questo a causa della della mancanza delle librerie GAUL nei repos ufficiali (necessarie per il problema del TSP – Travel Sale Person) e delle CGAL (necessarie per le driving distance) rilasciate sotto licenza QPL, quindi non libere.

Per avere TSP e driving distance e’ pertanto necessario installare dai sorgenti, la cui compilazione va a buon fine (o almeno per me e’ stato cosi’) solo dopo l’installazione del “core” pacchettizzato debian.

Creazione dei pacchetti .deb delle core library (senza funzioni TSP e DD)

Prima di tutto ho scaricato i sorgenti da qui.

Seguendo le istruzioni riportati nel sito ho seguito i seguenti passi:

– diamo un controllo ai requirements: cmake ed i compilatori C e C++ li dovremmo trovare già installati nella nostra distro; mancano invece le librerie Boost Graph Library (BGL), le Genetic Algorithm Utility Library (GAUL, per il problema del “commesso viaggiatore”), e le librerie computazionali Geometry Algorithms Library (CGAL, per il driving);

– per installare le BGL ho usato apt-get (sono già nei repo di Lenny): # apt-get install libboost-graph-dev

– anche le CGAL si trovano: installate via synaptic;

– le GAUL invece le ho scaricate dal sito: una volta fatto, scompattare il sorgente e impartire i comandi riportati nella pagina di istruzioni indicata sopra. I comandi sono:

$ ./configure –disable-slang

$ make

# make install

Seguendo le istruzioni riportate su questa pagina (nascosta, in quanto non c’e’ link del sito che la punti) ho compilato le librerie a partire dall’SVN:

– creare una directory in cui scaricare i sorgenti (nel mio caso $ mkdir compila_pgrouting);

– spostarsi nella directory appena creata e lanciare: $ svn checkout http://pgrouting.postlbs.org/svn/pgrouting/trunk pgrouting

– spostarsi nella directory pgrouting: $ cd pgrouting e diventare root

– creare i paccheti con: # dpkg-buildpackage -b -rfakeroot -uc -us

– risalire di una posizione : # cd ..

– installare i pacchetti con: # dpkg -i *.deb

A questo punto bisogna aggiungere le funzioni di routing al database (che deve essere gia’ un database spaziale). NB: il database ci chiama routing nel nostro caso.

psql -d routing -f /usr/share/postlbs/routing_core.sql
psql -d routing -f /usr/share/postlbs/routing_core_wrappers.sql

L’installazione copia due file con le istruzioni SQL. (routing_core.sql e routing_core_wrappers.sql). Seguendo i tutorial si notera’ che ad un certo punto, dopo avere importato i dati del database, e’ necessario creare la topologia dei dati. Per fare questo viene indicata una funzione “add_vertex_id” secondo questa sintassi:

nome_db=> SELECT assign_vertex_id(‘stradario_regione’, 0.0001, ‘the_geom’, ‘gid’)

dove:

– stradario_regione= nome tabella che contiene i dati;

– 0.0001= tolleranza di snap per la ricerca di nodi non collegati agli archi (espressa in unita’ di misua dei dati; nel mio caso avendo EPSG=4326 sono decimillesimi di grado);

– the_geom=colonna geometrica della tabella interessata;

– gid= colonna con gli id degli oggetti (solitamente la colonna “gid”).

In prima battuta, lanciando la query ho ottenuto questo messaggio:

ERROR:  function assign_vertex_id(unknown, numeric, unknown, unknown) does not exist

Infatti, navigando all’interno del database, nella sezione “Function” dello schema “public” non si trova una funzione chaimata “add_vertex_id”. Scrivendo il lista pgrouting mi e’ stato indicato da Anton Patrushev (che ringrazio infinitamente) che le regole per la pulizia topologica sono state spostate in un file di struzioni SQL chiamato “routing_topology.sql”. Ma purtroppo questo file non viene copiato in “usr/share/postlibs/” come gli altri due files indicati sopra (forse un bug?). Tuttavia questo file si trova nei sorgenti scaricati da SVN. Quindi ho copiato il file in “/usr”share/postlibs/” e lanciato l’istruzione (come utente postgres):

psql -d routing -f /usr/share/postlbs/routing_topology.sql

e magicamente la funzione “add_vertex_id” si trova tra le “Function” del database.

A questo punto ho re-impartito la query (dopo essere entrato nel db come utente “sit”) per la creazione della topologia:

SELECT assign_vertex_id(‘stradario_regione’,0.0001, ‘the_geom’, ‘gid’);

e questo e’ l’output del comando:

NOTICE:  CREATE TABLE will create implicit sequence “vertices_tmp_id_seq” for serial column “vertices_tmp.id”
CONTEXT:  SQL statement “CREATE TABLE vertices_tmp (id serial)”
PL/pgSQL function “assign_vertex_id” line 14 at EXECUTE statement
assign_vertex_id
——————
OK
(1 row)

Aprendo i dati in qgis e interrogando un qualsiasi arco del grafo si nota che i campi “sorce” e “target” sono popolati con gli ID dei nodi iniziale e finale dell’arco stesso.

Compilazione integrale dei sorgenti

La compilazione integrale dei sorgenti (per avere le funzioni di TSP e driving distance) avviene come da manuale: ho seguito anche questa ottima guida.

– spostarsi nella directory ottenuta dalla scompattazione dei sorgenti di pgrouting, quindi:

– $ cmake -DWITH_TSP=ON -DWITH_DD=ON .

– $ make

– # make install

[ 16%] Built target routing_tsp
[ 50%] Built target routing_dd
[100%] Built target routing
Install the project…
— Install configuration: “”
— Installing: /usr/lib/postgresql/8.3/lib/librouting.so
— Installing: /usr/share/postlbs/routing_core.sql
— Installing: /usr/share/postlbs/routing_core_wrappers.sql
— Installing: /usr/share/postlbs/routing_topology.sql
— Installing: /usr/lib/postgresql/8.3/lib/librouting_tsp.so
— Installing: /usr/share/postlbs/routing_tsp.sql
— Installing: /usr/share/postlbs/routing_tsp_wrappers.sql
— Installing: /usr/lib/postgresql/8.3/lib/librouting_dd.so
— Installing: /usr/share/postlbs/routing_dd.sql
— Installing: /usr/share/postlbs/routing_dd_wrappers.sql

– Quindi ho aggiunto le funzioni TSP e DD al database “routing” creato in precedenza:

– (come utente postgres):

# psql -d routing -f /usr/share/postlbs/routing_tsp.sql (crea 1 funzione)

# psql -d routing -f /usr/share/postlbs/routing_tsp_wrappers.sql (crea 5 funzioni)

# psql -d routing -f /usr/share/postlbs/routing_dd.sql (crea 2 funzioni)

# psql -d routing -f /usr/share/postlbs/routing_dd_wrappers.sql (crea 1 funzione)

# ./configure --disable-slang

Postgis | calcolare la lunghezza totale di uno strato lineare

Postgis offre 1000 strumenti per l’analisi spaziale. Una di queste, semplice quanto efficace, e’ la possibilita’ di calcolare la lunghezza totale degli oggetti memorizzati in una tabella di tipo LINESTRING.

Per esempio possiamo porci questa domanda : qual e’ la lunghezza totale delle strade, espressa in Km?
Procediamo:

– connettersi al database con psql:

$ psql -h nome_host -U nome_utente nome_database;

– una volta entrati (dopo aver digitato la password) impartire la seguente istruzione SQL:

nome_database=> SELECT sum(length(the_geom))/1000 AS km_roads FROM nome_tabella;

Nel mio caso ho ottenuto:

km_roads
——————
187.869370073215

che rappresenta la lunghezza totale espressa in Km.

Nel caso si volesse recuperare la lunghezza delle strade distinte per categoria (o meglio “classificazione”):

nome_database=> SELECT classifica,sum(length(the_geom))/1000 AS km_roads FROM grafo_new GROUP BY classifica;

classifica  |     km_roads
————-+——————-
| 0.278103249728031
provinciale |  7.57308599148574
statale     |  12.8419180713568
comunale    |   155.13306294845
autostrada  |  1.00396374819259
vicinale    |  11.0392360640017
(6 rows)

In questo modo si ottiene la lunghezza distinta per classificazione di strade

postgresql | archiviazione di immagini

Per archiviare le immagini in un db postgreSQL ci sono 2 metodi: salvare l’immagine direttamente nel database (con formato dati BLOB) oppure memorizzare solamente il percorso al file immagine. Questo secondo metodo risulta più pulito: in fase di dump del database le immagini vengono mantenute all’esterno del db.
Si crea una tabella “foto”:
nome_database=# CREATE TABLE foto (id integer NOT NULL, nome varchar(50), image_path varchar(100); // inserimento del percorso al file
Si popola la tabella creata:
nome_database=# INSERT INTO foto VALUES (‘1′,’foto01′,’/percorso/al/file/immagine/image001.png’);
Si concedono i GRANT di selezione agli utenti desiderati.
Le immagini possono essere visualizzate via PHP su browser web. Il codice PHP per la navigazione vine mandato al altro post.

postgis | conteggio punti ricadenti all’interno di poligoni

Questo metodo consente di contare il numero di punti ricadenti all’interno di poligoni mediante instruzione SQL in postGIS.

Partiamo da un esempio: siamo in possesso di uno strato poligonale (i.e. una griglia a maglia quadrata di lato 100m) e di uno strato puntuale (i.e. i punti che individuano antenne dislocate sul territorio). Lo scopo è quello di contare quante antenne ricadono all’interno di ogni quadrato di 100m.

La procedura illustrata crea una nuova tabella con il conteggio dei punti cercati.

nome_datbase=# CREATE TABLE nome_tabella AS SELECT tabella_polygon.cat, COUNT(id) AS count FROM tabella_point,tabella_polygon WHERE tabella_point.the_geom && tabella_polygon.the_geom AND CONTAINS (tabella_polygon.the_geom, tabella_point.the_geom) GROUP BY tabella_polygon.cat;

dove:

– tabella_polygon è la tabella che contiene le griglie;

– tabella_point è la tabella che contiene le antenne;

– tabella_polygon.cat rappresenta un campo id univoco (il cat proviene da una esportazione di dati da GRASS);