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.

Advertisements

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 basemap.at) use this code for basemap_0
var basemap_0 = L.tileLayer('https://{s}.wien.gv.at/basemap/bmapgrau/normal/google3857/{z}/{y}/{x}.png', {
 attribution: additional_attrib + 'Map tiles: <a href="http://www.basemap.at" target="_blank">www.basemap.at</a>, <a href="http://creativecommons.org/licenses/by/3.0/at/deed.de" 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.at': 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 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

QGIS and OSM

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 geofabrik.de: 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. http://download.geofabrik.de/europe.html

Printing OSM-Tiles

  • Scales for correct printing of osm-tiles (used with TileMapScaleLevels) with print-composer
7151
14302
28604
57208
114416
228832
457664
915328
1830656
3661312
7322624
14645248
29290496
58580992 …
  • DPI: i used 150 to 180 dpi for a readable result