Geo Tricks – SKIPPERKONGEN

Useful tools

prj2epsg.org, convert .proj files to epsg codes; just paste in the file contents
openstreetmap-wkt-playground, visualise WKT geometries on an OpenStreetMap background

org2ogr tricks

To PostgreSQL

From OpenStreetMap PBF format:

# database called "osm"
ogr2ogr -f PostgreSQL "PG:dbname=osm" planet.osm.pbf

From GeoJSON:

ogr2ogr -f "PostgreSQL" \
PG:"host=localhost user=xxx password=xxx dbname=xxx" \
input.geojson

Import into specific PostgreSQL schema (default is public):

ogr2ogr -f PostgreSQL \
PG:"host=localhost user=xxx dbname=xxx" \
-lco SCHEMA=xxx \
input.geojson

From PostgreSQL

To GeoJSON:

ogr2ogr -f "GeoJSON" \
output.geojson \
PG:"host=localhost user=xxx password=xxx dbname=xxx" \
-sql "select * from input_table where foo='bar'"

From CSV

For conversion from CSV, first create a VRT file (e.g. called “input.vrt”):


    
        input.csv
        wkbPoint
        WGS84

CSV to GeoJSON:

ogr2ogr -f GeoJSON output.geojson input.vrt

Reprojection

Reproject shapefile from Lat/Long to “Google Mercator”:

ogr2ogr output.shp -s_srs 'EPSG:4326 '-t_srs 'EPSG:3857' input.shp

Public data recipes

Example: Import Danish public data into CartoDB

1. Download zip with shapefiles of administrative borders for Denmark (it’s in EPSG:25832)

2. Unzip it

unzip DAGI500.zip

Let’s focus on the municipalities…

3. Reproject KOMMUNE.shp to EPSG:4326 using ogr2ogr:

ogr2ogr kommune4326.shp -t_srs "EPSG:4326" KOMMUNE.shp

4. Now we can import it using the web interface for CartoDB.

Reading data w. CartoDB SQL API

http://skipperkongen.cartodb.com/api/v2/sql?q=SELECT st_astext(the_geom) FROM kommune4326 (notice, no key needed because it is a public table)

Writing data w. CartoDB SQL API

http://skipperkongen.cartodb.com/api/v2/sql?q=INSERT INTO observations(the_geom,obs_id,ord) VALUES (ST_GeomFromText(‘POINT(12.59935%2055.66445)’,4326),’2′,’0′)&api_key=API_KEY

Other

Very good post on using GIS with Python, Shapely, and Fiona.

Other OGR2OGR Cheat sheets: