Forests of temperate Europe in the light of open data, open source software and SQL

According to the interpretation manual of the EU habitat directive some forests of community interest in the region of the temperate Europe are:

• 9110 Luzulo-Fagetum beech forests 
• 9120 Atlantic acidophilous beech forests with Ilex and sometimes also Taxus in the shrublayer (Quercinion robori-petraeae or Ilici-Fagenion)
• 9130 Asperulo-Fagetum beech forests
• 9140 Medio-European subalpine beech woods with Acer and Rumex arifolius
• 9150 Medio-European limestone beech forests of the Cephalanthero-Fagion
• 9160 Sub-Atlantic and medio-European oak or oakhornbeam forests of the Carpinion betuli
• 9170 Galio-Carpinetum oak-hornbeam forests
• 9180 * Tilio-Acerion forests of slopes, screes and ravines
• 9190 Old acidophilous oak woods with Quercus robur on sandy plains

Some open ingredients for baking an analysis around these forests:

• Natura 2000 data - the European network of protected sites
• Natural Earth Admin 0 – Countries
• SQLite - an open software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine
• SpatiaLite - open source library to extend the SQLite core to support fully fledged Spatial SQL capabilities
• GDAL/OGR
• QGIS

First download the spatialite version of the Natura 2000 data and have a quick look into the database by ogrinfo:

ogrinfo Natura2000_end2014.sqlite
INFO: Open of `Natura2000_end2014.sqlite'
      using driver `SQLite' successful.
1: natura2000polygon (Multi Polygon)

Beside the spatial data of protected areas within the Natura 2000 network (multipolygon layer: natura2000polygon), the database version of this data inlcudes also already additional informations connected to the protected areas (bioregion, contacts, designation status, directive species, habitat classes, habitats, impact, management, meta data, natura 2000 sites, other species, species).

Let's have a deeper look into the spatialite database (simplified by e.g. the spatialite-gui) and do some non-spatial SQL queries; e.g. filtering/querying the data and creating a view of protected areas including 9110 Luzulo-Fagetum beech forests:

# create view with protected areas including 9110 Luzulo-Fagetum beech forests based on the non-spatial table HABITATS
CREATE VIEW "vhabitat9110" AS
SELECT "SITECODE" AS "SITECODE", "HABITATCODE" AS "HABITATCODE",
    "COVER_HA" AS "COVER_HA", "REPRESENTATIVITY" AS "REPRESENTATIVITY",
    "RELSURFACE" AS "RELSURFACE", "CONSERVATION" AS "CONSERVATION",
    "GLOBAL_ASSESMENT" AS "GLOBAL_ASSESMENT", "DATAQUALITY" AS "DATAQUALITY",
    "PERCENTAGE_COVER" AS "PERCENTAGE_COVER"
FROM "HABITATS"
WHERE "HABITATCODE" = 9110
ORDER BY "SITECODE";

A view is some kind of a filtered/queried view of data organized like a table.

Just do it analogously for the other forest habitat types.

Then implement a spatial view of the data for later loading it to GIS:

# join the view created in the step before (vhabitat9110) with the spatial layer (Natura2000polygon) of the spatialite database.
CREATE VIEW "svhabitat9110" AS
SELECT "a"."ROWID" AS "ROWID", "a"."PK_UID" AS "PK_UID",
    "a"."SITECODE" AS "SITECODE", "a"."SITENAME" AS "SITENAME",
    "a"."RELEASE_DA" AS "RELEASE_DA", "a"."MS" AS "MS",
    "a"."SITETYPE" AS "SITETYPE", "a"."Geometry" AS "Geometry",
    "b"."SITECODE" AS "SITECODE_1", "b"."HABITATCODE" AS "HABITATCODE",
    "b"."COVER_HA" AS "COVER_HA", "b"."REPRESENTATIVITY" AS "REPRESENTATIVITY",
    "b"."RELSURFACE" AS "RELSURFACE", "b"."CONSERVATION" AS "CONSERVATION",
    "b"."GLOBAL_ASSESMENT" AS "GLOBAL_ASSESMENT", "b"."DATAQUALITY" AS "DATAQUALITY",
    "b"."PERCENTAGE_COVER" AS "PERCENTAGE_COVER"
FROM "Natura2000polygon" AS "a"
JOIN "vhabitat9110" AS "b" USING ("SITECODE")
ORDER BY "a"."SITECODE";

