Friday, May 8, 2015

ogr2ogr Append new Shapefile to existing PostGIS table

Ogr2ogr is simply powerful.  It is like salt in cooking you use it everyday.

The task how to import a Shapefile into a PostGIS table that exists without overwriting the existing table and simply append some new geometries and match the attribute fields from the Shapefile to the PostGIS table fields.

There are lots of things I want to do in this import so lets list off what exactly:

  1. append new geometry to an existing PostGIS table from a source Shapefile
  2. specifiy the source Shapefile projection, because sometimes ogr2ogr guesses wrong
  3. define which attribute fields map to which PostGIS table fields
  4. specify a non default PostgreSQL schema, in my case the schema=geodata
  5. specify the geometry type for output destination as MultiLineString even though my input Shapefile is  a simple Linestring

Here is the monster long command line arguments for OGR2OGR:

ogr2ogr -update -append -fieldmap -1,-1,2 -a_srs EPSG:900913 -nlt MULTILINESTRING -lco "SCHEMA=geodata" -f PostgreSQL "PG:host=localhost port=5432 user=postgres dbname=mydb password=secret" -nln geodata.table_name shapefileName.shp

What it all says broken down.  First we specify -update and -append telling ogr2ogr that we want to add new data to an existing table.

 Next we map the fields of the Shapefile to the fields in the PostGIS table (  Please read the documentation but the quick rundown is that the fieldmap takes a comma separated list which represents each field in your source Shapefile.  The Shapefile in my case has 3 fields.  -1 means skip this field so we skip fields 1 and 2 and field 3 is what we want to import into the position 2 field of our destination PostGIS table counting from 0 as the first, 1 is the second and 2 is the third field.

-a_srs EPSG:900913 tells ogr2ogr specifically to use this projection for our source Shapefile
-nlt MULTILINESTRING   imports our geom as multilinestring
-lco "SCHEMA=geodata"   specifies our user defined PostgreSQL schema where the table will go
-f PostgreSQL is our long connection string to connect to the DB
-nln geodata.table_name specifies explicitly the name of the existing table we want to import into

Lastly comes our ShapefileName.shp  which is in the same folder that we currently are located in when running this command.

Wheew   now that is lot for one line of import but it works :)  yes


1 comment:

  1. Top stuff - spent an age trying to get the syntax correct for this. My script is slightly different in that it truncates the table first and then appends the new data so that my views don't complain.