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:
- append new geometry to an existing PostGIS table from a source Shapefile
- specifiy the source Shapefile projection, because sometimes ogr2ogr guesses wrong
- define which attribute fields map to which PostGIS table fields
- specify a non default PostgreSQL schema, in my case the schema=geodata
- 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 (http://www.gdal.org/ogr2ogr.html). 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