Overview

Research on the behaviour of the maritime transport sector, including:
- Response to policy changes
- Identification of transport routes
- Environmental impact (GHG emission)
- Communication of outputs to the general public

ShipViz

Data sources:

Point data - Dynamic Automatic Identification System (AIS) for collision avoidance and security
- date, time, position (lat, lon), type of vessel, navigation status, etc., on CSV format
Polygon data - Maritime Policy data:
- Emission Control Areas
- List of (lat, lon) values describing the outer shape of the polygons on a series of PDF files

ShipViz data

What made the data difficult to deal with:

Highly sensitive data - license allowed data to be stored only at UCL premises and for internal use only
Big volumes of data - one entry every second for every registered vessel throughout a whole year
Geographical data - lat/lon coordinates
PDF format and descriptions of ECAs in text format

What they needed from us

Develop a visualisation tool for ingestion, manipulation, analysis and visualization of geo-temporal data
- Time slider / animation capabilities
- Filtering options (type of vessel, type of fuel, area, ...)
- Interactive and responsive
- Import / Export data (CSV, KML, SHP...)
- Secure due to data provider restrictions
- Easy for the Shipping Group to maintain and build on top
- Produces nice visualisations to inform their community
- Input SQL queries
- ... All these to be completed in around 30 days of work

PostgreSQL

The group had ingested all the point data in a PostgreSQL instance:
- Open Source relational database
- Several hundreds of millions of data points
- It doesn't "understand" geolocated data by default - it treats latitudes and longitudes as doubles

A standard ShipViz query took around 60 minutes

PostGIS

PostGIS = PostgreSQL DB + GIS extension
- Supports spatial data types:
  - Point, polygon, circle, curve, multipolygon, ...
- Supports spatial functions:
  - distance, area, union, intersection
- Does this by using spatial indexing with R-trees

A standard ShipViz query takes now 5 minutes!

PostGIS - data types

Geometry
- Represents a feature in the Euclidean coordinate system - compatible with GML

POINT(0 0)
LINESTRING(0 0,1 1,1 2)
CIRCULARSTRING(0 0, 1 1, 1 0) 
POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))
GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4))
-- Polygon with a hole
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))

import pyproj
transformer = pyproj.Transformer.from_crs("EPSG:27700", "EPSG:4326")
coord = transformer.transform(lat, lon)

PostGIS - data types

Geography
- Represents spatial features represented on spheral coordinates
- Understands projections

PostGIS - spatial queries

ST_Contains (A, B)
ST_Intersects (A, B)
ST_Length (A)
ST_Distance (A, B), ST_DWithin (A, B, r)
ST_Area (A)
ST_GeomFrom[KML|GML|GeoJSON |...]()
ST_MakeLine({points})
ST_Polygonize()
ST_BuildArea(multiline string)
ST_Union(geometry[])