Identifying earthquake-prone areas in the state of California

In this example we are going to use some of the functions included in CARTO’s Analytics Toolbox in order to highlight zones prone to earthquakes, using a BigQuery public dataset.

First we define our region of interest, which in this case is a bounding box enclosing the state of California, using the function ST_MAKEENVELOPE. After filtering the earthquake locations with this bounding box, we compute the concave hull polygon enclosing the resulting points using the ST_CONCAVEHULL function. For visualization purposes, this polygon is smoothed out by means of the ST_BEZIERSPLINE function. Finally, we construct the polygon defining the earthquake-prone area using the ST_POLYGONIZE function.

carto-un

WITH bounds AS (
    SELECT `carto-un`.carto.ST_MAKEENVELOPE(-126.98746757203217, 31.72298737861544, -118.1856191911019, 40.871240645013735) AS bbox
),
data AS (
    SELECT ST_GEOGPOINT(longitude, latitude) AS points
    FROM `bigquery-public-data`.noaa_significant_earthquakes.earthquakes
    JOIN bounds
    ON ST_CONTAINS(bounds.bbox, ST_GEOGPOINT(longitude, latitude))
    WHERE longitude IS NOT NULL AND latitude IS NOT NULL
),
bezier_spline AS (
    SELECT `carto-un`.carto.ST_BEZIERSPLINE(
        ST_BOUNDARY(
        `carto-un`.carto.ST_CONCAVEHULL(ARRAY_AGG(points), 300, "kilometres")),
        null,
        0.9) AS geom
    FROM data
),
polygon_array AS (
    SELECT `carto-un`.carto.ST_POLYGONIZE(ARRAY_AGG(geom)) AS geom
    FROM bezier_spline
)
SELECT unnested FROM polygon_array, UNNEST(geom) AS unnested;

carto-un-eu

WITH bounds AS (
    SELECT `carto-un-eu`.carto.ST_MAKEENVELOPE(-126.98746757203217, 31.72298737861544, -118.1856191911019, 40.871240645013735) AS bbox
),
data AS (
    SELECT ST_GEOGPOINT(longitude, latitude) AS points
    FROM `bigquery-public-data`.noaa_significant_earthquakes.earthquakes
    JOIN bounds
    ON ST_CONTAINS(bounds.bbox, ST_GEOGPOINT(longitude, latitude))
    WHERE longitude IS NOT NULL AND latitude IS NOT NULL
),
bezier_spline AS (
    SELECT `carto-un-eu`.carto.ST_BEZIERSPLINE(
        ST_BOUNDARY(
        `carto-un-eu`.carto.ST_CONCAVEHULL(ARRAY_AGG(points), 300, "kilometres")),
        null,
        0.9) AS geom
    FROM data
),
polygon_array AS (
    SELECT `carto-un-eu`.carto.ST_POLYGONIZE(ARRAY_AGG(geom)) AS geom
    FROM bezier_spline
)
SELECT unnested FROM polygon_array, UNNEST(geom) AS unnested;

manual

WITH bounds AS (
    SELECT carto.ST_MAKEENVELOPE(-126.98746757203217, 31.72298737861544, -118.1856191911019, 40.871240645013735) AS bbox
),
data AS (
    SELECT ST_GEOGPOINT(longitude, latitude) AS points
    FROM `bigquery-public-data`.noaa_significant_earthquakes.earthquakes
    JOIN bounds
    ON ST_CONTAINS(bounds.bbox, ST_GEOGPOINT(longitude, latitude))
    WHERE longitude IS NOT NULL AND latitude IS NOT NULL
),
bezier_spline AS (
    SELECT carto.ST_BEZIERSPLINE(
        ST_BOUNDARY(
        carto.ST_CONCAVEHULL(ARRAY_AGG(points), 300, "kilometres")),
        null,
        0.9) AS geom
    FROM data
),
polygon_array AS (
    SELECT carto.ST_POLYGONIZE(ARRAY_AGG(geom)) AS geom
    FROM bezier_spline
)
SELECT unnested FROM polygon_array, UNNEST(geom) AS unnested;

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.