openlite | migrazione tra database in pochi click

Il papà di SpatiaLite, Alessandro Furieri, ha lanciato un altro ottimo tool: OpenLite. Si tratta di uno strumento leggerissimo e semplice per migrare database (interi o in parte) tra SpatiaLite, PostGIS e MySQL.

E’ disponibile in forma sorgente oppure binaria per Windows ed è accompagnato da una semplice ma esaustiva guida che si trova alla stessa pagina del progetto.

Ho provato ad installarlo dai sorgenti su Ubuntu 10.10. Ecco una sisntesi dei passaggi e delle prove fatte:

– scaricare i sorgenti da qui;

– scompattare tutto in una directory di lavoro; nel mio caso in “/home/sit/src/openlite-0.0.1

– ci sono dipendenze da soddisfare: libspatialite e wxWidgets. Le installiamo via sudo apt-get install nome_pacchetti o via synaptic

– da riga di comando si accede alla directory interessata:

cd /home/sit/src/openlite-0.0.1

– lanciare in sequenza i tre canonici comandi:

  ./configure

  make

  sudo make install-strip

– a questo punto il programma è installato. Lo lanciamo da riga di comando con:

sit@dell1530:~$ openlite

– si presenta così:

OpenLite - come si presenta

la semplicità dello strumento si nota subito


– è necessario stabilire prima di tutto una connessione ad un db SQLite. Se ne esiste già uno colleghiamoci cliccando sul pulsante in alto a sx “Connecting an existing SQLite DB” e cerchiamo il DB interessato. In alternativa possiamo creare un DB nuovo cliccando sul secondo pulsante da sx “Creating a New (empty) SQLite DB”.

– in seconda battuta connettiamoci ad un atro DB. Nel mio caso ho provato con un DB PostGIS (PostgreSQL). Cliccare sull’icona con freccia azzurra e contenitore magenta “Connecting to PostgreSQL/PostGIS DBMS”. La prima volta che si tenta di connettersi ad un DB PostgreSQL viene chiedo di individuare la libreria necessaria. Nel nostro caso si tratta di “/usr/lib/libpq.so.5.2“. Questa impostazione viene salvata permanentemente ed ogni connessione successiva a DB PostgreSQ/PostGIS cercherà questa libreria. Nel caso si indicasse la libreria sbagliata viene restituito il messaggio “No PostgreSQL Client Library available … sorry
Connection impossible”. Per “sbloccare” la situazione ed impostare il percorso corretto alla libreria ci sono due alternative:

– alternativa 1 – brutale ma efficade:

– andare nella home directory
– lanciare il comando: ls -la
– dovremmo trovare un file nascosto chiamatto “.OpenLite” (è il file in cui vengono salvate le impostazioni permanenti)
– cancellare il file e ripartire.

– alternativa 2 – piu’ soft (ma non testata):

– andare nella home directory e aprire il file nascosto .OpenLite con: $ nano .OpenLite

– modificare la riga “PostgisLibraryName=libpq.so.5.2” indicando la libreria corretta.

– rilanciare openlite e ripartire.

Una volta individuata la libreria compare un popup per l’inserimento del percorso al DB e delle credenziali di accesso.

connessione a PostgreSQL

Connessione a DB PostgreSQL/PostGIS

Nella parte dx del pannello vengono elencate tutte le tabelle presenti del DB PostgreSQL. Selezionare con click sx la tabella che si vuole trasferire al DB SQLite e poi click dx. Compare un menù contenstuale: scegliere la voce “Select for data transfer”. Poi click dx sullo schema che contiene la tabella in questione (nel nostro caso “public“) altrimenti non è possibile avviare il tarsferimento. Fatto questo verrà abilitato un nuovo pulsante “Start data transfer” che si trova a sx del pulsante “About…”.

– Click sul pulsante indicato.

– Fatto: in questo modo avviene il trasferimento della tabella in questione al DB SQLite che comparirà dopo qualche secondo (a seconda delle dimensioni) nella parte sx del pannello.

La tabella "areacirc" è stata trasferita con successo

Annunci

django | somma di campi

Supponiamo di avere una tabella contente i valori dei consumi energetici di un particolare dispositivo.

