IT:HowTo minutely hstore

From OpenStreetMap Wiki
(Redirected from IT:HowtoMinutelyHstore)
Jump to navigation Jump to search

broom

Questo articolo o sezione potrebbe contenere informazioni obsolete: Warning! Outdated! this Article is from January 2011 and presumably outdated. For more recent information please visit http://switch2osm.org/
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:

Discussione dei differenti stili di importazione con benchmark:

Progetto che utilizza data base hstore sul tool server Wikimedia:

Documentazione su hstore e hstore-new (inglese)

Una carta Mapnik che preleva i dati dal tool-server data base sopraddetto:

Una mappa mondiale in tedesco e una in cinese che riprendeono tutte le definizioni dal tag „name:de“ piuttostoche „name:zh“ della colonna hstore.

Seconda parte del tutoriale