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

Oracle XML datahandling (import etc)

importing XML-files into an oracle database

create a table with a CLOB-column
CREATE TABLE CLOBTEST
(
  POID    NUMBER,
  POCLOB  CLOB
)

POID can/should be autoincrement…

using sqlloadr to load the XML-files into the table
with the unix find-command create a table with all your XML-files you want to import (see example below). the XML.ctl file looks like this:
LOAD DATA 
INFILE *
INTO TABLE CLOBtest TRUNCATE
FIELDS TERMINATED BY ‚,‘
(
    POID CHAR(10),
    clob_filename FILLER CHAR(100),
    POCLOB  LOBFILE(clob_filename) TERMINATED BY EOF
)
–sqlldr schema@db XML.ctl

–find /home/data/ -name *.XML
BEGINDATA
1,/home/data/DOC00001.XML
2,/home/data/DOC00001.XML
3,/home/data/DOC00001.XML

query to extract XML-data

the hard part is to query the XML-tags (elements, values etc) in the CLOB-column. here is an example. first convert the CLOB columns into a XMLType and then use extractvalue or other functions (extract…) to query your data

SELECT extractvalue(xmldata, ‚/700/710/711/‘) AS col1
FROM 
    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)
;
to query sub-tables in the XML-data
SELECT 

    t.id
    , li.ctry
FROM 

    SELECT XMLType(poClob) AS xmldata
    FROM CLOBtest
)    
p,
XMLTable
    (
        ‚/*‘ PASSING p.xmldata
        COLUMNS 
            id VARCHAR2(28) PATH ‚/product/@id‘,
            ctry XMLType PATH ‚/product/soldctry‘
    ) t
    ,
XMLTable
    (
        ‚ctry‘ PASSING t.ctry
        COLUMNS 
            ctry        VARCHAR2(2)  PATH ‚/‘
    ) li
ORDER BY
    t.id
    , li.ctry
;

the p-table is the conversion of the CLOB-datatype into an XML-document.
the t-table extracts the ID of the product-element and the country/countries inside the product.
the li-table extracts all country-names of the product.
the result is a list with all countries of a product.
ID CTRY
15A1 AT
15A1 BE
15A1 BG
15A1 CH
15A1 CY
01A2 AT
01A2 BE