![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2818439/LR_BlueRGB_PositiveboxL.png)
Working together
for a safer world
Using PostGIS within our Geospatial Workflows at Lloyd's Register
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2817918/pasted-from-clipboard.png)
Rob Burgess
Lloyd's Register
QGIS South West 2016
29.09.2016
Purpose
PostgreSQL and PostGIS are essential for many of our geospatial workflows, so this talk has been created to share some of those we've found most useful, along with some of the problems encountered along the way.
PostgreSQL: A database
PostGIS: A spatial extension to PostgreSQL - adds support for geographic objects
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Overview
- Why does Lloyd's Register need this geospatial data?
- Geospatial library
- Automated database updates
- Database replication
-
Providing access to PostGIS via:
- QGIS
- Web Mapping Services - focus on CAD drawings
- Tile Mapping Services
- Some of the problems we've encountered along the way
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
About...
LR Survey & GeoEngineering
- Offshore project management of "Geo" surveys
- Geoscience / Geospatial / Engineering consultancy
- Operational Support
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3015690/pasted-from-clipboard.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3015698/pasted-from-clipboard.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2860432/pasted-from-clipboard.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Reference data types
Administrative
Physical Environment
Ecology & Biodiversity
Infrastructure
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Reference data stored within PostGIS
~60 sources of data...
1000+ individual tables...
16GB worth of data...
How do we ensure users are accessing the most up-to-date data?
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Automated database updates
Bash command line script used to update PostGIS from WFS data sources. Runs weekly.
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3018538/bash_wfs_update_script.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3045649/automated_data_load_wfs.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Automated database updates
Drawbacks of these update scripts:
- Links to data must not change!
- Changes to attribute column names can be problematic
- If Postgis table is being consumed in ArcMap, be extra careful!
- Some providers don't support direct links to data - updates are manual
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
How do we ensure people are accessing data in the fatest, most efficient way?
Users spread out across multiple offices, and in multiple time zones around world
Accessing a database instance hundreds or thousands of miles away just does not work.
Database replication:
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3050499/master_slave_replication.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
How do we replicate data?
Write-Ahead Logging (WAL)
- Changes to data must be logged before any update occurs within the database
- Each log captures the changes made
- Snapshots (or WAL files) are streamed out to slave DBs where the changes are then replicated
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3050392/wal-replication.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Providing access to PostGIS via QGIS
Pre-styled QGIS projects
- Loaded via
- Created a read-only PostGIS user so that data accessed via the master cannot be edited
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3022699/Add-Postgis-Layer.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3022758/pre-styled-qgis-project.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Providing access to PostGIS via QGIS
QGIS projects are really easy to replicate for different locations (ie accessing via different slave servers)
Right click the QGIS project
Find/Replace the server name
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3022822/qgis_project_xml_edit.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Providing access to PostGIS via WMS
Quite often face problems like:
- Users wanting to view CAD drawings, but have no GIS / CAD software
- They want the colour coding within the CAD file maintained
- They want the CAD files to be updated asap
Create our own Web Mapping Services (WMS) to be consumed within a web application.
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3034226/geoserver-logo.jpg)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3034229/windfarm-sld.png)
Before
After
Providing access to PostGIS via TMS
Sometimes CAD drawings are too complex for WMS - too much stress on Geoserver
Created our own custom Tile Mapping Services.
This differs from WMSs because PNGs are pre-generated
and cached - and can be served to users considerably faster.
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3039390/fpso.jpg)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Step 1: Styling the Data within Tile Mill
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3026127/mapbox_logo.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3026131/tilemill-logo.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3039587/fpso-tilemill.gif)
Providing access to PostGIS via TMS
Step 2: Mapnik XML
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3039619/mapnik-xml.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Providing access to PostGIS via TMS
Step 3: Mapproxy
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/3041264/iris-tms-diagram.png)
Step 1
Step 2
Step 3
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Providing access to PostGIS via TMS
Mapproxy TMS:
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
Next Steps...
- Tile Mill is no longer supported - would be great to implement Vector Tiles through applications like MapBox Studio
- Is it possible to go from CAD to postGIS without intermediary shapefile?
- We don't have much experience of storing or processing raster data via PostGIS - maybe this would be interesting to explore
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2832957/LRonly_BlueRGB_PositiveL.png)
![](https://s3.amazonaws.com/media-p.slid.es/uploads/541907/images/2818439/LR_BlueRGB_PositiveboxL.png)
Working together
for a safer world
Rob Burgess
Lloyd's Register Survey & GeoEngineering
+44 (0)1225 485800
Robert.Burgess@lr.org
Lloyd’s Register and variants of it are trading names of Lloyd’s Register Group Limited, its subsidiaries and affiliates.
Copyright © Lloyd’s Register. 2016. A member of the Lloyd’s Register group.
PostGIS Workflows [QGIS SW Oct 2016 Talk]
By Lloyd's Register
PostGIS Workflows [QGIS SW Oct 2016 Talk]
Presentation by Rob Burgess of Lloyd's Register using PostGIS
- 4,440