PostgreSQL Geopackage – Uploading Geopackage Contents to PostgreSQL

geopackageogrogr2ogrpostgispostgresql

As a bit of background, we are looking to transition away from the use of shapefiles. We've already started using Geopackages for desktop work (QGIS / ArcGIS) but often have a requirement to upload data to our PostgreSQL databases.

I'm aware that you are able to upload Geopackage content to Postgres using QGIS but wondering if there are any commandline utilities similar to shp2pgsql for uploading geopackages or tables in geopackages to Postgres? I'm assuming possibly ogr2ogr but can't find the documentation.

Best Answer

This tutorial shows how to import geopackges into postgres with ogr2ogr like:

ogr2ogr -f PostgreSQL "PG:dbname=gadm" DNK_adm.gpkg

Note importing into PostgreSQL like this will convert the case of the field names in your GeoPackage data to lowercase in the PostgreSQL table. To maintain case you would need to use the lco option like:

ogr2ogr -f PostgreSQL "PG:dbname=gadm" DNK_adm.gpkg -lco LAUNDER=NO

Related Solutions

QGIS – How to Fix Shapefile’s Character Encoding Using ogr2ogr on the Command Line

Quoting from the GDAL documentation for the Esri Shapefile driver:

An attempt is made to read the LDID/codepage setting from the .dbf file and use it to translate string fields to UTF-8 on read, and back when writing. LDID "87 / 0x57" is treated as ISO8859_1 which may not be appropriate. The SHAPE_ENCODING configuration option may be used to override the encoding interpretation of the shapefile with any encoding supported by CPLRecode or to "" to avoid any recoding. (Recoding support is new for GDAL/OGR 1.9.0)

Depending on which GDAL/OGR version you are using, ogr2ogr may be trying to translate your data to UTF-8 or not doing nothing at all.

So you would do either:

ogr2ogr --config SHAPE_ENCODING "UTF-8" -f "MySql" MySql:"basemap,host=127.0.0.1,user=myUser,password=myPass,port=3306" -lco engine=MYISAM "C:/path/to/data/tl_2012_us_county.shp" -nln county -nlt "geometry" -s_srs EPSG:4269 -t_srs EPSG:3857

ogr2ogr --config SHAPE_ENCODING "" -f "MySql" MySql:"basemap,host=127.0.0.1,user=myUser,password=myPass,port=3306" -lco engine=MYISAM "C:/path/to/data/tl_2012_us_county.shp" -nln county -nlt "geometry" -s_srs EPSG:4269 -t_srs EPSG:3857

Finally, check that your MySQL database is using UTF-8 and not Latin-1.

PostgreSQL – Uploading File Geodatabase to Database Schema

You can add into your connection string the option ACTIVE_SCHEMA=your_schema

ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=GIS user=postgres password=xxxxxx ACTIVE_SCHEMA=your_schema" "C:\test.gdb

All other options can be found in the driver documentation: http://www.gdal.org/drv_pg.html

Best Answer

Related Solutions

QGIS – How to Fix Shapefile’s Character Encoding Using ogr2ogr on the Command Line

PostgreSQL – Uploading File Geodatabase to Database Schema

Related Question