SQL to explore the RNLI open "Returns of Service" data set

lifeboat.sql

-- Data source: https://data-rnli.opendata.arcgis.com/

-- Remove outliers which are beyond the reach of the maximum lifeboat range:
SELECT
  rescue.*
FROM
  `RNLI.RNLI_Returns_of_Service` rescue
LEFT JOIN
  `RNLI.RNLI_Lifeboat_Station_Locations` station
ON
  UPPER(station.station) = rescue.lifeboatstationnameproper
WHERE
  rescue.geom IS NOT NULL
  AND ST_DISTANCE(station.geom, rescue.geom) < 231500


-- Aggregate the number of rescues by origin lifeboat station
SELECT
  station.station AS station_name,
  ANY_VALUE(station.geom) AS geom,
  COUNT(rescue) AS total_rescues,
FROM
  `RNLI.RNLI_Returns_of_Service` rescue
LEFT JOIN
  `RNLI.RNLI_Lifeboat_Station_Locations` station
ON
  UPPER(station.station) = rescue.lifeboatstationnameproper
WHERE
  station.station IS NOT NULL
GROUP BY
  station_name
ORDER BY
  total_rescues DESC


-- Use DBSCAN to cluster all rescues due to strong currents
SELECT
  objectid,
  geom,
  ST_CLUSTERDBSCAN(geom, 10000, 5) OVER () AS cluster_num
FROM
  `RNLI.RNLI_Returns_of_Service`
WHERE
  reasonforlaunch = 'Currents'


-- Create linestrings between rescues and stations of origin
SELECT
  station.station,
  ST_MAKELINE(station.geom, rescue.geom) AS geom,
  ST_DISTANCE(station.geom, rescue.geom) AS Distance,
  rescue.yearofcall,
  rescue.lifeboatclass,
  rescue.reasonforlaunch,
  rescue.seaconditionsatincident
FROM
  `RNLI.RNLI_Returns_of_Service` rescue
LEFT JOIN
  `RNLI.RNLI_Lifeboat_Station_Locations` station
ON
  UPPER(station.station) = rescue.lifeboatstationnameproper
WHERE
  rescue.geom IS NOT NULL
  AND ST_DISTANCE(station.geom, rescue.geom) < 231500