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

QUANTUM GIS (mac/win) with OGD wien data

6 classes of district sizes in vienna OGD-wien

1. install qgis

easy on windows. on the mac install 3 additional packages

  • GDAL Complete (/Library/Frameworks/GDAL.framework/Versions…)
  • NumPy
  • matplotlib

2. get the data

go to data.wien.gv.at and get the link to the JSON-data
in QGIS click the button at the left „add WMS/WMTS Layer“
create a new layer (button „new“)
give it a nice name (ogd wien data) and past the url to url… ok
then choose the BEZIRKSGRENZEOGD-layer
to save it locally – right-click on the layer and save as…

2.1 filter data

right-click on the layer and go to filter… here you can filter with SQL-syntax. eg. LENGTH(„field_6“)>0. then all data matching that expression is shown on the map.

2.2 data links

import a csv-file (see additional comment on the bottum) with a key-column that matches to an existing data-layer. then go to the layer-properties and add a join on the joins-tab. now all columns of the csv-file can be used in this layer.

3. labels and colors

in the layer-options go to style

switch the option „singel symobl (?)“ to various options (play around a little bit) and choose FLAECHE as column value. (i created a new gray color schema for my map here – inverted)

the thickness of the lines can be changed in the „symbol“-button, then „simple content (?)“ – play a bit with thickness and border style…

the labels are edited via the „edit expression (?)“ button in the description tab. i choose the following value for the lable „“BEZ_RZ“ || ‚. Bezirk'“.
then i added a shadow and a background color to the label. thats it (and font as Lucida Blackletter)!

3.1 add a legend

this is not that fluent – go to this nice tutorial

Links

OGD-Data
Shape files for cities http://metro.teczno.com/
  imposm – shp-files
  osm.bz2 – just one large osm-file (db-convertion needed)
  osm-files http://download.geofabrik.de/
data overview http://en.wikipedia.org/wiki/List_of_GIS_data_sources

Tutorials
http://www.digital-geography.com/qgis-tutorial-8-openstreetmap-osm-import-und-export-mit-qgis/#.U5wvT41_ugU
http://manual.linfiniti.com/en/

Import
CSV-data http://www.qgistutorials.com/en/docs/importing_spreadsheets_csv.html
(important – the csv-descriptor-file, eg. test.csvt for test.csv data-file, has comma as seperator eg. string,integer,string)

GRASS GIS (windows) and STATISTIK AUSTRIA data

to use shape-files from STATISTIK AUSTRIA in GRASS GIS the following steps are requires

1. create your GIS-data project
create a new folder

create a new location
choose EPSG-code: 31287
for austria use upper 49.3 lower 46.3 left 9 rigth 17.3

start your GRASS
2. download data
i choose nuts3 regions
unzip the file 
3. import data
go to menu: file – import vector data – (first entry) [v.in.ogr]
source is file, format ESRI shapefile, choose the unzipped shape-.\statistik_austria_nuts3\STATISTIK_AUSTRIA_NUTS3.shp
click import
doing the same with this dataset:
STATISTIK_AUSTRIA_SIEDLUNGSEINHEITEN_2011_POINT.shp

now colorize the points
create a new column (because i had problems with adding the column directly with the v.color-method)
v.db.addcol map=STATISTIK_AUSTRIA_SIEDLUNGSEINHEITEN_2011_POINT@boehs columns=color varchar(250)

v.colors map=STATISTIK_AUSTRIA_SIEDLUNGSEINHEITEN_2011_POINT@boehs column=HWS rgb_column=color color=bgyr
(export png in the map window via the button save display to graphic file)

Dropbox uploader script for unix

very nice tool to upload files to dropbox over dropbox-api.

https://github.com/andreafabrizi/Dropbox-Uploader/blob/master/README.md

just register the app and you can upload data from the command line.

The syntax is quite simple:

./dropbox_uploader.sh COMMAND [PARAMETERS]…

to upload a file
./dropbox_uploader.sh UPLOAD /home/pi/myfile.txt Private/Test

to download the same file
./dropbox_uploader.sh DOWNLOAD Private/Test/myfile.txt /home/pi

send mails (gmail) from python on raspberry pi

create a text-file (eg. mail.py) and past the following into it:

import smtplib

fromaddr = ‚@gmail.com‘
toaddrs  = ‚@gmail.com‘

subject = ‚info sent by pi‘
text = ‚This is my first message from pi‘

msg = ‚Subject: %s\n\n%s‘ % (subject, text)

