# Identifying earthquake-prone areas in the state of California

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FhB2W9xXbzzo0kEuXMe3S%2Fintermediate%20banner.png?alt=media&#x26;token=4acd2cc7-c7e8-46c0-9669-6f6b73c030dd" alt="Intermediate difficulty banner" width="175"><figcaption></figcaption></figure></div>

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](https://console.cloud.google.com/marketplace/product/noaa-public/noaa-earthquakes).

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.

{% tabs %}
{% tab title="carto-un" %}

```sql
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;
```

{% endtab %}

{% tab title="carto-un-eu" %}

```sql
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;
```

{% endtab %}

{% tab title="manual" %}

```sql
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;
```

{% endtab %}
{% endtabs %}

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/FZkIora7XcE6uyLL6GHJ/earthquake-prone-california.png" alt=""><figcaption></figcaption></figure>

<img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/GWtJUWkKFcCzxcPMkgpe/eu-flag-website.png" alt="EU flag" data-size="line">This project has received funding from the [European Union’s Horizon 2020](https://ec.europa.eu/programmes/horizon2020/en) research and innovation programme under grant agreement No 960401.