IT:HowTo minutely hstore
Se ne conosci lo stato attuale, aiuta gli altri a rimanere informati aggiornando questo contenuto. (Discussion)
Questo Howto descrive, passo per passo, come può essere costruito un data base osm2pgsql con colonna hstore installata in ambiente Linux e tenuto sempre aggiornato al minuto mediante i file delle differenze.
Una macchina virtuale già pronta e che contiene questo Howto e HowTo Mapnik & Tirex può essere scaricata via BitTorrent da: HowTo minutely hstore/VM.
Una banca dati nello Schema osm2pgsql può essere utilizzata per quasi tutti gli scopi (Mapnik, Tileserver, Openlayers, POIs). Dal momento che essa non rappresenta i punti di incrocio delle singole vie e strade, non può essere usata per il routing o per gli aggiornamenti API. Di norma un data base in schema osm2pgsql include solamente tag selezionati (es. amenity, highway und name) mentre i tag più rari (es. drink:club-mate, traffic_signals:sound) vengono semplicemente ignorati. Nella colonna hstore invece vengono inclusi tutti i tag, per quanto siano rari, che possono anche essere ricercati. Le modifiche (file di differenze) vengono rilasciate da openstreetmap.org ogni minuto e includono tutte le modifiche che hanno avuto luogo nell'ultimo minuto. Vengono utilizzate per tenere aggiornato il data base.
Sistema operativo
In questo Howto usiamo un'installazione di base di Linux Debian 5.0.3. Per poter tenere aggiornato un data base e, sulla stessa macchina, renderizzare le tile, c'è decisamente bisogno di un sistema veramente solido. Possiamo prendere come esempio una configurazione come quella usata nel server OSM Dev tedesco, con 4 CPU a 2.3 Ghz e 16 GB di RAM. Sul disco fisso è presente uno spazio minimo di 200 GB per il data base. Poi, a seconda di quanto si intendere di avere nelle varie parti del mondo, da 100 a 300 GB per le tile precalcolate. Ciò consiglia l'uso di LVM (Logical Volume Manager), e una partizione tanto per il data base quanto per le tile.
Software
Il data base usato è PostgreSQL 8.3, poiché ci sono osservazioni (vedi anche [1]) secondo le quali l'importazione delle differenze con la versione 8.4 durerebbe più a lungo. Per le informazioni geografiche viene usata l'estensione PostGIS mentre per gli aggiornamenti dalle differenze usata l'estensione IntArray dalla sezione Contrib. Sotto alcune distribuzioni è disponibile anche il pacchetto “postgresql-8.3-hstore-new”. Poiché questo non è il caso della Debian 'Lenny' qui usata, creeremo il pacchetto noi stessi più tardi; per le altre distribuzioni è consigliabile l'utilizzo del pacchetto già pronto.
Per scaricare Quellcodes necessitiamo di Subversion e Cvs, mentre per la creazione dei pacchetti servono Build-essentials, Autoconf e alcuni pacchetti Dev. Inoltre per Osmosis avremo bisogno di Java, Osmosis funziona al meglio con Sun's JRE. Per poter installare quest'ultima, il file /etc/apt/sources.list deve essere modificato come segue:
deb http://ftp.de.debian.org/debian/ lenny main non-free contrib
Fatto questo, possiamo installare il tutto con il comando:
osm@osm:~$
sudo aptitude update
sudo aptitude install postgresql-8.3 postgresql-client-8.3 postgresql-contrib-8.3 \
postgresql-server-dev-8.3 postgresql-8.3-postgis subversion cvs build-essential \
autoconf libxml2-dev libgeos-dev libbz2-dev libpq-dev sun-java6-jre unzip
wget http://bretth.dev.openstreetmap.org/osmosis-build/osmosis-latest.zip
sudo unzip -d /opt osmosis-bin-latest.zip
sudo ln -s /opt/osmosis-* /opt/osmosis
sudo chmod +x /opt/osmosis/bin/osmosis
Configurazione del data base
Il prossimo passo sarà la configurazione del data base. Innanzitutto dovranno essere aumentati i valori “shared_buffers” e “checkpoint_segments”, nella configurazione standard impostati molto bassi. Valori ragionevoli sono ad esempio “shared_buffers=16GB” (Valore standard: 32M ) e “checkpoint_segments=16” (Valore standard: 3)
Ora procediamo con l'estensione hstore, che deve essere scaricata, compilata e installata. Ovviamente ciò è necessario se non è già stato precedentemente installato il pacchetto “postgresql-8.3-hstore-new”.
osm@osm:~$ cvs -d :pserver:anonymous@cvs.pgfoundry.org:/cvsroot/hstore-new checkout hstore-new cd hstore-new/ osm@osm:~/hstore-new$ make sudo make install
Ora è finalmente possibile costruire una data base.
osm@osm:~$
sudo -u postgres createuser gis
Soll die neue Rolle ein Superuser sein? (j/n) n
Soll die neue Rolle Datenbanken erzeugen dürfen? (j/n) n
Soll die neue Rolle weitere neue Rollen erzeugen dürfen? (j/n) n
sudo -u postgres createdb -E UTF8 -O gis gis
sudo -u postgres createlang plpgsql gis
Il data base ora si chiama “gis”, così come il suo utente. Per permettere che l'utente possa accedere al data base, il file „/etc/postgresql/8.3/main/pg_hba.conf“ deve essere modificato come segue. Infine si deve far ripartire il server Postgresql.
# "local" is for Unix domain socket connections only local gis gis trust local all all ident sameuser
Di seguito attiviamo le estensioni per il data base appena creato.
osm@osm:~$ sudo -u postgres psql gis </usr/share/postgresql/8.3/contrib/hstore-new.sql sudo -u postgres psql gis </usr/share/postgresql/8.3/contrib/_int.sql sudo -u postgres psql gis </usr/share/postgresql-8.3-postgis/lwpostgis.sql sudo -u postgres psql gis </usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql
L'installazione dell'estensione PostGIS include entrambe le tabelle “geometry_columns” e “spatial_ref_sys”, che però appartengono ancora all'utente Postgres. Per fare in modo che tutto vada a posto, il proprietario deve essere impostato al nostro utente”gis”:
osm@osm:~$ echo 'ALTER TABLE geometry_columns OWNER TO gis; ALTER TABLE spatial_ref_sys OWNER TO gis;' | sudo -u postgres psql gis
Il prossimo passo consiste nello scaricamento, compilazione e installazione del programma Osm2pgsql:
osm@osm:~$ apt-get build-dep osm2pgsql svn co http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/ cd osm2pgsql/
osm@osm:~/osm2pgsql$ ./autogen.sh ./configure make sudo make install sudo -u postgres psql gis < 900913.sql
L'ultima riga specifica la descrizione della proiezione “900913 Spherical Mercator” nella tabella “patial_ref_sys. Questa si rende necessaria per poter convertire le coordinate, che Osmpgsql ha descritto nel data base, in un'altra proiezione.
Planet-Dump
Ora siamo pronti a scaricare e importare il Planet:
osm@osm:~/data$ wget https://planet.openstreetmap.org/planet-100526.osm.bz2.md5 https://planet.openstreetmap.org/planet-100526.osm.bz2 md5sum -c planet-100526.osm.bz2.md5
Il download dura 4 ore abbondanti. Chi volesse testare un po' più in fretta con dei lavori più piccoli, può usare un piccolo estratto dal Planet di Geofabrik. Qui sotto faremo allo stesso modo. Per un'importazione completa del Planet si deve semplicemente sostituire nelle seguenti istruzioni il nome del file berlin.osm.bz2 con planet-100526.osm.bz2.
osm@osm:~/data$ wget http://download.geofabrik.de/osm/europe/germany/berlin.osm.bz2
Importazione iniziale
Importeremo ora questo file .os.bz2 nel data base creato precedentemente
osm@osm:~/data$ osm2pgsql --create --database gis --username gis --prefix planet --slim --cache 2048 --hstore berlin.osm.bz2
I parametri del comando sopra hanno i seguenti significati:
- --create
- crea le tabelle prima di immettervi i dati
- --database gis
- nome del data base
- --username gis
- nome utente
- --prefix planet
- Tutte le tabelle inizieranno col prefisso „planet_“, per es. „planet_point“
- --slim
- Crea la cache del nodo/way/relation nel data base anziché nella RAM. In questo modo il consumo di RAM diminuirà consentendo successivi aggiornamenti dalle differenze.
- --cache 2048
- Fissa nella RAM la grandezza della cache del nodo. Valori ragionevoli vanno fino a 3072 MB.
- --hstore
- Abilita la colonna hstore
Normalmente Osm2pgsql userà i file in stile standard allegando alla colonna hstore delle colonne proprie create con i tag più importanti (es. amenity, way e name). Ciò è vantaggioso, poiché può modificare lo stile standard OSM-Mapnik, tuttavia ciò comporta un maggior uso di memoria. In alternativa l'importazione può aver luogo anche in stile hstore, il quale oltre alla colonna hstore include soltanto l'ID e la colonna della geometria. Lo stile hstore può essere scaricato qui: http://svn.toolserver.org/svnroot/mazder/planet-import/hstore.style. Verrà poi passato ai parametri stile a Osm2pgsql.
Per quanto riguarda la memoria necessaria, c'è un po' di confusione, in quanto sembra che un'importazione combinata sia più piccola di una che invece usa soltanto la colonna hstore. I numeri precisi possono essere letti nella mailing list seguente (in tedesco). Se qualcuno ha dei numeri diversi o può fornire spiegazioni, dovrebbe cortesemente scrivere ad una delle mailing list di OSM. http://lists.openstreetmap.de/pipermail/devserver/2010-May/001021.html
Un'importazione planetaria può durare a seconda della velocità dei dischi fissi da 24 ore a 5 giorni e con condizioni sfavorevoli ancora più a lungo. Un'importazione dell'estratto di Berlino summenzionato può essere completato in meno di dieci minuti.
Test iniziale
Ora è il tempo di verificare se veramente nel data base abbiamo i dati che ci aspettiamo.
osm@osm:~/data$ psql -U gis gis
gis=> \d
Lista delle Relazioni
Schema | Nome | Typ | Propriet.
--------+------------------+---------+------------
public | geometry_columns | Tabelle | gis
public | planet_line | Tabelle | gis
public | planet_nodes | Tabelle | gis
public | planet_point | Tabelle | gis
public | planet_polygon | Tabelle | gis
public | planet_rels | Tabelle | gis
public | planet_roads | Tabelle | gis
public | planet_ways | Tabelle | gis
public | spatial_ref_sys | Tabelle | gis
(9 righe)
gis=> \d planet_point
Tabelle »public.planet_point«
Colonna | Typ | Attributo
--------------------+----------+-----------
osm_id | integer |
access | text |
addr:flats | text |
addr:housenumber | text |
addr:interpolation | text |
admin_level | text |
aerialway | text |
aeroway | text |
amenity | text |
area | text |
. . .
wood | text |
z_order | integer |
tags | hstore |
way | geometry |
Indexe:
»planet_point_index« gist (way)
»planet_point_pkey« btree (osm_id)
gis=> SELECT osm_id, tags FROM planet_point LIMIT 5;
osm_id | tags
-----------+---------------------------------------------------------
590204700 | "addr:street"=>"Schwanenallee", "addr:housenumber"=>"7"
590204491 | "addr:street"=>"Menzelstraße", "addr:housenumber"=>"18"
590204521 | "addr:street"=>"Menzelstraße", "addr:housenumber"=>"2a"
590349404 | "amenity"=>"bench"
590349407 | "amenity"=>"bench"
(5 Zeilen)
Sembra proprio di sì.
Importazione delle differenze
Le differenze vengono importate con una numerazione progressiva come file osc (OsmChange) compressi gzip pubblicati all'indirizzo https://planet.openstreetmap.org/minute-replicate. Qui viene pubblicato un estratto ogni minuto. I file delle differenze di un periodo di tempo determinato (es. una mezza giornata) vengono scaricati, spacchettati e reimpacchettati in un file .osc tramite il tool Java Osmosis, Il file .osc viene passato a osm2pgsql che aggiorna il data base.
Dal momento che la creazione di un dump planetario su openstreetmap.org, lo scaricamento e l'installazione richiedono ugualmente alcuni giorni, l'importazione delle differenze all'inizio deve ricuperare un ritardo di alcuni giorni prima di raggiungere l'attività normale e importare i singoli file delle differenze.
All'inizio ci occupiamo dello script “load-next” e di alcuni altri file, cosa che raggruppa i vari componenti:
osm@osm:~/data$ svn co http://svn.toolserver.org/svnroot/mazder/diff-import/ diffs
L'importazione viene lanciato all'inizio in un Cronjob ad esempio ogni 15 minuti. Dapprima deve accertarsi che nessun'altra importazione stia avendo luogo. Poi Osmosis scarica i file delle differenze secondo le impostazioni del file di stato e l'intervallo massimo ('maxInterval') di Osmosis riportate in 'configuration.txt' e invia il tutto a Osm2pgsql. Tutto ciò viene eseguito dallo script “load-next'.
Siccome i file delle differenze risiedono in posizioni diverse in ogni server, si deve specificare dei percorsi in testa al file. Nel nostro esempio potrebbe essere così:
#!/bin/bash # loads the diffs for the interval read from configuration.txt # may be executed every 5 minutes or so PIDFILE=`basename $0`.pid OSMOSIS=/opt/osmosis/bin/osmosis OSM2PGSQL=/usr/local/bin/osm2pgsql STYLE=/usr/local/share/osm2pgsql/default.style # java proxy settings for osmosis #JAVACMD_OPTIONS="-Dhttp.proxyHost=ha-proxy.esi -Dhttp.proxyPort=8080" #export JAVACMD_OPTIONS OSMOSISLOG=logs/osmosis.log PSQLLOG=logs/osm2pgsql.log EXPIRYLOG=logs/expiry.log RUNLOG=logs/run.log HOST=/var/run/postgresql DB=gis PREFIX=planet USER=gis
Infine dobbiamo comunicare a Osmosis che data hanno i dati a disposizione nel data base. A questa dovrebbe essere sottratta almeno mezza giornata dalla data di pubblicazione nel dump Planet per poter avviare una sovrapposizione. Dato che Osm2pgsql memorizza soltanto la versione più recente di ogni elemento, ciò non è un problema.
L'istante temporale calcolato può essere immesso in questo tool, che restituisce il corrispondente file di stato:
osm@osm:~/data/diffs$ wget "http://toolserver.org/~mazder/replicate-sequences/?Y=2010&m=05&d=25&H=08&i=00&s=00&stream=minute" -O state.txt
Ora lo script 'load-next' può essere eseguito per la prima volta. La progressione del processo può essere osservata nei file logs/run.log, logs/osmosis.log e logs/osm2pgsql.log
osm@osm:~/data/diffs$ ./load-next & tail -f logs/run.log tail -f logs/osm2pgsql.log
Se la prima importazione si è conclusa con successo, è consigliabile adattare i parametri di temporizzazione e lanciare lo script via Cron.
Ci sono due posti nei quali si può governare il download: nel file diffs/configuration.txt viene specificata la quantità massima di dati da caricare ed elaborare; nella Crontab viene specificato ogni la frequenza con cui si va alla ricerca di nuovi dati.
È stato verificato di caricare con un singolo lancio fino a 6 ore (21600 secondi) e di impostare il Cronjob per un intervallo da 15 a 30 minuti.
# The URL of the directory containing change files. baseUrl=https://planet.openstreetmap.org/minute-replicate/ # Defines the maximum time interval in seconds to download in a single invocation. # Setting to 0 disables this feature. # 6 hours maxInterval = 21600
Una volta che il proprio data base è soltanto 15 - 30 minuti dietro il data base principale, si può abbassare l'intervallo del Cronjob fino a un minuto, se si desidera essere quanto possibile aggiornati. Il valore in diffs/configuration.txt può tranquillamente restare lì, dato che Osmosis carica soltanto i dati più nuovi.
0-59/5 * * * * /home/walter/OSM/projekte/db/diffs/load-next &
Cronjob può verificare se il lavoro precedente è ancora attivo e semplicemente si scollega senfa fare alcunché.
Tramite il file di stato è possibile sapere quanto staccato sia il proprio data base da quello principale. A questo scopo può essere usato per esempio il tool 'replag':
osm@osm:~/data/diffs$ ./replag -h 2 day(s) and 14 hour(s)
Indice hstore
Per rendere efficiente la ricerca nella colonna hstore, contenente tutti i tag, e per esmepio determinare tutti i punti in una zona con “amenity=restaurant” oppure tutte le linee con “highway=trunk”, è necessario immettere un indice GIN nella colonna hstore “tags”.
osm@osm:~$ echo '
CREATE INDEX planet_line_tags ON planet_line USING GIN (tags);
CREATE INDEX planet_point_tags ON planet_point USING GIN (tags);
CREATE INDEX planet_polygon_tags ON planet_polygon USING GIN (tags);
CREATE INDEX planet_roads_tags ON planet_roads USING GIN (tags);
' | psql -U gis gis
Richieste all'hstore
Per poter ora utilizzare la nuova colonna hstore, le richieste SQL devono essere opportunamente formulate. Vengono di seguito mostrate alcune istanze a titolo d'esempio:
osm@osm:~$ psql -U gis gis gis=> -- alle Restaurants mit allen anhängenden tags gis=> SELECT osm_id, tags FROM planet_point WHERE (tags @> '"amenity"=>"restaurant"'); gis=> gis=> -- die Namen aller Restaurants gis=> SELECT tags->'name' FROM planet_point WHERE (tags @> '"amenity"=>"restaurant"') AND (tags ? 'name'); gis=> gis=> -- die Namen aller Restaurants, die mit A anfangen gis=> SELECT tags->'name' FROM planet_point WHERE (tags @> '"amenity"=>"restaurant"') AND (tags->'name' LIKE 'A%'); gis=> gis=> -- Informationen über Club-Mate ausschenkende Geschäfte gis=> SELECT osm_id, tags->'name' AS "name", tags->'drink:club-mate' AS "club-mate" FROM planet_point WHERE (tags ? 'drink:club-mate'); gis=> gis=> -- Top-20 Tags gis=> SELECT key, count(*) FROM gis-> (SELECT (each(tags)).key FROM planet_point) AS stat gis-> GROUP BY key gis-> ORDER BY count DESC, key gis-> LIMIT 20;
Struttura delle tabelle
Dopo l'importazione la struttura del data base contiene 9 tabelle. Queste si dividono in 3 categorie.
Tabelle di geometria
Per l'uso sono di importanza maggiore le quattro tabelle di geometria:
- planet_point
- contiene oggetti puntuali (generati da nodi che possiedono tag)
- planet_line
- contiene oggetti lineari (generati da way o relazioni che possiedono tag)
- planet_polygon
- contiene oggetti piani ((generati da way o relazioni che possiedono tag)
La quarta tabella di geometria è:
- planet_roads
- contiene oggetti lineari (way o relazioni) con tag selezionati – questa tabella è per il render di tile a bassi livelli di zoom. Dato che la tabelle non contiene ad esempio piste pedonali, le richieste sono qui più veloci.
Queste tabelle contengono una colonna per ogni tag dichiarato nell'Import-Style di Osm2gpsql. Ci sono inoltre alcune colonne speciali:
- osm_id
- L'ID dell'oggetto in OpenStreetMap. Dal momento che le tabelle planet_line e planet_polygon contengono entrambe dati a partire sia da way che da relazioni OSM, ci potrebbero essere ID doppi (esiste tanto la Way 1200 quanto la Relation 1200). Per risolvere questo problema, gli oggetti Relation ricevono un ID negativo (-1200 anziché 1200).
- tags
- La colonna prodotta dall'opzione osm2pgsql –hstore e contenente tutti i tag. È possibile risalire ai valori delle singole colonne mediante l'operatore hstore (tags->'name'). Le richieste WHERE possono essere formulate tramite altri operatori.
- way
- Questa è la colonna di geometria. Contiene le informazioni geometriche in formato PostGIS. Nella tabella Planet_point queste informazioni sono costituite da coordinate bidimensionali, nelle altre tabelle invece da descrizioni da linee o poligoni. Per l'estrazione dei contenuti si può per esempio utilizzare le funzioni ST_AsText-, ST_AsSVG e ST_AsGeoJSON, l'ultima delle quali è utile per la rielaborazione in linguaggio di programmazione. Per l'uso di tutte le funzioni e degli operatori si può consultare la PostGIS Reference http://postgis.refractions.net/docs/reference.html. Per il filtraggio dai valori geometrici c'è inoltre un esempio per una richiesta bbox in PHP.
- z_order
- Valore calcolato da Osm2pgsql per riordinare più facilmente superfici e linee. Al ricalcolo dell'indice 'z' vengono inclusi oltre al tag layer anche i tag highway, bridge, tunnel, railway e boundary.
- way_area
- Valore calcolato da Osm2pgsql. Descrive la superficie ricoperta da un poligono.
Tabelle di dati grezzi
Queste tabelle contengono i dati grezzi letti dall' OSM-XML. Questi vengono usati da Osm2pgsql per l'importazione delle differenze. Normalmente è raro accedervi direttamente. Le tre tabelle corrispondono alle tre primitive OSM:
- planet_nodes
- planet_ways
- planet_rels
Tabelle PostGIS
Le tabelle PostGIS sono fornite per uso interno a PostGIS e vengono usate per la determinazione dei tipi geometrici delle colonne delle way. Anche queste tabelle non vengono quasi mai utilizzate direttamente:
- spatial_ref_sys
- geometry_columns
Link
Approfondimenti su diversi tipi di query all'hstore. La query “@>” ivi consigliata non è qui nominata, poiché Kai l'ha notata solo successivamente:
- http://lists.wikimedia.org/pipermail/maps-l/2010-May/000623.html
- http://lists.wikimedia.org/pipermail/maps-l/2010-May/000645.html
Discussione dei differenti stili di importazione con benchmark:
Progetto che utilizza data base hstore sul tool server Wikimedia:
- http://wiki.openstreetmap.org/wiki/Query-to-map
- http://toolserver.org/~kolossos/qtm2/queryinmap.php?BBOX=13.5333,50.95,13.9333,51.15&name=*&key=lit&value=yes&types=lines%7Careas
Documentazione su hstore e hstore-new (inglese)
- http://developer.postgresql.org/pgdocs/postgres/hstore.html
- http://pgfoundry.org/projects/hstore-new/
Una carta Mapnik che preleva i dati dal tool-server data base sopraddetto:
- http://toolserver.org/~osm/styles/?layers=B000T0FF0000F
- http://toolserver.org/~mazder/styles/surveillance/surveillance.xml
Una mappa mondiale in tedesco e una in cinese che riprendeono tutte le definizioni dal tag „name:de“ piuttostoche „name:zh“ della colonna hstore.
- http://toolserver.org/~osm/styles/?layers=0000F0FFB000F
- http://toolserver.org/~osm/styles/?layers=0000F0FF00B0F
Seconda parte del tutoriale