Working together
for a safer world
Using PostGIS within our Geospatial Workflows at Lloyd's Register
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
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
About...
LR Survey & GeoEngineering
- Offshore project management of "Geo" surveys
- Geoscience / Geospatial / Engineering consultancy
- Operational Support
Reference data types
Administrative
Physical Environment
Ecology & Biodiversity
Infrastructure
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?
Automated database updates
Bash command line script used to update PostGIS from WFS data sources. Runs weekly.
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
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:
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
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
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
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.
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.
Step 1: Styling the Data within Tile Mill
Providing access to PostGIS via TMS
Step 2: Mapnik XML
Providing access to PostGIS via TMS
Step 3: Mapproxy
Step 1
Step 2
Step 3
Providing access to PostGIS via TMS
Mapproxy TMS:
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
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.