# registration of the spatial view to be loadable for GIS
INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only)
  VALUES ('svhabitat9110', 'geometry', 'rowid', 'natura2000polygon', 'geometry', 1);

A spatial view is some kind of a filtered/queried view of spatial data organized in a spatial database.

Do the same also for the other forest habitat types and have a look into the database:

ogrinfo Natura2000_end2014.sqlite
INFO: Open of `Natura2000_end2014.sqlite'
      using driver `SQLite' successful.
1: natura2000polygon (Multi Polygon)
2: svhabitat9110 (Multi Polygon)
3: svhabitat9120 (Multi Polygon)
4: svhabitat9130 (Multi Polygon)
5: svhabitat9140 (Multi Polygon)
6: svhabitat9150 (Multi Polygon)
7: svhabitat9160 (Multi Polygon)
8: svhabitat9170 (Multi Polygon)
9: svhabitat9180 (Multi Polygon)
10: svhabitat9190 (Multi Polygon)

Now there are spatial views of several forest habitat types.

Open QGIS, load the spatial views of protected areas with forest and let's do some further analysis, e.g. calculate the polygon centroids (in QGIS: Vector -> Geometry tools -> Polygon centroid) and a Delaunay triangulation of the centroids (in QGIS: Vector -> Geometry tools -> Delaunay triangulation) showing the function of an ecological network of protected areas for a certain habitat type in a simple way.

Some overviews of the distribution of some European temperate forest types within the Natura 2000 network by centroids of protected areas, connected by a Delaunay triangulation:

9110 Luzulo-Fagetum beech forests

9120 Atlantic acidophilous beech forests with Ilex and sometimes also Taxus in the shrublayer (Quercinion robori-petraeae or Ilici-Fagenion)

9130 Asperulo-Fagetum beech forests

9140 Medio-European subalpine beech woods with Acer and Rumex arifolius

9150 Medio-European limestone beech forests of the Cephalanthero-Fagion

9160 Sub-Atlantic and medio-European oak or oakhornbeam forests of the Carpinion betuli

9170 Galio-Carpinetum oak-hornbeam forests

9180 * Tilio-Acerion forests of slopes, screes and ravines

9190 Old acidophilous oak woods with Quercus robur on sandy plains

Another nice example of a interaction/interplay of open data and open source software to get an overview of European nature.

biodiversity and red list spatial data along the open way

Biodiversity is declining worldwide. Red lists and available biodiversity data are the basics for conservation.

The IUCN, International Union for Conservation of Nature, provides Red List of Threatened Species with taxonomic, conservation status and distribution information on plants, fungi and animals that have been globally evaluated using the IUCN Red List Categories and Criteria.

GBIF provides free and open access to biodiversity data.

Let's see what you can do with these informations along the open way, e.g. on the example of Triturus carnifex:

"This species is sensitive to changes in water quality. The principal threats to the species are loss of aquatic habitats, especially breeding sites, through agricultural intensification and agrochemical pollution, and introduction of predatory fishes. In the Balkans, there has been loss of breeding habitats in recent years due to decreased spring rains, perhaps as a result of global climate change, and presumed mortality of individuals because of predation by introduced fishes." (IUCN 2015)

Open data

• Natural Earth: Admin 0 – Countries
• GBIF biodiversity data
• IUCN Red List Spatial Data

Open source software

• GRASS GIS
• v.in.gbif
• v.in.redlist 

Some commands in GRASS GIS to get an overview:

# link to Natural Earth Admin 0 - Countries
v.external input=C:\dl\iucn\ne_10m_admin_0_countries output=ne_10m_admin_0_countries layer=ne_10m_admin_0_countries

# import downloaded GBIF data of Triturus carnifex
v.in.gbif -c --verbose input=C:\dl\iucn\triturus\0000336-150922153815467.csv \ output=GBIF_Triturus_carnifex dir=C:\dl\iucn\triturus 

# import IUCN Red List Spatial Data defined filtered for Triturus carnifex
v.in.redlist input=C:\dl\iucn\CAUDATA\CAUDATA.shp output=IUCN_redlist_Triturus_carnifex \ species_name="Triturus carnifex"

Triturus carnifex: blue points (GBIF data), grey polygon (IUCN Red List Spatial data), red lines (Natural Earth Data)

These are nice basic data and tools to help improving biodiversity status. Let's contribute to extend!