In particolare abbiamo inserito un campo (somma) che sarà popolato con il risultato ottenuto dalla somma di altri 4 campi. In questo modo ad ogni inserimento (Admin) di dati relativi ai primi 4 campi (rientranti nella somma) e dopo avere cliccato il punsante “Salva” o “Salva e continua le modifiche” il campo “somma” apparirà popolato con il valore somma.

Ecco un esempio di strutturazione della class nel file models.py.

class ConsumiIlluminazione(models.Model):
 """Tabella consumi illuminazione"""

 utente = models.ForeignKey(UtenteIlluminazione)
 campo1 = models.IntegerField(null=True, blank=True)
 campo2 = models.IntegerField(null=True, blank=True)
 campo3 = models.IntegerField(null=True, blank=True)
 campo4 = models.IntegerField(null=True, blank=True)
 somma = models.IntegerField(null=True, blank=True)
 def calcoloTotale(self):
  self.somma = self.campo1 + self.campo2 + self.campo3 + self.campo4
 def save(self):
  self.calcoloTotale()
  super(ConsumiIlluminazione, self).save()
 def __unicode__(self):
  return "%s" % (self.utente)
 class Meta:
  ordering = ["utente"]
  verbose_name_plural = "Consumi illuminazione"

geodjango | primo approccio

GeoDjango è un add-on per Django che consente di gestire e manipolare dati geografici all’interno di un progetto django.

Questo post riassume i passi iniziali per implementare una class geografica in una applicazione….spero di espandere il post non appena avro’ fatto qualche test ed esperienza ulteriore. Andiamo per step:

1. Creazione del db geografico (nel mio caso si tratta di un db Postgis).

Se ancora non esiste creiamo un template per i db gis (sarà molto comodo anche in futuro):

– autenticarsi come utente postgres ed entrae in un template (esempio il “template1”):

postgres@debian:/home/sit$ psql template1;

–  creare un nuovo db che chiameremo template_gis sul modello del template0;

postgres=# CREATE DATABASE template_gis template=template0;

– uscire da psql e creare il linguaggio per il db appena creato:

postgres@debian:/home/sit$ createlang plpgsql template_gis

– poi:

postgres@debian:/home/sit$ psql -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql -d template_gis

– popoliamo la tabella spatial_ref_sys:

postgres@debian:/home/sit$ psql -d template_gis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

– entrare nuovamente in un template e indicare che template_gis è un template:

postgres=# UPDATE pg_database set datistemplate=’t’ WHERE datname=’template_gis’;

– a questo punto possiamo creare il nostro db per django usando il template_gis appena creato:

postgres=# CREATE DATABASE energia_django template=template_gis OWNER sit;

– ci spostiamo all’interno del database appena creato:

postgres=# \connect energia_django

– poi è necessario dare i GRANT di SELECT sulla spatial_erf_sys e ALL sulla geometry_columns:

postgres=# GRANT SELECT on spatial_ref_sys to sit;

postgres=# grant ALL on geometry_columns to sit;

2. Configurazione del progetto django (geodjango)

Andiamo all’interno del nostro progetto django (il progetto sia chiama “energia” e una prima applicazione si chiama “manager”)

– Configurare il file settings.py modificando le impostazioni del database come segue:

DATABASES = {
    'default': {
         'ENGINE': 'django.contrib.gis.db.backends.postgis',
         'NAME': 'energia_django',
         'USER': 'sit',
     }
}

Aggiungere:

django.contrib.gis tra le INSTALLED_APPS

3. Preparazione dei dati geografici

