Wednesday, November 11, 2015

How to import Spatialite into PostGIS using ogr2ogr

GOAL: Import a Spatialite table into an existing PostGIS table appending (inserting) new rows using ogr2ogr. The trick is we want to map the fields to import to the correct corresponding PostGIS columns.

 First get the list of Spatialite fields using ogrinfo

 ogrinfo somedb.sqlite sqliteTableName


INFO: Open of `..\sqlite\socgen-db-final.sqlite'
      using driver `SQLite' successful.

Layer name: e00_poly_join
Geometry: Polygon
Feature Count: 60
Extent: (-168613.719162, 5983831.402051) - (-168508.270008, 5983961.187764)
Layer SRS WKT:
PROJCS["WGS 84 / Pseudo-Mercator",
    GEOGCS["WGS 84",
        DATUM["WGS_1984",
            SPHEROID["WGS 84",6378137,298.257223563,
                AUTHORITY["EPSG","7030"]],
            AUTHORITY["EPSG","6326"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AXIS["Latitude",NORTH],
        AXIS["Longitude",EAST],
        AUTHORITY["EPSG","4326"]],
    PROJECTION["Mercator_1SP"],
    PARAMETER["central_meridian",0],
    PARAMETER["scale_factor",1],
    PARAMETER["false_easting",0],
    PARAMETER["false_northing",0],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["X",EAST],
    AXIS["Y",NORTH],
    EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0
    AUTHORITY["EPSG","3857"]]
FID Column = PK_UID
Geometry Column = geom
Text: String (0.0)
spacetype: String (0.0)
building: Integer (0.0)
floor: Integer (0.0)
type_id: Integer (0.0)
tag: String (0.0)
name: String (0.0)

Now you see we have a total of 9 columns
 using the -fieldmap option of ogr2ogr we can only input 7 of those fields.. The FID column and geometry column do NOT count towards the -fieldmap list

-fieldmap 7,4,6,9,10,3,2

This means  7 = field Text,  4 = field spacetype, 6 = building, 9=floor, 10=type_id, 3=tag, 2=name

the value 7 for example represents the column position in your PostgreSQL DB where you want to import INTO using the Spatialite column called "Text" in this example.

Counting the column number in Postgresql is also hard.  You start at 0 NOT including the primary key and excluding the geometry column where ever it is the the column order.