CARTO User Manual

CARTO User Manual

Assessing the damages of La Palma Volcano

Context

Since 11 September 2021, a swarm of seismic activity had been ongoing in the southern part of the Spanish Canary Island of La Palma (Cumbre Vieja region). The increasing frequency, magnitude, and shallowness of the seismic events were an indication of a pending volcanic eruption; which occurred on 16th September, leading to evacuation of people living in the vicinity.

In this tutorial we are going to assess the number of buildings and population that may get affected by the lava flow and its deposits. We’ll also estimate the value of damaged residential properties affected by the volcano eruption.

Steps to reproduce

  1. Go to the new CARTO platform access page: https://app.carto.com

    Log in Email and password

  2. Create a new CARTO organization. Check this guide to get started.

  3. The first time that you access the Workspace, you will see a Welcome banner with links providing quick access to different actions to get you started with CARTO, like creating your first connection or your first map.

    Welcome banner Homepage first landing

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

    Menu features data explorer

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

    Data Explorer content carto data warehouse

  6. In this tutorial, we are going to use the following 3 tables:

    • lapalma_buildings: it contains the buildings in La Palma as obtained from the Spanish cadaster website;
    • lapalma_sociodemo_parcels: it contains a sample from Unica360’s dataset in the Data Observatory “Cadaster and Sociodemographics (Parcel)”;
    • lapalma_volcano_lavaflow: it includes the lava flow from the Volcano eruption in La Palma, Spain as measured by the Copernicus satellite on 10/04/2021.
  7. Spend some time exploring the three tables in the Data Explorer.

    Data Explorer data prewiew

    Data Explorer data prewiew

    Data Explorer data prewiew

  8. Selected “lapalma_buildings” and click on Create > Create map button on the top.

    Data Explorer create map from table

    This will open CARTO Builder with this table added as a layer to a map.

    Data Explorer created map from table

  9. Let’s start by having a look at the Data table. Click on the three dots icon, select Show data table and your dataset table will be displayed.

    Map source options

    Note that we have added a column called estimated_prop_value in which we have estimated the value of the residential buildings based on the average market value per square meter in each municipality and the surface area of each building (which is provided by the cadaster itself).

    Map table column

  10. Rename the layer to “La Palma Buildings”.

Map layers rename

  1. Click on Layer style to start styling the layer.

Map layers style

  1. We can style the layer, by changing for example the color of the geometries.

    Map fill color

  2. Let’s now add another layer by clicking on the “Add source from…” button from Sources. Select Table/Tileset, then select the CARTO Data Warehouseconnection and click on demo data > demo_tables > lapalma_sociodemo_parcels from the collapsible tree. Click on Add Source.

    Add source table

  3. Let’s change the name of this second layer (B) to “La Palma demographics”

Map layers rename

  1. Now we change the style of this new layer, by changing the color and size of the geometries.

    Map fill color

    Map radius

    And we can also change the outline color.

    Map outline color

  2. Note that at this point we could do more things such as styling the size of the points by the population living in the parcel. For that, click on the “three dots” icon on “Radius” and select p_t as the field to base the radius on.

    Map radius based on

  3. Now we are going to add a new layer by creating a custom SQL Query that is going to aggregate the number of buildings and their estimated value (remember, only for residential properties) that fall within the volcano’s lava flow.

    For that, we click again on “Add source from…”, but now we select “Custom Query (SQL)”, we select the CARTO Data Warehouse connection, and the option “Type your own query”. Then we click on Add Source.

    Add source custom query

  4. In the SQL panel, type the following query:

    1
    2
    3
    4
    5
    6
    7
    8
    
    WITH lava_flow AS (
    SELECT * FROM `carto-demo-data.demo_tables.lapalma_volcano_lavaflow`
    )
    SELECT *,
    FROM (SELECT count(*) as num_properties, sum(d.estimated_prop_value) as value_damage
    FROM `carto-demo-data.demo_tables.lapalma_buildings` d
    INNER JOIN lava_flow g
    ON ST_INTERSECTS(g.geom,d.geom)), lava_flow
    

    Click on Run.

    Map sql console first query

  5. This query should have generated a new layer with the volcano’s lava flow and the following columns num_properties and value_damage . You can check the Data table from the “Source” to validate the result of your query.

    Map table results

  6. Let’s rename this third layer (C) as “Lava flow”.

Map layers rename

  1. Let’s change the ordering of the layers and place this third layer to the bottom of the list so the other layers render on top of the lava flow. You should only drag & drop the “C"layer on the layer panel.

    Map layers order

  2. Let’s change the styling of the Lava flow layer. Change the Fill and Stroke colors as suggested below:

    Map fill stroke

  3. In case it’s not activated by default, let’s activate a tooltip for this third layer, in order to show the values of the number of damaged buildings and the estimated damaged value of residential properties when hovering on top of the lava flow polygon.

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

    Map tooltips new tooltip

    Map tooltips new tooltip on map

  4. Now let’s add a last layer into the map. We are going to do again a Custom SQL query, so repeat the steps done before but now run the following query:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    
    WITH lava_buffer AS
    (SELECT `carto-un`.transformations.ST_BUFFER(geom,0.5,"kilometers",1) AS geom
    FROM `carto-demo-data.demo_tables.lapalma_volcano_lavaflow`),
     
    affected_properties AS 
    (SELECT *,
    FROM (SELECT count(*) AS num_properties, sum(d.estimated_prop_value) AS value_damage
    FROM `carto-demo-data.demo_tables.lapalma_buildings` d
    INNER JOIN lava_buffer g
    ON ST_INTERSECTS(g.geom,d.geom)), lava_buffer)
        
    SELECT *,
    FROM (SELECT round(sum(b.p_t)) AS affected_population
    FROM `carto-demo-data.demo_tables.lapalma_sociodemo_parcels` b
    INNER JOIN lava_buffer g
    ON ST_INTERSECTS(g.geom,b.geom)), affected_properties
    

    What this SQL does is to create a 0.5km buffer around the lava flow, and performs the same aggregations as we have done before to compute the total number of buildings and the estimated damaged value of the residential properties within this new geometry. Additionally, we intersect the buffer with the table with Sociodemographic data (“lapalma_sociodemo_parcels”) and aggregate the total number of population living within the lava buffer area.

    It should generate a fourth layer such as the one illustrated below:

    Map sql console second query

  5. Let’s rename this fourth layer (D) as “Lava buffer” and place it at the bottom of all other layers.

    Map layers rename

    Map layers order

  6. Let’s apply some styling to the layer, for example as shown below:

    Map fill stroke

  7. Make sure you have the tooltip enabled also on this “D” layer, to show the fields Affected_population, Num_properties and Value_damage.

    Map tooltips new tooltip

    Map tooltips new tooltip

  8. We can now also change the basemap of our map. Let’s go to the Basemap tab and select Google Maps basemaps with the “Terrain” option, and let’s remove all layers except the “Landscape” one. As follows:

    Map basemap

  9. Finally we can make the map public and share the link to anybody in the organization. For that you should go to “Share” on the top right corner and set the map as Public. For more details, see Publishing and sharing maps.

    Map public map

  10. Finally, we can visualize the result.