Raquel Alegre

 

RSDG meeting, 25th April 2019

 

  • Context:
    • ShipViz project with the Energy Institute (2015)
    • ShipViz data
  • PostgreSQL and PostGIS
    • R-tree indexing
  • What's Carto
    • Demos

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

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[])

 

PostGIS - Indexing ​

  • ​R-tree: Groups nearby objects and represents them with their minimum bounding rectangle

 

PostGIS - Indexing ​

  • ​R-tree: Groups nearby objects and represents them with their minimum bounding rectangle

 Carto

At carto.com you can:

  • Import/Export data in different formats
  • View table data as well as plotted data on a map
  • Interact with the DB writing SQL queries and see live result in the map
  • Customise and export interactive visualisations on the web
  • Use online or install on premises
  • Use as a whole or use different bits (DB, CSS, ...)

Software as a Service cloud computing platform that provides GIS and web mapping tools for display in a web browser.

 Carto - infrastructure

Demo!

Another demo!

           20% data points in CartoDB

          400K points:  ~1% of the data points in CartoDB

The End

Copy of ShipViz work

By Raquel Alegre

Made with Slides.com

Copy of ShipViz work

Presentation about work done on ShipViz (Energy Institute) by Raquel Alegre for one of the events at the series of UCL's Technical Socials - March 2016

  • 1,058

More from Raquel Alegre