postgresql | qualche tips, qualche tricks

Posto alcuni comandi pgsql che ho avuto modo di usare di recente dovendo maneggiare i formati date di Postgresql. Li metto cosi’ in ordine sparso e premetto che fanno riferimento ad un database usato per la gestione dei consumi energetici di impianti di illuminazione; pertanto alcuni commenti (personali) fanno riferimento a questo tipo di lavoro. Tuttavia vanno benissimo per qualsiasi altro db.

Sia data una tabella “tbl01” che contiene un campo “anno” come stringa, “data01”, “data02”, “data03”, “data04” campi in formato date, “consumo_anno” come integer :

# comando SELECT per recuperare il valore del campo “anno” sul quale faremo le nostre operazioni:

mydb=# SELECT anno FROM tbl01 WHERE id=4;
 anno
——
 2010
(1 row)

Come si puo’ vedere si tratta di un campo varchar di 4 caratteri.

# comando SELECT che converte in numero (attualmente in formato testo) il campo “anno” della tabella “tbl01”:

mydb=# SELECT to_number(anno, ‘9999’) FROM tbl01 WHERE id=4;
 to_number
———–
      2010
(1 row)

# comando SELECT per convertire in numero il campo “anno” e togliere 1:

mydb=# SELECT to_number(anno, ‘9999’)-1 FROM tbl01;
 ?column?
———-
     2009
     2009
     2008
     2009
(4 rows)

# comando per convertire in data nel formato YYYYMMDD il valore del campo “anno”:

mydb=# SELECT to_date(anno,’YYYYMMDD’) FROM tbl01 WHERE id=4;
  to_date   
————
 2010-01-01
(1 row)

# calcolo del 31 dicembre dell’anno precedente a quello in oggetto (con riferimento al campo “anno”):

mydb=# SELECT to_date(anno,’YYYYMMDD’)-1 FROM tbl01;
  ?column?  
————
 2009-12-31
 2009-12-31
 2008-12-31
 2009-12-31
(4 rows)

# comando per calcolare la differenza tra una data chiamata “data1” (inizio anno) e il 31 dicembre dell’anno precedente (giorni in eccesso inizio anno): siano data0=2008-12-29, data1=2009-01-12, data2=2009-12-31, data3=2009-12-31.

mydb=# SELECT data1-(to_date(anno,’YYYYMMDD’)-1) FROM tbl01 WHERE id=4;
 ?column?
———-
       12
(1 row)

# comando per calcolare la differenza tra il 31 dicembre dell’anno precedente e la prima data “data0” (giorni in difetto inizio anno):

mydb=# SELECT (to_date(anno,’YYYYMMDD’)-1)-data0 FROM tbl01 WHERE id=4;
 ?column?
———-
        2
(1 row)

# comando per calcolare la differenza tra “data3” e il 31 dicembre dell’anno in corso (giorni in eccesso fine anno):

mydb=# SELECT data3-(to_date(anno,’YYYYMMDD’)+364) FROM tbl01 WHERE id=4;
 ?column?
———-
        0
(1 row)

# comando per calcolare la differenza tra il 31 dicembre dell’anno in corso e la “data2” (giorni in difetto fine anno):

mydb=# SELECT (to_date(anno,’YYYYMMDD’)+364)-data2 FROM tbl01 WHERE id=4;
 ?column?
———-
        0
(1 row)

===========================================================================

Supponiamo di avere due tabelle in relazione 1 a molti (tabella “a” e tabella “b”) con campo di collegamento (“id” della tabella “a” si collega a a_id della tabella “b”). Se vogliamo recuperare valori da entrambe le tabelle:

mydb=# SELECT a.campo1, b.campo2 FROM a,b WHERE a.id=b.a_id AND campo3=valore ORDER BY id;

===========================================================================

Quella che segue e’ una query su una tabella cosi’ strutturata:

id integer
utente_id integer
data_lettura0 date
energia_attiva_kwh_fase0_l0 numeric(10,2)
energia_attiva_kwh_fase1_l0 numeric(10,2)
energia_attiva_kwh_fase2_l0 numeric(10,2)
energia_attiva_kwh_fase3_l0 numeric(10,2)
totale_energia_attiva_kwh_l0 numeric(12,2)
data_lettura1 date
energia_attiva_kwh_fase0_l1 numeric(10,2)
energia_attiva_kwh_fase1_l1 numeric(10,2)
energia_attiva_kwh_fase2_l1 numeric(10,2)
energia_attiva_kwh_fase3_l1 numeric(10,2)
totale_energia_attiva_kwh_l1 numeric(12,2)
data_lettura2 date
energia_attiva_kwh_fase0_l2 numeric(10,2)
energia_attiva_kwh_fase1_l2 numeric(10,2)
energia_attiva_kwh_fase2_l2 numeric(10,2)
energia_attiva_kwh_fase3_l2 numeric(10,2)
totale_energia_attiva_kwh_l2 numeric(12,2)
data_lettura3 date
energia_attiva_kwh_fase0_l3 numeric(10,2)
energia_attiva_kwh_fase1_l3 numeric(10,2)
energia_attiva_kwh_fase2_l3 numeric(10,2)
energia_attiva_kwh_fase3_l3 numeric(10,2)
totale_energia_attiva_kwh_l3 numeric(12,2)
anno character varying(4)
inizio_anno date
fine_anno date
consumo_anno integer

