Geo Tricks

Useful tools, 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" \

Import into specific PostgreSQL schema (default is public):

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

From PostgreSQL


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"):

    <OGRVRTLayer name="input">
        <GeometryField encoding="PointFromColumns" x="Longitude" y="Latitude"/>


ogr2ogr -f GeoJSON output.geojson input.vrt


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


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 st_astext(the_geom) FROM kommune4326 (notice, no key needed because it is a public table)

Writing data w. CartoDB SQL API INTO observations(the_geom,obs_id,ord) VALUES (ST_GeomFromText('POINT(12.59935%2055.66445)',4326),'2','0')&api_key=API_KEY


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

Other OGR2OGR Cheat sheets:

  3. (in spanish, but good)

5 thoughts on “Geo Tricks”

  1. Thank u kostas :)

    do u have any idea about how to implement HDFS filesystem in ogr2orgr??
    Is there any java api provided for this ogr2ogr? if it is so,kindly send me the reference link?

    1. I’m sorry, but I don’t know what you mean by you first question (implementing HDFS in ogr2ogr). Could you elaborate?

      Regarding your second question, there are Java bindings for the ogr2ogr library, meaning you can call GDAL (of which OGR is a module) from Java. Again, I haven’t tried it.

    2. aahhh vielen dank!ich bin astlouber unterstfctzer/mapper/fan von OSM aber nicht ste4ndig im wiki/forum/mailingliste unterwegs.perfekt wenn es eine zentrale stelle gibt, an der man sich OSM news reinpfeifen kann!danke nochma,ben

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.