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
Annunci

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

join tra layer postGIS (tabella geometrica) e tabella postgreSQL

Dato un layer postGIS con campo chiave “id” ed una tabella postgreSQL che contiene lo stesso campo chiave si può creare una VIEW (vista) tramite JOIN sul campo comune. In questo modo le informazioni geometriche degli oggetti vengono salvate su una tabella “leggera” mentre le informazioni alfanumeriche (attributi) vengono salvate su un’altra tabella. Es:

nome_database=# SELECT tabella_dati.campo1, tabella_dati.campo2, tabella_dati.campo3,…..,tabella_dati.campon, tabella_geom.id, tabella_geom.gid, tabella_geom.the_geom FROM tabella_geom JOIN tabella_dati ON tabella_geom.id=tabella_dati.id

postgresql | elencare tutti i valori (non ripetuti) di un campo

La parola chiave DISTINCT esclude dai risultati le righe duplicate.

Es: vogliamo elencare tutti i valori che sono contenuti nel campo “nome_via” di una tabella contenente i nomi di tutte le strade. Può succedere che esistano strade “spezzate” in più tratti ed in un database geometrico (PostGIS) siano memorizzati più archi di strada con lo stesso nome. DISTINCT consente di elencare i valori non ripetuti:

nome_database=# SELECT DISTINCT nome_via FROM nome_tabella_strade;

Nel caso volessimo elencare i nomi delle strade così distinte indicando anche la lunghezza totale dei tratti che hanno lo stesso nome:

nome_database=#SELECT DISTINCT ON (nome) nome,sum(lunghezza) FROM nome_tabella_strade GROUP BY nome;

e se volessimo esportare l’output della query in un file CSV:

– nel prompt di psql indicare:

nome_database=# \o /percorso/alt/file/nome_file_output.csv

– poi lanciare la query:

nome_database=#SELECT DISTINCT ON (nome) nome,sum(lunghezza) FROM nome_tabella_strade GROUP BY nome;

– In questo modo otteniamo un file CSV con il risultato.