Partiamo da dati in formato SHP (nel nostro caso si tratta di uno SHP di punti chiamato “quadri.shp”.

– creare una directory chiamata “data” all’interno della nostra applicazione:

$ mkdir manager/data

e salviamo al suo interno gli SHP relativi:

– con ogrinfo ispezioniamo lo SHP:

$ ogrinfo -so quadro.shp quadro

ottenendo una lista di dati relativi allo SHP (tra cui il numero di feature, SRS, nomi dei campi). Questo è molto importante per creare la class (nel file models.py) che andrà ad “accogliere” i dati. Definiamo quindi la class nel nostro models.py dell’applicazione.

class QuadroIlluminazione(models.Model):
 kp = models.IntegerField()
 numnuovo = models.CharField(max_length=5)
 via = models.CharField(max_length=30)
 codanagr = models.IntegerField()
 norma = models.CharField(max_length=2)
 regolato = models.CharField(max_length=2)
 contenel = models.CharField(max_length=15)
 contpote = models.CharField(max_length=20)
 elettronic = models.CharField(max_length=2)
 ncliente = models.CharField(max_length=10)
 gmrotation = models.FloatField()
 geom = models.MultiPointField(srid=3003)
 objects = models.GeoManager()
 class Meta:
 verbose_name_plural = "Quadri illuminazione pubblica"

 # Returns the string representation of the model.
 def __unicode__(self):
 return self.numnuovo

NB: attenzione all’indentazione; in questo post puo’ non essere corretta; vedere la guida di python per una corretta scrittura del codice)

Abbiamo definito la nuova tabella che conterrà i dati geografici con gli stessi attributi trovati nello SHP. Gli unici due campi che vengono aggiunti sono “geom” e “objects“.

– Controlliamo che tutto sia a posto prima di generare il DB con:

$ python manage.py sqlall manager

l’output è simile a questo (la parte finale):

……………..

CREATE INDEX “manager_contrattoaperturagasnaturale_utente_id” ON “manager_contrattoaperturagasnaturale” (“utente_id”);
CREATE INDEX “manager_contrattofornituragasnaturale_utente_id” ON “manager_contrattofornituragasnaturale” (“utente_id”);
SELECT AddGeometryColumn(‘manager_quadroilluminazione’, ‘geom’, 3003, ‘MULTIPOINT’, 2);
ALTER TABLE “manager_quadroilluminazione” ALTER “geom” SET NOT NULL;
CREATE INDEX “manager_quadroilluminazione_geom_id” ON “manager_quadroilluminazione” USING GIST ( “geom” GIST_GEOMETRY_OPS );COMMIT;

– a questo punto sincronizziamo il DB con:

$ python manage.py syncdb

compariranno una serie di messaggi (con la richiesta di creazione del superutente). alla fine avremo il nostro db pronto.

4. Ridefinizione del models.py

Il models.py deve essere modificato inserendo classe e dizionario. Una funzione ci viene in aiuto per automatizzare il processo:

$ python manage.py ogrinspect manager/data/quadro.shp QuadroIlluminazione –srid=3003 –mapping –multi

Questo comando produce in output la definizione della class e del dizionario necessario per il nostro layer geografico. Tale output puo’ essere copiato/incollato nel nostro models.py

5. Caricamento dei dati del database

Per il caricamento dei dati è necessario creare un modulino python per l’import. Per farlo creare un file chiamato “load.py” e salvarlo all’interno della nostra applicazione. Copiare al suo interno il seguente codice:

import os
from django.contrib.gis.utils import LayerMapping
from models import QuadroIlluminazione

quadroilluminazione_mapping = {
 'kp' : 'KP',
 'numnuovo' : 'NumNuovo',
 'via' : 'Via',
 'codanagr' : 'CodAnagr',
 'norma' : 'Norma',
 'regolato' : 'Regolato',
 'contenel' : 'ContEnel',
 'contpote' : 'ContPote',
 'elettronic' : 'Elettronic',
 'ncliente' : 'Ncliente',
 'gmrotation' : 'GMRotation',
 'geom' : 'MULTIPOINT',

}

quadroilluminazione_shp = os.path.abspath(os.path.join(os.path.dirname(__file__), 'data/quadro.shp'))

def run(verbose=True):
 lm = LayerMapping(QuadroIlluminazione, quadroilluminazione_shp, quadroilluminazione_mapping, transform=True, encoding='iso-8859-1')
 lm.save(strict=True, verbose=verbose)

– Ora invochiamo la shell python per importare i dati mediante il modulo appena creato

$ python manage.py shell

Al prompt di python impartiamo i seguenti comandi (prima richiamiamo il modulo e poi la funzione di “load”):

>>> from manager import load
>>> load.run()

Seguirà una serie di messaggi di import (uno per ogni feature).

6. Caricamento del layer geografico

All’interno del file “admin.py” inserire le seguenti stringhe di codice:

