In an emergency the importance of location is critical. Knowing the precise whereabouts of an incident can be the difference between life and death.
The Royal National Lifeboat Institution (RNLI) are the charity that saves lives at sea. Responsible for saving over 140,000 lives since their formation in 1824 they work with a dedicated team of volunteers, staff and community fundraisers. They allow us to enjoy our shorelines and water, safe in the knowledge that in an emergency they will be there to assist us.
There is a lot of data behind the lifesaving and in 2017 RNLI teamed up with ESRI to create an open data portal to help share some of this data.
The RNLI contributed six datasets to the portal, including one covering the location of their 238 lifeboat stations (RNLI Lifeboat Station Locations) and another listing the location and details of every emergency call-out they dealt with between 2008 and 2016 (RNLI Return of Service).
With access to such rich datasets we (the OS GeoDataViz team) felt it was a great chance to collaborate with the RNLI and produce a number of data visualisations to show just what the RNLI deal with every day and how location helps.
To explain the data we have used three different pieces of software and we’ll explain how we achieved the results for each visualisation. The data is available from the portal as Shapefiles, KML and CSV files and we used the CSV files.
We’ll be splitting the tutorials into three blog posts.
- Part One – QGIS
- Part Two – Kepler
- Part Three – Tableau
This first walkthrough assumes you are on a Windows machine with working knowledge of PostGIS, PostgreSQL and QGIS. To produce these visualisations, we loaded the data (one table containing the stations (RNLI Lifeboat Station Locations) as points, the other containing the incidents (Return of Service 2008 – 2016) as points) into a PostGIS-enabled database. From here, we combined some table joins (based on the station names), ST_SetSRID, ST_MakePoint and ST_MakeLine to produce the line geometries. After this we loaded the data into QGIS to be visualised. The incident points and lines were stacked (in that order) and styled appropriately. ‘Draw Effects’ is really handy for producing glowy effects.
Next, we needed some context to give the lines and points some meaning. Given the data moved into Irish waters, coverage beyond Great Britain was required. We used a European-wide DEM, produced by Copernicus, as the backdrop alongside EMODnet’s bathymetry data. QGIS’ ‘on the fly’ CRS transformation feature came in handy given the data was provided in different coordinate reference systems. We styled the DEM as a hillshade and styled the bathymetry data using greys and blues from the GDV Colour Palettes. Finally, we added surface water. For Ireland and Northern Ireland this came from DAERA Northern Ireland and Ordnance Survey Ireland. For Great Britain, the surface water layer was taken from OS Open Zoomstack. All water layers were styled up in the same hue of blue as the shallow areas of the bathymetry data.
We used simple filtering in GQIS to produce each visualisation. One contains all incidents, one contains just hoaxes and false alarms, and the other all legitimate incidents. Follow our walk-through to recreate the visualisation containing all incidents.
As part of this project we also produced visuals for just the locations of the false alarm call-outs and the location of real calls only. These and the visual produced in this tutorial can be found here.
Step-by-step to creating a visualisation in QGIS
We firstly need to download all the data and store this in an easily accessible location.
- EMODnet’s bathymetry data (tiles C3, C4, D3, D4, D5, E3, E4)
- Copernicus’ EU-DEM v1.1 data (tiles E20N30, E30N20, E30E30, E30N40, E40N40)
- RNLI’s ‘Return of Service 2008 – 2016’ and ‘Lifeboat Station Locations’ tables
- Ordnance Survey’s Open Zoomstack (surface water)
- DAERA Northern Ireland (surface water)
- Ordnance Survey Ireland (surface water)
Next we need to get the ‘Station’ and ‘Return of Service’ (AKA ‘Incident’) tables loaded into our spatially enabled PostGIS database. We can do this using the ‘PostGIS Shapefile and DBF Loader Exporter’. If you are just working with the provided shapefiles then go to stage 4.
Once loaded, we can begin working with the data. The aim is to create line geometries. Namely, for each incident, we want a line that goes from the origin station to the destination incident. For starters, we’ll explore the data. Having had a look at the different attributes, we can see that both tables have a ‘station’ column. This is what we will use to perform our table join – the field on which we can merge the two tables. You may have noticed that the station field in the station table is lowercase and the station field in the incident table is uppercase. However this isn’t a problem as we can handle this problem mid-query. This can be done in one query using Common Table Expressions (CTEs). We then add a spatial index to the new table to speed up rendering in QGIS later.
CREATE TABLE rnli.flowlines AS
WITH e1 AS
— make unique ID with window function.
— ST_X and ST_Y retrieve the x and y coordinates from a geometry column.
SELECT row_number() over() AS uid, launchtime, yearofcall AS year,
ST_X(geom) AS incident_x, ST_Y(geom) AS incident_y, aic, lifeboatst AS station,
lifeboatnu AS lifeboat, rostype, casualtyca AS casualty, casualtyty AS type,
reasonforl AS issue, outcomeofs AS outcome, activity,
visibility AS incident_visibility, weatherati AS incident_weather,
seaconditi AS incident_conditions, visibili_1 AS launch_visibility,
weatheratl AS launch_weather, seacondi_1 AS launch_conditions,
winddirect AS launch_winddirection, geom
— The station fields differ. Stations tables is lowercase, Incidents table is
— upper case. Make the stations table upper case with UPPER()
— ST_X and ST_Y used again to get the x and y coordinates from the geometry
SELECT UPPER(station) AS station, county, region, division, country,
ST_X(geom) AS station_x, ST_Y(geom) AS station_y, geom
— select the various fields we want from e1 and e2, joining them on the station
SELECT e1.uid, e1.launchtime, e1.year, e1.incident_x, e1.incident_y, e1.aic, e1.station, e2.station_x, e2.station_y, e2.county, e2.region,
e2.division, e2.country, e1.lifeboat, e1.rostype, e1.casualty, e1.type, e1.issue, e1.outcome, e1.activity, e1.incident_visibility, e1.incident_weather,
e1.incident_conditions, e1.launch_visibility, e1.launch_weather, e1.launch_conditions, e1.launch_winddirection,
— get the length in metres of the line. The lines doesn’t exist yet so we have to — wrap the two geometries with ST_MakeLine
— make the linestring geometry using ST_MakeLine. It is good practice to cast the
— geometry to a specific geometry type and Coordinate Reference System
ST_MakeLine(e1.geom, e2.geom) :: Geometry(Linestring, 27700) AS geom
FROM e1 AS e1
— Our common field is the station field. We will join on this, only including
— records where both tables have the station field
e2 AS e2
e1.station = e2.station;
— make spatial index to speed up future spatial queries and rendering in QGIS
CREATE INDEX rnli_flowlines_geom_idx ON rnli.flowlines USING GIST(geom);
Now we begin to use QGIS. Open a new project and connect to your PostGIS database. This can be done by clicking the PostGIS elephant icon –> your database connection –> table –> ‘OK’. If this has not yet been set up, click the PostGIS elephant icon –> ‘new’ and fill out the login details and database connection information as appropriate. We want to bring in 2 tables: The incident table and the flowline table. If using the provided shapefiles, simply drag the .shp element of each file onto the QGIS window to open them. Before doing anything else, please click in the bottom right corner to select the CRS.
Fill out the settings as you can see below. We will be using data with different projections so it is important that ‘on the fly’ CRS transformation is enabled. Our chosen CRS is ‘OSGB 1936 / British National Grid’.
You can probably see that there are a few lines streaking across England from Ireland. These look messy and subtract from the final visualisation. We can eliminate these by right clicking the flowlines layer and selecting ‘Filter’. Paste the code from the text file named RNLI_line_filter which can be found here and then click ‘OK’. Please note, this step is not necessary if you are using the provided shapefiles.
We’ll order the layers so that the incidents are on the bottom, with the flowlines on top. We will then duplicate all of the layers and rename and order them as follows:
Now for some more filtering. For both the flowlines and incidents ‘hoax’ layers, we will right click on a layer and hit ‘Filter’. We will enter the following at the very bottom of the filter window. If you are using the provided shapefiles, please remove AND from the start of the code.
AND “aic” = ‘Hoax and false alarm’
For the ‘normal’ layers, we will filter again, but this time enter the following. Again, if you are using the provided shapefiles, please remove AND from the start of the code.
AND “aic” != ‘Hoax and false alarm’
What we have done here is split the data. We now have data in the ‘hoax’ layers that are only hoaxes and false alarms, and data in the ‘normal’ layers encompassing all legitimate incidents.
Now for the styling. We’ll handle the ‘hoax’ layers first. For flowlines_hoax, right click the layer and click ‘properties’. Click ‘Simple Line’ and then fill out the properties as below:
To get the exact same red, click the colour shown in the ‘color’ bar and enter the hex code:
Duplicate this step for the flowlines_normal layer, but this time ensure the transparency is 50% and the hex code is set to:
Now for styling the incident layers. We’re going to be using the same colours as before, so #ff0101 for the hoaxes and #00adc0 for the legitimate callouts. Right click incidents_hoax and select properties. We will style up as follows:
Note this time we have selected Draw Effects at the bottom left. Ensure this is ticked and click the yellow star. Left click Outer Glow and tick the box next to it. Fill out the options as follows, ensuring we use the same red for the glow colour:
Now do exactly the same for the incidents_normal layer, ensuring the colour is set to blue using the hex code we used before. Don’t forget, the glow colour needs to be the same blue!
We now need to add some context. We’ll start from the bottom with the bathymetry data. Drag each .tif into QGIS, ensuring that they are underneath all of the other layers. Pick layer D4, right click it and click ‘properties’. Under ‘style’, we want to change the style to ‘Singleband pseudocolor’ and fill out the rest of the properties as shown. It doesn’t matter what ‘color’ you have selected, just ensure that you have two classes and that the values are set at 0 and 233. Make sure 0 is styled with colour #000000 and 233 with colour #00302d. You can do this by double clicking the colour block next to the value.
Now for a neat trick. We need to repeat this process for all of the bathymetry layers. However, repeating this would be inconvenient. What we can do instead is right click layer D4, hover over ‘Styles’ and click ‘Copy Style’. For each bathymetry layer, right click it, hover over ‘Styles’ and click ‘Paste Style’. All bathymetry layers should now have a consistent styling with blacks in deeper waters and blues nearer the shore.
We will now ensure the background colour is set to the same blue as the shallow waters. Click ‘Project’ at the top left of the window and find ‘Project properties’. Go to ‘General’ and ensure ‘Background color’ is set to:
We will now add the terrain. We’ll repeat our styling approach from earlier by copying and pasting styles. This time however, we will style the layers as hillshades. Drag and drop the 5 DEM layers (in any order) into QGIS and ensure that they sit on top of the bathymetry layers, but below the RNLI data. Right click one, select ‘Properties’ and set the ‘Render type’ to ‘Hillshade’. Proceed to fill out the other settings as shown in the image below:
Having changed the style to that seen above, click ‘OK’. As we did before, right click the layer you just styled, hover over ‘Styles’ and click ‘Copy style’. Paste this style onto all of the other DEM layers.
Finally we need to add some very subtle surface water to the visualisation to finish it off. We will use the OS Open Zoomstack surface water data, the Ordnance Survey Ireland data and the DAERA Northern Ireland data. Bring each layer in and place them above the DEM data, but below the RNLI data. We want the surface water layers to be the same colour as the shallow water in the bathymetry data (#00302d). We’ve got 1 line layer and 3 polygon layers. Right click the line layer and click ‘Properties’. We want the styling to be subtle, so click ‘Simple line’ and replicate the settings below. As mentioned previously, the colour should be:
Now style the water polygons. Right click one of the layers and go to ‘properties’. With the same shade of blue once more, click on ‘Simple fill’ and replicate the style shown below:
Hit ‘OK’ and copy and paste this style onto the remaining 2 polygon layers. We now should have our data all styled to look like this:
We are now ready to export the image. Right click one of the RNLI layers and hit ‘Zoom to layer’. Now hit ‘Project’ at the top left and navigate to ‘Print Composer’. From here, we will click ‘Composition’ and change the image dimensions to 235mm x 300mm. We will also ensure the resolution is high at 600dpi.
Now click ‘Add new map’ and fill the canvas with a new map:
Now click ‘Item Properties’. Set scale to:
Work to fit the map to the centre of the image using ‘Move item content’:
Also ensure the rest of ‘Item Properties’ looks like this:
Additionally you can add a title using ‘Add new label’.
Fonts and text can be changed under ‘Item properties’.
We can now click ‘Composer’ at the top left and go to ‘Export as image…’. Save it in a location of your choice in a format of your choice. PNG is usually good. Hit ‘Save’. Ensure the resolution is 600dpi. Hit ‘Save’ again. You should now have a lovely visualisation displaying a flow map of RNLI callouts between 2008 and 2016!
Come back next week for a guide on using Kepler to create a data visualisaltion with RNLI data.