Getting spatial data into PostGIS

There are surprisingly numerous ways to get data into PostGIS for free, here are my top 3.

1. GDAL – using command line
2. QGIS – database manager
3. QGIS – drag and drop

GDAL:

Lets assume that you have a SHP that you want to upload into PostGIS.

ogr2ogr -f PostgreSQL PG:"host='localhost' port='5432'dbname=mydatabase user=myusername password=mypassword" /path/to/yourfile.shp 

The SHP would be uploaded into the public schema with a tabled called yourfile . This a bit simplistic so lets dive a bit deeper

ogr2ogr -a_srs "EPSG:27700" -f "PostgreSQL" --config PG_USE_COPY YES PG:"host=localhost port=5432 dbname=mydatabase user=myusername password=mypassword" /path/to/yourfile.shp -overwrite -nln schema_x.table_y -nlt POLYGON

Lets review the second example:

• a_srs “EPSG:27700” — make sure the we assign the British National Grid to the data being loaded into PostGIS
• config PG_USE_COPY YES — copies data into PostGIS a lot faster using the COPY command
• overwrite — overwrite the existing data/table in PostGIS. This is perfect is the you are the only owner/user of the table. Effectively the exisitng data table is dropped and recreated, but in doing so removes any other users/roles assigned to that table. There is also a danger in this approach just in case the column names change, as this will affect any styling you have against the data.
• nln (new layer name) schema_x.table_y — upload the data to a specific schema and table in PostGIS. It's good practice NOT to just upload data into the public schema.
• nlt (new layer type)POLYGON — be careful with this one, just in case the SHP file that you are loading has MULTIPOLYGONs in it.

If you want to upload data and retain user/role permissions then you need to run an ogrinfo/ SQL statement that will truncate the PostGIS table first, and then use a GDAL command to upload the new data. Beyond the scope of this post but let me know if you would like me to write a post up about this.

QGIS:

There are two methods (actually there are quite a few) in QGIS that can be used to get data into PostGIS but these are the easiest to be starting out with.

Data Manager:

For this to work you first need to make sure that in the browser panel you have a PostGIS connection added.

In the QGIS make sure that you have the data you want to load into PostGIS added to the map. In the menu bar (at the top of QQGIS) go to database and choose DB Manager. A new window will appear on screen.

Click on the PostGIS icon on the left and then choose the PostGIS instance that you want to upload data into.

Now click on the IMPORT LAYER/FILE icon at the top of the DB manager window:

Set the INPUT layer to in uploaded, set the OUTPUT TABLE schema and table name. Remember that best practice for database naming is always to keep your schema and table names in lowercase, and replace whitespaces with underscores.

Sometimes it is necessary to set the source and target SRID, just in the case the projection of the data you want to load is different to how you want it to be store.

I always check ‘convert field names to lowercase’ and ‘create spatial index’.

Click OK and fingers crossed QGIS gives you a successful upload message.

DRAG AND DROP:

For this to work you first need to make sure that in the browser panel you have a PostGIS connection added.

This is relatively easy to do but can be a bit fiddly.

In the QGIS make sure that you have the data you want to load into PostGIS added to the map, and you need to be able to see both panels LAYERS and BROWSER.

Click and hold on your layer, in LAYERS and then drag it into the schema that you want to upload it into, in the BROWSER section. A progress bar will appear at the bottom of QGIS letting you know on the progress of the upload.

I’ve obscured all my database connection and some of my schemas!

SUMMARY:

Getting data into PostGIS is relatively easy but the trick to any databases is the way that you manage that data going forward. For my money the QGIS DB Manager is the best option for adhoc uploads and refreshes of full datasets. While if the refresh of data is more frequent then using GDAL is a better option, as it will save time and is ultimately more fun to setup!