from django.contrib.gis import admin
from manager.models import *
........
admin.site.register(QuadroIlluminazione, admin.GeoModelAdmin)

…to be continued as soon as possible

da PostGIS a Spatialite

Avevo necessità di esportare un DB PostGIS (PostgreSQL con estensione spaziale) in spatialite.

Cercando in rete ho trovato questa pagina: le librerie GDAL/OGR sono in grado di fare la conversione richiesta. In particolare il terzo esempio riportato alla pagina indicata fa proprio al caso nostro.

$ ogr2ogr –config PG_LIST_ALL_TABLES YES –config PG_SKIP_VIEWS NO -f “SQLite” nome_db.sqlite -progress PG:”dbname=’nome_db_postgis’ active_schema=public schemas=public host=’nome_host’ port=’5432′ user=’nome_utente’ password=’pwd_segreta’ ” -lco LAUNDER=yes -dsco SPATIALITE=yes -lco SPATIAL_INDEX=no

Ma provando a caricare il DB in QGIS si ottiene un errore:

“nome del layer” (GEOMETRY non è un layer valido e non può essere caricato)

Ho chiesto il lista gfoss.it e il papà di spatialite, Alessandro Furieri, mi ha gentilmente controllato l’output di ogr2ogr. La sua illuminante risposta e conclusione è riportata qui. Ma provo a riassumere i passaggi che, con le sue indicazioni, ho seguito per ripulire le sporcizie che erano rimaste.

Il database che viene creato presenta alcune incongruenze (alcune dovute ad errori preesistenti del DB Postgis come il settaggio dello SRID che non deve essere uguale a -1 ma deve essere esplicitamente dichiarato). Poi errori di interpretazione delle colonne geometriche nel passaggio da postgis a spatialite.

In sostanza, una volta creato il DB spatialite ho seguito questi passaggi (indicati da Sandro):

$ spatialite nome_db.sqlite

– si presenta il prompt di spatialite: a questo punto impostare alcune semplici configurazioni per l’output dei comandi:

spatialite> .nullvalue NULL

spatialite> .headers on

spatialite> .mode column

In questo modo otteniamo che vengano visualizzati come NULL in valori senza valore (bel gioco di parole :-)), che si vedano gli headers delle tabelle quando si lanciano query di select e che i valori vengano visualizzati secondo una impostazione tabellare.

A questo punto bisogna forzare lo SRID (nel nostro caso si tratta del codice EPSG=3003, Gauss Boaga Fuso Ovest) nella tabella geometry_columns:

spatialite> update geometry_columns SET srid = 3003;

Poi sistemiamo il type geometrico aggiornando alcuni valori sghembi che erano rimasti; in particolare si tratta di alcune tabelle con dati poligonali in cui il tipo geometrico era rimasto con valore GEOMETRY.

spatialite> update geometry_columns SET type=’POLYGON’ WHERE type = ‘GEOMETRY’;

– Infine aggiorniamo correttamente lo SRID su tutte le geometrie creando i trigger necessari al buon funzionamento di spatialite.

spatialite> UPDATE acqua SET GEOMETRY = SetSrid(GEOMETRY, 3003);

Questo va fatto tabella per tabella (nel caso riportato la tabella “acqua”).

A questo punto il caricamento con QGIS va liscio!

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)

django | modificare le tabelle

Una volta creato il progetto e l’applicazione con django succede di dover apportare delle modifiche alla struttura del database (per esempio aggiunta di campi alle tabelle,..).

Per farlo sono necessari 4 passi:

– eseguire un backup del db con:

$ python manage.py dumpdata manager > db.json

dove manager è il nome dell’applicazione (db) e db.json è un backup di tutto il db (JSON sta per JavaScript Object Annotation: si tratta di un formato di interscambio per dati)

– si apportano le modifiche alla tabella (model); ad esempio inserendo un nuovo campo agendo sul file models.py

– si resetta il database: in questo modo vengono ricreate le tabelle con il nuovo modello (rispondere “yes” alla domanda di conferma di reset):

$ python manage.py reset manager

– ri ricaricano i dati prelevandoli dal backup fatto in precedenza:

$ python manage.py loaddata db.json

Fatto!