CARTO User Manual

CARTO User Manual

Go back

Analysing urban areas affected by earthquakes

Context

In this section, we provide a tutorial that showcases how easy it is to perform geospatial analysis operations using CARTO Builder.

In this example, we will analyse the total of urban areas affected in Spain by earthquakes over 2021. This type of analysis can be useful to show the general situation of risks threatening a given population in order to be able to plan measures and actions to mitigate their possible negative effects (human, economic and environmental)

Steps To Reproduce

  1. Go to the CARTO signup page.

    • Click on Log in.
    • Enter your email address and password. You can also log in with your existing Google account by clicking Continue with Google.
    • Once you have entered your credentials: click Continue.

    Log in Email and password

  2. From the Navigation Menu in the left panel, select Data Explorer.

    Menu features data explorer

  3. Select the CARTO Data Warehouse connection and click on demo data > demo_tables from the collapsible tree.

    Data Explorer content carto data warehouse

  4. Select “spain_earthquakes” and explore the preview of the map and the details of the table.

    Data Explorer map prewiew

    Data Explorer data prewiew

  5. Create a map by clicking the Create map button on the top. This will open the table as a layer on a CARTO Builder map. Check Creating a map from your data to get started.

    Data Explorer create map from table

  6. Let’s now add another layer by clicking on the “Add source from…” tab from Sources. Select Import file tab, then choose the URL method and insert the following URL.

1
 https://storage.googleapis.com/carto_test_tables/natural_earth/large_scale_data/polygon/ne_10m_urban_areas.zip

Map import select file

  1. The next screen will allow you to set the location. The file will be uploaded to the CARTO Data Warehouse (organization data > shared). Once you have completed this configuration, click on Continue.

    Map import set location

  2. Review the details before starting the importing process and then click on Add source. This will start the importing process, you can minimise the modal screen and continue working in CARTO while the file is being imported.

    Map import confirmation

    Once the data has been imported, the dataset is included in the Builder map tool as a new layer.

  3. Rename the name of the layers to “Spain earthquakes” and “Urban areas”, respectively.

    Map layers rename

  4. Now let´s started with our analysis by clicking on the three dots in the “spain_earthquakes” data source to find the Add SQL Analysis option. For more details, check the SQL Analyses guide.

Map add sql analysis button

  1. You will see a list of analyses compatible with your source. Select Filter by column and click on Continue.

    Map add sql analysis filter by column

  2. Select date as target column, type “2021” manually in the values and press enter. Leave the keep option selected in the results and click on Run SQL analysis.

    Map add sql analysis filter by column

  3. Once the analysis has finished, the dataset is included in the Builder map tool as a new layer. As we have run the analysis directly, the SQL editor will appear but in a collapsed mode. You can open it to check the SQL analysis or close it if you don’t need the query.

    Map add sql analysis filter by column new layer

  4. Now we are going to add a new SQL analysis from the previous one by clicking on the three dots in the “SQL Query 1” data source and selecting the Add SQL Analysis option.

    Map add sql analysis button

  5. Select Intersect and aggregate and click on Continue. Select urban_areas as second source and COUNT as the aggregation operation. Click on Preview SQL analysis query.

    Map add sql analysis button

  6. As we have clicked on preview the analysis, the SQL editor will appear in a expanded mode and the layer will not be added until we run the analysis. You can either change the parameters or leave them as they are and click on Run.

    Map add sql analysis button

    The resulting SQL query will be the initial analysis with the new analysis added. We are analysing earthquakes occurring in 2021 that intersect with urban areas.

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    
    WITH __q1 AS (
        SELECT * FROM `carto-demo-data.demo_tables.spain_earthquakes`
    ),
    __q2 AS (
        SELECT * FROM __q1 WHERE REGEXP_CONTAINS(date, r'(?i)(2021)')
    ),
    __q3 AS (
        SELECT * FROM `carto-dw-ac-lqe3zwgu.shared.urban_areas`
    ),
    __q4 AS (
        SELECT
            base.event,
            base.date,
            base.hour,
            base.lat,
            base.long,
            base.depth,
            base.intensity,
            base.magnitude,
            base.magnitude_type,
            base.location,
            base.time,
            ST_UNION_AGG(base.geom) as geom,
            COUNT(*) as aggregated_value
        FROM
            __q2 as base,
            __q3 as second
        WHERE
            ST_INTERSECTS(base.geom, second.geom)
        GROUP BY
            1,2,3,4,5,6,7,8,9,10,11
    )
    SELECT * FROM __q4
    
  7. Run the analysis, close the SQL editor, disable the layers “Layer 3” and “Spain earthquakes” and rename the new layer to “Spain earthquakes (2021)”.

    Map add sql analysis button

  8. We are going to add a new SQL analysis from the previous one by clicking on the three dots in the “SQL Query 2” data source and selecting the Add SQL Analysis option. Select Create Buffers and click on Continue.

    Map add sql analysis create buffers

  9. Leave the default parameters, click on the arrow and select Preview SQL analysis query.

    Map add sql analysis create buffers parameters

  10. Run the analysis, close the SQL editor and rename the new layer to “Spain earthquakes (2021) buffer”. What this SQL does is to create a 10km buffer around the earthquakes.

    Map add sql analysis create buffers new layer

  11. Additionally, we are going to intersect the buffer with the table with Urban Areas data (“urban_areas”) and aggregate the total urban area affecting by the earthquakes. Click on the three dots in the “SQL Query 3” data source and select the Add SQL Analysis option. Select Intersect and aggregate and click on Continue.

    Map add sql analysis intersect and aggregate

  12. Select urban_areas as second source, SUM as the aggregation operation AND area_sqkm as the aggregation column. Click on Preview SQL analysis query.

    Map add sql analysis intersect and aggregate parameters

  13. We can also modify the SQL query in order to give the column resulting from the aggregation a more suitable alias. For example, by renaming the default alias aggregated_value to affected_area and changing the SQL query to:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    
        WITH __q1 AS (
        SELECT * FROM `carto-demo-data.demo_tables.spain_earthquakes`
    ),
    __q2 AS (
        SELECT * FROM __q1 WHERE REGEXP_CONTAINS(date, r'(?i)(2021)')
    ),
    __q3 AS (
        SELECT * FROM `carto-dw-ac-lqe3zwgu.shared.urban_areas`
    ),
    __q4 AS (
        SELECT
            base.event,
            base.date,
            base.hour,
            base.lat,
            base.long,
            base.depth,
            base.intensity,
            base.magnitude,
            base.magnitude_type,
            base.location,
            base.time,
            ST_UNION_AGG(base.geom) as geom,
            COUNT(*) as aggregated_value
        FROM
            __q2 as base,
            __q3 as second
        WHERE
            ST_INTERSECTS(base.geom, second.geom)
        GROUP BY
            1,2,3,4,5,6,7,8,9,10,11
    ),
    __q5 AS (
        SELECT
            * EXCEPT (geom),
            ST_Buffer(geom, 10000) AS geom
        FROM
            __q4
    ),
    __q6 AS (
        SELECT * FROM `carto-dw-ac-lqe3zwgu.shared.urban_areas`
    ),
    __q7 AS (
        SELECT
            base.event,
            base.date,
            base.hour,
            base.lat,
            base.long,
            base.depth,
            base.intensity,
            base.magnitude,
            base.magnitude_type,
            base.location,
            base.time,
            base.aggregated_value,
            ST_UNION_AGG(base.geom) as geom,
            sum(second.area_sqkm) as affected_area
        FROM
            __q5 as base,
            __q6 as second
        WHERE
            ST_INTERSECTS(base.geom, second.geom)
        GROUP BY
            1,2,3,4,5,6,7,8,9,10,11,12
    )
    SELECT * FROM __q7
    
  14. Rename this new layer layer (F) as “Spain earthquakes (2021) buffer-intersect” and disable the layer “Spain earthquakes (2021) buffer”.

    Map add sql analysis intersect and aggregate rename layer

  15. Let’s change the ordering of the layers and place the layer “Spain earthquakes (2021)” to the top of the list so the other layers render at the bottom of this layer. You should only drag & drop the “C" layer on the layer panel.

    Map add sql analysis intersect and aggregate reorder layer

  16. Let´s also minimize the “Sources” panel and close the SQL editor for better visibility.

    Map add sql analysis intersect and aggregate minimize panels

  17. Select the “Spain earthquakes (2021)” layer and click the “three dots” icon. Select Layer style to start styling the layer.

