Import data into PostGIS with ogr2ogr#

ogrIntoPostGIS.md

Generally I can use QGIS and its DBManager to import data into a PostGIS database. Sometimes that doesn't work perfectly. ogr2ogr can help though. Here are a few approaches to getting data into PostGIS with ogr2ogr.

This is probably the most basic approach:

 ogr2ogr -f "PostgreSQL" PG:"dbname=<db name> user=<username> password=<password> host=<host> port=<port #>" input.geojson -nln schema.table

Running this little command seems to work if you have need to specify the geometry type (Source):

ogr2ogr -overwrite -f "PostgreSQL" PG:"host=<host> user=<username> dbname=<db name> password=<password> port=<port #>" "C:\some.gdb" "<feature class>" -nln <new table name> -nlt <geometry type>

Sometimes you have mixed single- and multi-part geometries in a data source. This just imports everything as a multi-geometry:

ogr2ogr -overwrite -f "PostgreSQL" PG:"host=<host> user=<username> dbname=<db name> password=<password> port=<port #>" -s_srs EPSG:4326 -t_srs EPSG:2264 -lco GEOMETRY_NAME=geom -overwrite <from.file> -nln schema.table -nlt PROMOTE_TO_MULTI

And sometimes you get a weird geometry like a MultiSurface so you have to do a little extra to specify a geometry PostGIS can work with:

ogr2ogr -overwrite -f "PostgreSQL" PG:"host=<host> user=<username> dbname=<db name> password=<password> port=<port #>" -lco GEOMETRY_NAME=geom -overwrite <from.file> -nln schema.table -nlt <GEOMETRY_TYPE>

QGIS has trouble reading non-Spatialite and non-GeoPackage SQLite databases. Maybe there's a way to do it, but I can't figure it out. You could convert your SQLite database to a PostGIS table though.

ogr2ogr -f "PostgreSQL" PG:"host=<host> user=<username> dbname=<db name> password=<password> port=<port #>" <sqlite_db>.sqlite -sql "SELECT * FROM <table>" -dialect SQLite -nln <schema>.<table>

Setting the output as a result of a query of the input dataset.

ogr2ogr -overwrite -f "PostgreSQL" PG:"host=<host> user=<username> dbname=<db name> password=<password> port=<port #>" source_file.shp -dialect sqlite -sql "SELECT * FROM source_file" -nln <schema>.<table>