# Identifying earthquake-prone areas in the state of California

<div align="left"><figure><img src="/files/OG2RD7V5fNQpaw0lxn8Y" 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="/files/UCQAHv5GID0secD1QU2x" alt=""><figcaption></figcaption></figure>

<img src="/files/4m1BK9j4Wq34gat4HHd2" 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://academy.carto.com/advanced-spatial-analytics/spatial-analytics-for-bigquery/step-by-step-tutorials/identifying-earthquake-prone-areas-in-the-state-of-california.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.