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.