OGR2OGR examples for Spatialite, PostGIS, Shapefiles

I'm constantly trying to avoid Shapefiles but seem to only fail because some software GPS or GIS only supports Shapefiles as the interchange format.  Hence bye bye table names longer than 10 Characters (very annoying !) when going from a DB to Shapefile.

Here are some examples that I have recently used in doing some batch conversions here and there between PostGIS, Spatialite and Shapefiles.

My Setup:  Windows 7 Pro, 32 Bit,  ogr2ogr.exe  version GDAL 1.9.2, released 2012/10/08 (Installed using OSGeo4W  installer)

Example how to get version of ogr2ogr on my machine, at the command prompt.  Note your path to ogr2ogr is most likely not where I have it.

> c:/OSGeo4W/bin/ogr2ogr.exe/ogr2ogr.exe --version
  version GDAL 1.9.2, released 2012/10/08

So now the examples

Example 1.  upload all Spatialite tables to an existing PostGIS DB and existing tables with same name as the Spatialite table names

 c:/OSGeo4W/bin/ogr2ogr.exe/ogr2ogr.exe -append -lco GEOMETRY_NAME=geom -lco SCHEMA=geodata -f "PostgreSQL" PG:"host=localhost port=5432 user=username dbname=nameOfDatabase password=yourDbPassword" -a_srs "EPSG:31468" gisdata.sqlite

-append  appends data to existing tables
-lco GEOMETRY_NAME=geom   says the name of the geometry column should be "geom"
-lco SCHEMA=geodata     says the Postgis schema name is "geodata"
-f "PostgreSQL"    says destination is Postgresql Postgis format
PG:"...."   says connection information
-a_srs "EPSG:31468"   says the coordinate system is EPSG code 31468
gisdata.sqlite   this is the source Spatialite file with the tables to import into Postgis

if you just want to import the Spatialite tables into an existing PostGIS database and have the tables automagically auto created for you just ommit the -append   option above.


Example 2.  How to export all Spatialite tables into individual Shapefiles in an empty folder that exists.

c:/OSGeo4W/bin/ogr2ogr.exe -f "ESRI Shapefile" shp geodata.sqlite -dsco SPATIALITE=yes

shp      this is the name of the folder where the newly batch created shapefiles will go.
geodata.sqlite   this is the source Spatialite file with the tables to export to shapefiles
-dsco SPATIALITE=yes   says the input is in Spatialite format


in the end all the Spatialite tables will be exported to individual shapefiles (name = tableName) into the folder "shp"  the folder name can of course be whatever, where ever you want it on your machine. 


Example 3.  Convert a Spatialite file and all its tables into a new Sqlite table where the geometry is stored as Well Known Text

c:/OSGeo4W/bin/ogr2ogr.exe -f SQlite output.sqlite input_spatialdb.sqlite -dsco SPATIALITE=no -lco FORMAT=WKT


-f  SQlite    says the desitination format is SQlite
output.sqlite is the name of the newly created Sqlite database (must not exists before running)
input_spatialdb.sqlite    is the name of the Spatialite db with the source data
-dsco SPATIALITE=no   say destination output Sqlite is not Spatialite
-lco FORMAT=WKT    says output format of geometry columns data is Well Known Text format  (note: this could also be WKB for Well Known Binary)


So there you have it some conversion, batch and not so batch for you moving data between Spatialite, PostGIS and Shapefiles.

hope it helps you out.
cheers
Michael