postgresql with OSM on raspberryPI

follow these instruction (german) to install all you need for your own osm-server

install postgresql

first update

  sudo apt-get update
then install the package; about 21MB additional space will be used
  sudo apt-get install postgresql
after that the server should report
  [ ok ] Starting PostgreSQL 9.1 database server: main.
here are some steps to configure the server
install tools for osm
install imposm git postgis and postgresql-9.1-postgis
  sudo apt-get install postgis imposm git postgresql-9.1-postgisnow create a source directory and download osm data
  mkdir ~/src
  cd ~/src && wget http://download.geofabrik.de/europe/slovenia-latest.osm.pbfdownload the following tool, needed later
cd ~/src && git clone https://github.com/mapbox/osm-bright.git

 

run the following scritp as postgres-user, so first create a sh-file
  set -xe
  createuser –no-superuser –no-createrole –createdb osm
  createdb -E UTF8 -O osm osm
  createlang plpgsql osm
  psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
  psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
  psql -d osm -f /usr/share/python-imposm/900913.sql
  echo „ALTER TABLE geometry_columns OWNER TO osm; ALTER TABLE spatial_ref_sys OWNER TO osm;“ |psql -d osm
  echo „ALTER USER osm WITH PASSWORD ‚osm‘;“ |psql -d osm
  set +x
then login as postgres
  sudo su postgres
  sh ./create_db.sh
  exit

now add the following row to your postgre config and restart postgre
  sudo nano /etc/postgresql/9.1/main/pg_hba.conf
  host osm osm 127.0.0.1/32 md5
  sudo /etc/init.d/postgresql restart

to import your first data use (more infos here)
imposm –-read –-write –-optimize -h localhost -p 5432 -d osm -m ~/src/osm-bright/imposm-mapping.py ~/src/slovenia-latest.osm.pbf
this will need quite a time – in my case
  imposm took 2 h 40m 44s

in the mean time you can check your database for new created tables after the message ## dropping/creating tables

in psql you can view your tables with (\connect osm;)

get the version of postgis

SELECT PostGIS_full_version();

postgis_full_version

——————————————————————————————————-

POSTGIS=“1.5.3″ GEOS=“3.3.3-CAPI-1.7.4″ PROJ=“Rel. 4.7.1, 23 September 2009″ LIBXML=“2.8.0″ USE_STATS

(1 row)

  SELECT table_name FROM information_schema.tables WHERE table_schema = ‚public‘;

      table_name
————————–
 spatial_ref_sys
 geometry_columns
 geography_columns
 osm_new_mainroads
 osm_new_buildings
 osm_new_amenities
 osm_new_aeroways
 osm_new_motorways
 osm_new_transport_points
 osm_new_landusages
 osm_new_waterways
 osm_new_railways
 osm_new_waterareas
 osm_new_places
 osm_new_admin
 osm_new_barrierways
 osm_new_minorroads
 osm_new_barrierpoints
 osm_new_roads

get column names from a table
  SELECT column_name FROM information_schema.columns WHERE table_name=’osm_new_roads‘;

 column_name
————-
 class
 type
 access
 z_order
 layer
 oneway
 tunnel
 ref
 bridge
 name
 geometry
 osm_id
 id

and easy to find different types
  SELECT type, COUNT(*)  FROM osm_new_roads GROUP BY type ORDER BY type;

      type      | count
—————-+——-
 bridleway      |     1
 cycleway       |    38
 footway        |   855
 living_street  |    27
 motorway       |   171
 motorway_link  |   247
 path           |   318
 pedestrian     |   139
 primary        |   283
 primary_link   |    26
 rail           |   128
 residential    |  2683
 road           |     5
 secondary      |   581
 secondary_link |    12
 service        |   593
 steps          |    60
 tertiary       |   888
 tertiary_link  |     2
 track          |  1820
 trunk          |    47
 trunk_link     |    47
 unclassified   |  1397
(23 rows)

after the import finished turn your *new* tables to operational tables – WARNING old data will be lost!
  imposm –deploy-production-tables -d osm

lets create tiles

go to the folder ~/src/osm-bright and rename
  mv configure.py.sample configure.py

edit the config file
config[„importer“] = „imposm“
config[„postgis“][„host“]     = „localhost“
config[„postgis“][„port“]     = „5432“
config[„postgis“][„dbname“]   = „osm“
config[„postgis“][„user“]     = „osm“
config[„postgis“][„password“] = „osm“
download additional files to ~/src/osm-bright (coastline has ~310MB, shoreline ~45MB, 10m-land 5MB)

  wget http://tilemill-data.s3.amazonaws.com/osm/coastline-good.zip
wget http://tilemill-data.s3.amazonaws.com/osm/shoreline_300.zip
wget http://mapbox-geodata.s3.amazonaws.com/natural-earth-1.3.0/physical/10m-land.zip

create this dir
  mkdir ~/Documents/MapBox/project

psql

get server version
  select version();
PostgreSQL 9.1.14 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Debian 4.6.3-14+rpi1) 4.6.3, 32-bit
  CREATE DATABASE mydb;
response: CREATE DATABASE
drop a database
  DROP DATABASE mydb;
  CREATE TABLE account(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
  );
as you see, squences, keys and uniques will be generated automatically
drop table also dropps all autogenerated objects
get tablenames
  SELECT table_name FROM information_schema.tables WHERE table_schema = ‚public‘;useful documentation to access postgres from external device (eg qgis…)
important commands/files for postgre
config file
  sudo nano /etc/postgresql/9.1/main/pg_hba.conf
restart server
  sudo /etc/init.d/postgresql restart
enter server with psql
  psql -U postgres
exit with CTRL-D
show data align after the query
  -S

manual installation of postGIS

download from http://postgis.net/source
sudo apt-get install libpq-dev libproj-dev postgresql-server-dev-9.1
install geos and gdal – see http://northwestspatial.com/wp/?p=340
download source
./configure
sudo make install
test it with
geos-config –version
gdal-config –version
then update apt-get
sudo ldconfig
download source for postGIS and

./configure –with-topology –with-raster
sudo make install
restart postgresql
sudo service postgresql restart

use this documentation to install postGIS to postgresql
http://postgis.net/docs/manual-2.1/postgis_installation.html#install_short_version

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s