# Credentials
username = ‚@gmail.com‘
password = “

# The actual mail send
server = smtplib.SMTP(’smtp.gmail.com:587′)
server.starttls()
server.login(username,password)
server.sendmail(fromaddr, toaddrs, msg)
server.quit()

that’s it. now rund the script with python mail.py „this is a test“

OGD-data from wiener-linien to mysql

source files are
http://data.wien.gv.at/csv/wienerlinien-ogd-linien.csv
http://data.wien.gv.at/csv/wienerlinien-ogd-haltestellen.csv
http://data.wien.gv.at/csv/wienerlinien-ogd-steige.csv

download all the files with wget  and make a short check…
let’s start with the steige-file, containing all stations/platforms.

make a head wienerlinien-ogd-steige.csv to check the header and the first 9 lines/datasets.

log into mysql database (mysql -u -p) and paste the following sql-code

use test;

create table 
wienerlinien_ogd_steige
(
STEIG_ID INT
, FK_LINIEN_ID INT
, FK_HALTESTELLEN_ID INT
, RICHTUNG VARCHAR(1)
, REIHENFOLGE SMALLINT
, RBL_NUMMER VARCHAR(64)
, BEREICH SMALLINT
, STEIG VARCHAR(128)
, STEIG_POINT POINT
, STAND DATETIME
);

now import data using that code:

LOAD DATA
INFILE ‚/wienerlinien-ogd-steige.csv‘
INTO TABLE wienerlinien_ogd_steige
FIELDS
TERMINATED BY ‚;‘
OPTIONALLY ENCLOSED BY ‚“‚
LINES TERMINATED BY ‚\r\n‘
IGNORE 1 LINES
(
  STEIG_ID
, FK_LINIEN_ID
, FK_HALTESTELLEN_ID
, RICHTUNG
, REIHENFOLGE
, RBL_NUMMER
, BEREICH
, STEIG
, @LAT
, @LON
, STAND
)
SET STEIG_POINT = POINT(@LAT,@LON)
;

that should result in the following output
Query OK, 6052 rows affected, 557 warnings (1.56 sec)

Records: 6052  Deleted: 0  Skipped: 0  Warnings: 557
we have some errors, because some rows contain empty strings for integer-data eg.
;““;““;““;
when you write SHOW WARNINGS; after the import you get the first warnings. eg:
| Warning | 1366 | Incorrect integer value: “ for column ‚BEREICH‘ at row 4331 |

compare the inserted rows (6052) with the rows of the source file: wc -l minus the header row.

now let’s view the data

SELECT * FROM wienerlinien_ogd_steige;

SELECT * FROM wienerlinien_ogd_steige
WHERE STEIG_ID = 215037496;

to extract the geo-data do the following

SELECT
  STEIG_ID
, STEIG
, X(STEIG_POINT) AS X 
, Y(STEIG_POINT) AS Y 
FROM wienerlinien_ogd_steige
WHERE STEIG LIKE ‚%13A%‘;
SELECT
  STEIG_ID
, FK_LINIEN_ID
, FK_HALTESTELLEN_ID
, RICHTUNG
, REIHENFOLGE
, RBL_NUMMER
, BEREICH
, STEIG
, X(STEIG_POINT) AS X 
, Y(STEIG_POINT) AS Y 
, STAND
FROM wienerlinien_ogd_steige

WHERE STEIG LIKE ‚%13A%‘;


now we note the number of rows for to compare with the next import
SELECT COUNT(*), STAND FROM wienerlinien_ogd_steige GROUP BY STAND;
+———-+———————+
| COUNT(*) | STAND               |
+———-+———————+
|     3026 | 2013-08-12 10:18:58 |
+———-+———————+

Visualization

Types of diagrams
Tree map – http://en.wikipedia.org/wiki/Tree_map
Heat map – http://en.wikipedia.org/wiki/Heat_map
Bubble chart – http://en.wikipedia.org/wiki/Bubble_chart
Streamgraph – http://en.wikipedia.org/wiki/Streamgraph
Parallel coordinates – http://en.wikipedia.org/wiki/Parallel_coordinates
Chord diagram

Tools
R-Project for Statistical Computing

D3

Links
http://www.davidmccandless.com/

treemap of financial capital market data http://www.marketwatch.com/tools/stockresearch/marketmap

all kinds of graphics 
http://driven-by-data.net/
http://bost.ocks.org/mike/

Blogs
http://flowingdata.com/

source c’t 2013/17