Custom search

Samstag, 21. November 2015

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:
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.