# query per aggiornare la tabella delle letture (in un db che contiene le letture dei consumi di contatori elettrici) calcolando il consumo_anno nel caso in cui data_lettura0!=data_lettura1 per l’anno=’2009′:

energia_django=> UPDATE manager_letturailluminazione SET consumo_anno=(totale_energia_attiva_kwh_l3-totale_energia_attiva_kwh_l1)+((totale_energia_attiva_kwh_l1-totale_energia_attiva_kwh_l0)/(data_lettura1-data_lettura0)*(data_lettura1-(to_date(anno,’YYYYMMDD’)-1)))-((totale_energia_attiva_kwh_l1-totale_energia_attiva_kwh_l0)/(data_lettura1-data_lettura0)*(data_lettura3-(to_date(anno,’YYYYMMDD’)+364)))  WHERE anno=’2009′ AND data_lettura0!=data_lettura1;

id integer
Annunci

Postgresql | Postgis | modificare i valori dei campi di una vista

Lavorando con Postgresql|Postgis mi capita spesso di avere delle tavole come viste “View” ottenute dal join di una tabella geomerica e di una tabella non-geometrica. Esempio: la tabella geometrica contiene poligoni (edifici) con un campo chiave (es= scheda); la tabella alfanumerica contiene tutti i dati relativi a quegli edifici + un campo chiave “scheda” come la prima.

Questo perché, in un lavoro svolto, un utente si occupava di gestire la parte geometrica (mediante QGIS) mentre un altro utente caricava i dati alfanumerici relativi ad un rilievo di edifici mediante interfaccia web (via PHP).

In un secondo momento quindi è stata creata una join fra le due tabelle in modo da generare tavole tematiche sulle condizioni e sull’uso di quegli edifici.

Ecco la View creata (siano “schede” e “schede_nogeom” rispettivamente la tabella geometrica e non-geometrica)

=> CREATE VIEW schede_cartogr AS SELECT scheda_nogeom.grado, scheda_nogeom.altezza, scheda_nogeom.volume, scheda_nogeom.piani, scheda_nogeom.note, scheda_nogeom.stcontetto, scheda_nogeom.punttetto, scheda_nogeom.stconprosp, scheda_nogeom.puntprosp, scheda_nogeom.stconserr, scheda_nogeom.puntserr, scheda_nogeom.stconsolai, scheda_nogeom.puntsolai, scheda_nogeom.stconmurat, scheda_nogeom.stcompl, scheda_nogeom.puntcompl, scheda_nogeom.d_uso_pint, scheda_nogeom.d_uso_pt, scheda_nogeom.d_uso_p1, scheda_nogeom.d_uso_p2, scheda_nogeom.d_uso_p3, scheda_nogeom.d_uso_p4, scheda_nogeom.d_uso_p5, scheda_nogeom.d_uso_compl, scheda_nogeom.d_uso_progetto, scheda_nogeom.d_uso_comp_grafica, scheda_nogeom.essenze_arboree, scheda_nogeom.emerg_arch, scheda_nogeom.h_pint, scheda_nogeom.h_pt, scheda_nogeom.h_p1, scheda_nogeom.h_p2, scheda_nogeom.h_p3, scheda_nogeom.h_p4, scheda_nogeom.h_p5, scheda_nogeom.vol_pint, scheda_nogeom.vol_pt, scheda_nogeom.vol_p1, scheda_nogeom.vol_p2, scheda_nogeom.vol_p3, scheda_nogeom.vol_p4, scheda_nogeom.vol_p5, scheda.area_gis, scheda_nogeom.foto1, scheda_nogeom.estratto_cartografico, scheda_nogeom.vol_residenza, scheda_nogeom.vol_comm_dir, scheda_nogeom.vol_artigianale, scheda_nogeom.vol_pubblico, scheda_nogeom.vol_misto, scheda_nogeom.puntmurat, scheda_nogeom.d_uso_vigente, scheda.gid, scheda.the_geom, scheda.scheda FROM scheda JOIN scheda_nogeom ON scheda.scheda::text = scheda_nogeom.scheda::text;

– Caricando questa vista in QGIS riusciamo quindi a consultare geometrie e tutti i valori colelgati. Ma se ad un certo punto voglio modificare un valore o alcuni valori della tabella? Abilitando l’editing sul layer “schede_cartogr” vediamo che possiamo modificare i valori ma al momento del salvataggio veniamo avvisati che non è possibile. Ci viene allora in aiuto la creazione di un RULE con istruzione “DO INSTEAD” in Postgresql . In sostanza si tratta di una regola che consente di modificare la (o le) tabelle origine agendo sulla vista.

In generale la sintassi è la seguente:

>= CREATE RULE nome_rule_da_creare AS ON UPDATE TO nome_della_view DO INSTEAD UPDATE nome_tabella_origine SET nome_campo=NEW.nome_campo WHERE campo_chiave=NEW.campo_chiave;

Ecco la definizione del RULE che ho usato:

=>CREATE RULE update_grado_schede_cartogr AS ON UPDATE TO schede_cartogr DO INSTEAD UPDATE scheda_nogeom SET grado=NEW.grado WHERE scheda=NEW.scheda;

D’ora in avanti possiamo modificare da QGIS il valore del campo “grado“; verrà aggiornato di conseguenza il valore della tabella originale.

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.