Loading data into PostGIS using OGR

  • Software: PostGIS 9,10,11
  • QGIS: 3.x
  • OGR: 1.x, 2.x

I’m assuming for this tutorial you’ve installed PostgreSQL/PostGIS somewhere either on your computer or on a server AND you’ve installed QGIS on your computer. For this tutorial I’m only going to upload a shapefile but the general idea is the same with any data.

If you are on a windows machine you can open the OSGEO4W cmd tool or if you’re on a Mac or Linux just open a terminal. Change your directory to the directory of the data. Just to make sure you have access to ogr/gdal type: ogr2ogr -v and you should see: 

ogr2ogr
 Usage: ogr2ogr [--help-general] [-skipfailures] [-append] [-update]
                [-select field_list] [-where restricted_where|@filename]
                [-progress] [-sql |@filename] [-dialect dialect]
                [-preserve_fid] [-fid FID] [-limit nb_features]
                [-spat xmin ymin xmax ymax] [-spat_srs srs_def] [-geomfield field]
                [-a_srs srs_def] [-t_srs srs_def] [-s_srs srs_def]
                [-f format_name] [-overwrite] [[-dsco NAME=VALUE] …]
                dst_datasource_name src_datasource_name
                [-lco NAME=VALUE] [-nln name] 
                [-nlt type|PROMOTE_TO_MULTI|CONVERT_TO_LINEAR|CONVERT_TO_CURVE]
                [-dim XY|XYZ|XYM|XYZM|layer_dim] [layer [layer …]]
The command works like this (with some explanation): 
ogr2ogr -f "PostgreSQL" -t_srs "EPSG:2274" PG:"host=host user=user dbname=database password=trustno1 schemas=schema" shapefile.shp -nln new_name_for_table

The Breakdown of the command is:

  • ogr2ogr – the command
  • -f “PostgreSQL” – the driver you need to push this into PostgreSQL
  • -t_srs “EPSG:2274” – In this case I wanted to project the data into an EPSG code of 2274. Know you data and what projection it is in and what you want it to be.
  • host=host – the computer holding the database. Can be an ip address
  • user=user – user in the database
  • dbname=database – database name
  • password=trustno1 – your password
  • schemas=schema – the schema you want to hold the data. You can use the Public Schema
  • Shapefile.shp – your data.
  • -nln – can be the new name for your table. For instance if you were loading a_terrible_file.shp you can rename it to gpspoints in the database.

Hopefully this helps a bit. As always the total sum of all knowledge is at https://www.gdal.org