Demo

The following two examples demonstrate questions that CASFRI can help answer. Since CASFRI is stored in a PostgreSQL database, we use SQL queries to extract the required data. The queries below reference the CASFRI specifications and use the valid_year_begin and valid_year_end attributes which are explained in the CASFRI readMe on Github.

Question 1: What is the most current information on the distribution of lodgepole pine in BC and Yukon?

To answer this question we want to query the CASFRI database to get the most recent forest species information from BC and Yukon.
We only want to extract CASFRI polygons where the leading species is lodgepole pine.
This equates to all polygons where the valid year is 2020, the CAS_ID matches either ‘BC’ or ‘YT’, the LAYER attribute is 1, and the SPECIES_1 attribute is one of PINU_CON_CON, PINU_CON_LAT, or PINU_CON_###.

The following query extracts the data which we can then visulaize using QGIS. The query identifies the valid polygons and joins them to the LYR and GEO tables. It then queries to get the polygons with leading lodgepole pine in BC and Yukon:

CREATE TABLE casfri50_test.demo_1 AS
WITH valid_polygons AS(
    SELECT cas_id cas_id_valid
    FROM casfri50_history.geo_history gh
    WHERE gh.valid_year_begin <= 2020 AND 2020 <= gh.valid_year_end
)
SELECT *
FROM valid_polygons
LEFT JOIN casfri50.lyr_all lyr ON valid_polygons.cas_id_valid = lyr.cas_id
LEFT JOIN (SELECT cas_id AS geo_cas_id, geometry FROM casfri50.geo_all) geo ON valid_polygons.cas_id_valid = geo.geo_cas_id
WHERE LEFT(cas_id, 2) IN('BC','YT') AND LAYER = 1 AND SPECIES_1 IN('PINU_CON_CON', 'PINU_CON_LAT', 'PINU_CON_###');

Question 2: What was the age of all forest stands across Quebec and New Brunswick in 2015?

To answer this question we want to query the CASFRI database to get the most valid information that is available for the year 2015. Since all jurisdictions produce inventories on different schedules, the most valid information could be from before or after 2015.
We will query the database using the year 2015, and only return the LYR rows where LAYER is 1.
We can then calculate age as a new attribute using the formula 2015 - ((ORIGIN_UPPER+ORIGIN_LOWER)/2).

With the following query, we select the most valid polygons in 2015 and join them to the LYR and GEO tables. We then calculate age for all stands originating in 2015 or earlier as the average origin value subtracted from 2015. We discard any polygons where the origin is >2015 (these polygons might be the most valid information we have for 2015, but since they originate after 2015 we cannot calculate an age for them). Blank areas on the map are either non-forested, missing data, or do not have sufficient information to calculate age.

CREATE TABLE casfri50_test.demo_2 AS
WITH valid_polygons AS(
    SELECT cas_id cas_id_valid
    FROM casfri50_history.geo_history gh
    WHERE gh.valid_year_begin <= 2015 AND 2015 <= gh.valid_year_end
)
SELECT *, 2015-((ORIGIN_UPPER+ORIGIN_LOWER)/2) age
FROM valid_polygons
LEFT JOIN casfri50.lyr_all lyr ON valid_polygons.cas_id_valid = lyr.cas_id
LEFT JOIN (SELECT cas_id AS geo_cas_id, geometry FROM casfri50.geo_all) geo ON valid_polygons.cas_id_valid = geo.geo_cas_id
WHERE LEFT(cas_id, 2) IN('QC','NB') AND LAYER = 1 AND ORIGIN_UPPER > 0 AND ORIGIN_UPPER <= 2015;