problem exporting data with ogr2ogr

i wanted to export a geojson file from my table „GeoData4326“ (with quotes) postgis postgres database with ogr2ogr

ogr2ogr -f „GeoJSON“ export.json PG:“host=localhost dbname=mydb user=myuser password=12341234 „\“GeoData4326\““

i got the following error:

ERROR 1: No field definitions found for ‚“GeoData4326″‚, is it a table?
FAILURE: Couldn’t fetch requested layer ‚“GeoData4326″‚!

my workaround is: create a view to this table without quotes on the name:

create or replace view GeoData4326 as SELECT * FROM „GeoData4326“;

now the export should work.


comparing 3 projections based on ETRS-LAEA

Lambert azimuthal equal-area projection (LAEA) accurately represents area in all regions of the sphere, but it does not accurately represent angles (based on the European Terrestrial Reference System).

ETRS89 ETRS-LAEA, EPSG:3035 wikipedia
WGS 84 EPSG 4326
WGS84, EPSG:4326 wikipedia
WGS 84 Pseudo Mercator EPSG 3857
WGS84 Pseudo Mercator, EPSG:3857

qgis2leaf plugin for qgis

this plugin creates a nice webpage out of your layer in QGIS within some seconds!

after installing the plugin qgis2leaf (and with a prepared maps with some vector-layers) click the new button „Exports a QGIS Projetc…“

  • Press „Get Layer“. All layers are loaded to the list-box
  • Select all layers you want to display in your webpage
  • Click all checkboxes you need for your webpage
    • Create cluster clusters all points with the number of points per cluster
  • Select one or more basemaps. If you want to change your basemap to a custom map (eg use this code for basemap_0
var basemap_0 = L.tileLayer('https://{s}{z}/{y}/{x}.png', {
 attribution: additional_attrib + 'Map tiles: <a href="" target="_blank"></a>, <a href="" target="_blank">CC-BY 3.0</a>',
 subdomains: ["maps","maps1", "maps2", "maps3"],});

// at the bottom of the html replace the naming of the basemap_0
 '': basemap_0,
  • After pressing OK your browser starts with your map. Every OK-Click created a new folder with date and time inside your project folder.

QGIS import and export GPX

import is simple – just open the gpx-file with double click in the qgis-browser

export (lines)

  • right-click on the layer
  • save as…
    • Format: GPS eXchange Format [GPX]
    • Save as …
    • CRS: Selected CRS (coordinate reference system)
    • (do not „Add saved file to map“)
    • OK

export (points)

if you want to export points to GPX and get the following error – welcome 🙂

Export to vector file failed.
Error: creation of layer failed (OGR error:Geometry type of `Multi Point' not supported in GPX.)

i found this bug-fix and my workaround is to use the following script from the toolbox (menu Processing – Toolbox (on the bottom „Advanced Interface“))

  • GDAL/OGR – OGR Conversion – Conversion format

i added these additional commands to „creation options“:

GPX_USE_EXTENSIONS=YES -explodecollections
processing.runalg("gdalogr:convertformat","/Users/me/QGIS/test.shp",16,"-dsco GPX_USE_EXTENSIONS=YES -explodecollections","/Users/me/QGIS/test.gpx")

export the gpx file as a script via python run the following in your python-console

import processing
processing.runalg("gdalogr:convertformat","/Users/me/QGIS/test.shp",16,"-dsco GPX_USE_EXTENSIONS=YES -explodecollections","/Users/me/QGIS/test.gpx")


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 the following tool, needed later
cd ~/src && git clone


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 ./

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 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/ ~/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=“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‘;


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


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

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)


create this dir
  mkdir ~/Documents/MapBox/project


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
drop a database
  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

manual installation of postGIS

download from
sudo apt-get install libpq-dev libproj-dev postgresql-server-dev-9.1
install geos and gdal – see
download source
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


Download Data from OSM

1. Download
in the QGIS menu go to Vector – OpenStreetMap – Download Data and define your data rectangular (a little help from that OpenLayers mouse position sample) or zoom to a location and choose „from layer“. define an output file.
2. Convert to db
go to same menu entry -> Import Topology from XML and define an output file. After converting the osm to db-file you can view the content with DB Manager (Menu: Database – DB Manager – DB Manager).
3. from db to QGIS
then use the menu entry „Export OpenStreetMap Topology to SpatialLite“. set input db-file. press the button „Load from DB“ to get all Tags check all Tags you want to import OK geofabrik-pbf/osm Download data from download pbf (direct import to qgis, but less attribute details) or osm.bz2 (zipped osm-file). Unzip the bz2 and open it. geofabrik-shp Download zipped shp-files and use them directly in QGIS.

Printing OSM-Tiles

  • Scales for correct printing of osm-tiles (used with TileMapScaleLevels) with print-composer
58580992 …
  • DPI: i used 150 to 180 dpi for a readable result