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​
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.
4.From the Navigation Menu in the left panel, select Data Explorer.
5.Select the CARTO Data Warehouse connection and click on demo data > demo_tables from the collapsible tree.
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.
8.Selected “lapalma_buildings” and click on Create > Create map button on the top.
This will open CARTO Builder with this table added as a layer to a map.
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.
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).
10.Rename the layer to “La Palma Buildings”.
11.Click on Layer style to start styling the layer.
12.We can style the layer, by changing for example the color of the geometries.
13.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.
14.Let’s change the name of this second layer (B) to “La Palma demographics”
15.Now we change the style of this new layer, by changing the color (fill and outline) and size of the geometries.
16.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.
17.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.
18.In the SQL panel, type the following query:
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.
19.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.
20.Let’s rename this third layer (C) as “Lava flow”.
21.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.
22.Let’s change the styling of the Lava flow layer. Change the Fill and Stroke colors as suggested below:
23.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:
24.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:
WITH lava_buffer AS
(SELECT `carto-un`.carto.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:
25.Let’s rename this fourth layer (D) as “Lava buffer” and place it at the bottom of all other layers.
26.Let’s apply some styling to the layer, for example as shown below:
27.Make sure you have the tooltip enabled also on this “D” layer, to show the fields Affected_population, Num_properties and Value_damage.
28.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:
29.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.
30.Finally, we can visualize the result.
Analyzing Airbnb ratings in Los Angeles
Build a categories & bubbles visualization
Last modified 8mo ago