ogr2ogr tricks

Postgres

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'"

To PostgreSQL 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

CSV

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

<OGRVRTDataSource>
    <OGRVRTLayer name="input">
        <SrcDataSource>input.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Longitude" y="Latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

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:

  1. https://github.com/dwtkns/gdal-cheat-sheet
  2. http://www.gdal.org/ogr2ogr.html
  3. http://geocomuna.wordpress.com/geo-acordeon/ (in spanish, but good)
  4. http://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet

5 thoughts on “ogr2ogr 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.

      http://gdal.org/java/

    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