Map layers options

  1. Click the “three dots” icon in the Stroke Color section and select “Stroke color Based on” feature magnitude. Pick a palette for a sequential variable (gradient). Now the earthquakes on the map will display a ramp color based on on the magnitude at which they occur.

Map layers options

  1. We can change the radius of the points in order to make them more visible.

    Map radius

  2. Now let´s change the opacity of the layer “Spain earthquakes (2021 buffer-intersect)” Change the opacity to 0,2.

Map fill color based on field and opacity

  1. In case it’s not activated by default, let’s activate a tooltip for this layer (“Spain earthquakes (2021) buffer-intersect”), in order to show the to show the place of occurrence of the earthquake and the affected urban area when hovering on top of the earthquake buffer zones.

    For that, go to the Interactions tab and activate the tooltip option. Press on Clear All and delete the tooltips except for the fifth source “SQL Query 4” and leave the fields location and affected_area as shown below:

    Map tooltip

  1. Go to the Widget tab. If you haven’t created a widget yet, you will see the following page:

    Map widgets new widget

  2. Click the New widget button and select “SQL Query 2”. When you add a widget, it´s always the Formula widget by default, based on a Count operation on the number of features displayed on the map viewport. Rename the widget to “Nº of earthquakes in Spain (2021)”.

    Map widgets formula rename

  3. Now we are going to add a few more interactive widgets (HISTOGRAM). Click the Add widget button and select “SQL Query 1”.

    Map widgets select source histogram

    Select the HISTOGRAM widget and choose the field magnitude from the list. You will get a histogram widget in order to be able to filter the earthquakes in Spain based on their magnitude. Rename the widget to “Earthquakes in Spain by magnitude”.

    Map histogram widget select field

  4. We are going to add the last widget. For that, go back to the widget’s list, click on Add widget button, select “SQL Query 1" and choose the TIME-SERIES widget. We are going to aggregate the data range of the time column by months. The histogram widget displays the number of earthquakes in each month over time, allowing you to select and visualize a specific range of data.

    Map time series widget select field

  5. Rename the map to “Earthquakes in Spain over 2021”.

    Map rename title and legend

  6. We can make the map public and share it online with our colleagues. We could also protect our map before sharing it by enabling password protection and setting a map password. For more details, see Publishing and sharing maps .

    Map public map

  7. Finally, we can visualize the result.