2 Preprocess bird trends and distribution maps

2.1 Converting the ESRI Geodatabase

We use the BirdLife species distribution maps¹ to determine the extents of our analyses. As this data is provided in an ESRI File Geodatabase that is somehow difficult to query due to its size, we convert it to a Geopackage first. The file will be substantially bigger, but much quicker to query.

ogr2ogr("data/raw/birdlife/BOTW.gdb", "data/raw/birdlife/BOTW.gpkg", f = "GPKG", nlt = "MULTIPOLYGON", sql = "SELECT * FROM All_Species", progress = TRUE)

2.2 Load species trends

We will use species trends derived from PECBMS, the Pan-European Common Bird Monitoring Scheme, which contains trends for 170 European breeding birds.

colnames_species <- c("euring", "species", "namenote", "trend_long", "trend_short", "trend_long_slope",
                      "trend_long_se", "trend_short_slope", "trend_short_se", "habitat", "common_name",
                      "trend_classification", "graphnote")
coltypes_species <- "ncnnnnnnncccc"

species <- read_delim("data/raw/pecbms/pecbms-europe-indicesandtrends-2019.csv", delim = ";", 
                      col_names = colnames_species, col_types = coltypes_species, skip = 1)
head(species)

euring	species	namenote	trend_long	trend_short	trend_long_slope	trend_long_se	trend_short_slope	trend_short_se	habitat	common_name	trend_classification	graphnote
70	Tachybaptus ruficollis	7	12	2	1.01	0.00	1.02	0.01	other	Little Grebe	Stable	NA
90	Podiceps cristatus	7	5	-1	0.99	0.00	1.01	0.01	other	Great Crested Grebe	Moderate decline (p<0.01) **	NA
1110	Bubulcus ibis	10	15	67	1.01	0.01	1.07	0.01	farmland	Cattle Egret	Stable	NA
1190	Egretta garzetta	1	NA	14	NA	NA	1.02	0.01	other	Little Egret	Moderate increase (p<0.05) *	NA
1220	Ardea cinerea	NA	118	-19	1.02	0.00	0.99	0.00	other	Grey Heron	Moderate increase (p<0.01) **	NA
1340	Ciconia ciconia	NA	57	-1	1.01	0.00	1.01	0.00	farmland	White Stork	Moderate increase (p<0.01) **	NA

2.3 Loading species traits

We download a dataset of life-history characteristics (LHC) by Storchová & Hořák.²

if (!file.exists("data/raw/life-history-characteristics/Life-history characteristics of European birds.txt")) {
  paths_cache <- dryad_download("10.5061/dryad.n6k3n")[[1]]
  paths_final <- paste0("data/raw/life-history-characteristics/", basename(paths_cache))
  
  if(!dir.exists("data/raw/life-history-characteristics")) {
    dir.create("data/raw/life-history-characteristics")
  }
  file.copy(from = paths_cache, to = paths_final)
  file.remove(paths_cache)
}

We can now load the dataset and extract species name and migratory characteristics, so we can filter for the latter later.

lhc <- read_tsv("data/raw/life-history-characteristics/Life-history characteristics of European birds.txt",
                col_types = cols_only(`Species` = col_character(), `Family` = col_character(),
                                      `Sedentary` = col_double(), `Facultative migrant` = col_double(),
                                      `Short distance migrant` = col_double(), `Long distance migrant` = col_double())) %>%
  rename(species = Species, family = Family, sedentary = Sedentary, facultative = `Facultative migrant`, 
         shortdist = `Short distance migrant`, longdist = `Long distance migrant`) %>%
  drop_na()

We add the species trends and life-history characteristics to a single dataframe.

species %>%
  left_join(lhc, by = c("species" = "species")) -> species

We can inspect for which species no migration strategy has been found yet because of mismatching species name-pairs. These are species which have NA values for the family column.

species %>%
  filter(is.na(family)) %>%
  head()

euring	species	namenote	trend_long	trend_short	trend_long_slope	trend_long_se	trend_short_slope	trend_short_se	habitat	common_name	trend_classification	graphnote	family	sedentary	facultative	shortdist	longdist
9910	Hirundo rupestris	10	45	22	1.01	0.01	1.02	0.01	other	Eurasian Crag-martin	Stable	NA	NA	NA	NA	NA	NA
9950	Hirundo daurica	10	108	54	1.02	0.01	1.05	0.01	other	Red-rumped Swallow	Moderate increase (p<0.01) **	NA	NA	NA	NA	NA	NA
11060	Luscinia svecica	9	-33	-18	0.98	0.01	0.99	0.01	other	Bluethroat	Moderate decline (p<0.01) **	Index only represents population change of subspecies Luscinia svecica svecica.	NA	NA	NA	NA	NA
11471	Oenanthe cypriaca	1	NA	-3	NA	NA	1.00	0.01	other	Cyprus Wheatear	Stable	NA	NA	NA	NA	NA	NA
12550	Hippolais pallida	1	NA	94	NA	NA	1.04	0.03	other	Eastern Olivaceous Warbler	Uncertain	NA	NA	NA	NA	NA	NA
12680	Sylvia melanothorax	1	NA	25	NA	NA	1.06	0.02	other	Cyprus Warbler	Moderate increase (p<0.01) **	NA	NA	NA	NA	NA	NA

We can now manually change the scientific names in the life-history characteristics dataset to match with the PECBMS dataset.

name_replacements <-
  c("Ptyonoprogne rupestris" = "Hirundo rupestris",
    "Cecropis daurica" = "Hirundo daurica",
    "Cyanecula svecica" = "Luscinia svecica",
    "Oenanthe pleschanka" = "Oenanthe cypriaca",
    "Iduna pallida" = "Hippolais pallida",
    "Sylvia melanothorax" = "Sylvia melanothorax",  # Not in LHC dataset
    "Poecile palustris" = "Parus palustris",
    "Poecile montanus" = "Parus montanus",
    "Lophophanes cristatus" = "Parus cristatus",
    "Periparus ater" = "Parus ater",
    "Cyanistes caeruleus" = "Parus caeruleus",
    "Chloris chloris" = "Carduelis chloris",
    "Spinus spinus" = "Carduelis spinus",
    "Linaria cannabina" = "Carduelis cannabina",
    "Acanthis flammea" = "Carduelis flammea",
    "Emberiza calandra" = "Miliaria calandra"
    )
speciesnames <- lhc$species
oldnames <- speciesnames
for (name in names(name_replacements)) {
  speciesnames <- replace(speciesnames, which(speciesnames == name), name_replacements[name])
}
lhc$species <- speciesnames

And we once again join the LHC and PECBMS datasets.

species %>%
  dplyr::select(-colnames(lhc)[!colnames(lhc) %in% c("species")]) %>%
  left_join(lhc, by = c("species" = "species")) %>%
  drop_na(family) %>%
  identity() -> species

head(species)

euring	species	namenote	trend_long	trend_short	trend_long_slope	trend_long_se	trend_short_slope	trend_short_se	habitat	common_name	trend_classification	graphnote	family	sedentary	shortdist	longdist
70	Tachybaptus ruficollis	7	12	2	1.01	0.00	1.02	0.01	other	Little Grebe	Stable	NA	Podicipedidae	0	1	0
90	Podiceps cristatus	7	5	-1	0.99	0.00	1.01	0.01	other	Great Crested Grebe	Moderate decline (p<0.01) **	NA	Podicipedidae	0	1	0
1110	Bubulcus ibis	10	15	67	1.01	0.01	1.07	0.01	farmland	Cattle Egret	Stable	NA	Ardeidae	1	0	0
1190	Egretta garzetta	1	NA	14	NA	NA	1.02	0.01	other	Little Egret	Moderate increase (p<0.05) *	NA	Ardeidae	0	0	1
1220	Ardea cinerea	NA	118	-19	1.02	0.00	0.99	0.00	other	Grey Heron	Moderate increase (p<0.01) **	NA	Ardeidae	0	1	0
1340	Ciconia ciconia	NA	57	-1	1.01	0.00	1.01	0.00	farmland	White Stork	Moderate increase (p<0.01) **	NA	Ciconiidae	0	0	1

2.4 Select migratory species

We now have a dataset of 169 species, but we are merely interested in those that are actually migratory, so we filter out resident and facultative migrants.

species %>%
  filter(sedentary != 1 & facultative != 1) -> species

head(species)

euring	species	namenote	trend_long	trend_short	trend_long_slope	trend_long_se	trend_short_slope	trend_short_se	habitat	common_name	trend_classification	graphnote	family	shortdist	longdist
70	Tachybaptus ruficollis	7	12	2	1.01	0	1.02	0.01	other	Little Grebe	Stable	NA	Podicipedidae	1	0
90	Podiceps cristatus	7	5	-1	0.99	0	1.01	0.01	other	Great Crested Grebe	Moderate decline (p<0.01) **	NA	Podicipedidae	1	0
1190	Egretta garzetta	1	NA	14	NA	NA	1.02	0.01	other	Little Egret	Moderate increase (p<0.05) *	NA	Ardeidae	0	1
1220	Ardea cinerea	NA	118	-19	1.02	0	0.99	0.00	other	Grey Heron	Moderate increase (p<0.01) **	NA	Ardeidae	1	0
1340	Ciconia ciconia	NA	57	-1	1.01	0	1.01	0.00	farmland	White Stork	Moderate increase (p<0.01) **	NA	Ciconiidae	0	1
1730	Tadorna tadorna	8	27	-5	1.01	0	1.00	0.00	other	Common Shelduck	Moderate increase (p<0.05) *	NA	Anatidae	1	0

2.5 Match names to BirdLife taxonomy

Finally, we will be querying BirdLife distribution maps, so we have to match scientific names with the taxonomy used by BirdLife.

birdlife_taxonomy <- readxl::read_excel("data/raw/birdlife/HBW-BirdLife_List_of_Birds_v4.xlsx", 
                                        col_names = c("family_birdlife", "common_name_birdlife", "species", "iucn_redlist"))

species %>%
  left_join(birdlife_taxonomy, by = c("species" = "species")) -> species

Once again, we see which species are not matched and tweak these manually to match with the BirdLife dataset.

species %>%
  filter(is.na(family_birdlife)) %>%
  head()

euring	species	namenote	trend_long	trend_short	trend_long_slope	trend_long_se	trend_short_slope	trend_short_se	habitat	common_name	trend_classification	graphnote	family	shortdist	longdist	family_birdlife	common_name_birdlife	iucn_redlist
9950	Hirundo daurica	10	108	54	1.02	0.01	1.05	0.01	other	Red-rumped Swallow	Moderate increase (p<0.01) **	NA	Hirundinidae	0	1	NA	NA	NA
11060	Luscinia svecica	9	-33	-18	0.98	0.01	0.99	0.01	other	Bluethroat	Moderate decline (p<0.01) **	Index only represents population change of subspecies Luscinia svecica svecica.	Muscicapidae	0	1	NA	NA	NA
12550	Hippolais pallida	1	NA	94	NA	NA	1.04	0.03	other	Eastern Olivaceous Warbler	Uncertain	NA	Acrocephalidae	0	1	NA	NA	NA
16540	Carduelis spinus	NA	12	29	0.99	0.00	1.00	0.00	forest	Eurasian Siskin	Moderate decline (p<0.01) **	NA	Fringillidae	1	0	NA	NA	NA
16630	Carduelis flammea	NA	-75	47	0.97	0.00	1.03	0.01	other	Common Redpoll	Moderate decline (p<0.01) **	NA	Fringillidae	1	0	NA	NA	NA

And we tweak those species manually.

name_replacements_birdlife <-
  c("Hirundo daurica" = "Cecropis daurica",
    "Luscinia svecica" = "Cyanecula svecica",
    "Hippolais pallida" = "Iduna pallida",
    "Carduelis spinus" = "Spinus spinus",
    "Carduelis flammea" = "Acanthis flammea"
    )
speciesnames <- species$species
oldnames <- speciesnames
for (name in names(name_replacements_birdlife)) {
  speciesnames <- replace(speciesnames, which(speciesnames == name), name_replacements_birdlife[name])
}
species$species_birdlife <- speciesnames

2.6 Querying distribution maps

Now that we have gathered the species trends and selected only migratory species, we can query the database to select the corresponding distribution maps. As some species are distributed over massive areas, we select only these parts of their distributions that intersect with the bounding box of Africa.

species_query <- str_sub(str_replace_all(toString(paste0("SCINAME = '", species$species_birdlife, "' OR ")), " OR , ", " OR "),
                         start = 1, end = -5)
query <- paste("SELECT * FROM All_Species WHERE (", species_query, ") AND PRESENCE = 1 AND SEASONAL = 3", sep = "")

africa <- st_read("data/raw/Africa.gpkg")

ogr2ogr("data/raw/birdlife/BOTW.gpkg", "data/processed/TrendSpecies.gpkg", sql = query, f = "GPKG", nlt = "MULTIPOLYGON", progress = TRUE,
        spat = st_bbox(africa))

We can now join these datasets again and save them for upcoming analysis steps.

st_read("data/processed/TrendSpecies.gpkg") %>%
  group_by(SCINAME) %>%
  summarise(species = SCINAME, geometry = st_union(st_buffer(geom, dist = 0))) %>%
  distinct(species) %>%
  left_join(species, by = c("species" = "species")) %>%
  identity() -> trendspecies

saveRDS(trendspecies, file = "data/processed/trendspecies.RDS")

2.7 Species included in the analysis

Finally the following species have now been included in this analysis, based on their distribution ranges and migratory behavior.

readRDS("data/processed/trendspecies.RDS") %>% 
  st_drop_geometry() %>%
  ungroup() %>%
  dplyr::select(species, common_name_birdlife, trend_long) %>%
  rename(`Scientific name` = species, 
         `Common name (BirdLife)` = common_name_birdlife,
         `Long-term trend` = trend_long) -> species_included

species_included

Scientific name	Common name (BirdLife)	Long-term trend
Accipiter nisus	Eurasian Sparrowhawk	4
Acrocephalus arundinaceus	Great Reed-warbler	-3
Acrocephalus palustris	Marsh Warbler	-9
Acrocephalus schoenobaenus	Sedge Warbler	-29
Acrocephalus scirpaceus	Common Reed-warbler	-4
Actitis hypoleucos	Common Sandpiper	-36
Anas platyrhynchos	Mallard	49
Anthus campestris	Tawny Pipit	-67
Anthus pratensis	Meadow Pipit	-62
Anthus trivialis	Tree Pipit	-52
Apus apus	Common Swift	4
Ardea cinerea	Grey Heron	118
Burhinus oedicnemus	Eurasian Thick-knee	-33
Calandrella brachydactyla	Greater Short-toed Lark	49
Carpodacus erythrinus	Common Rosefinch	-38
Cecropis daurica	NA	NA
Ciconia ciconia	White Stork	57
Circus aeruginosus	Western Marsh-harrier	436
Clamator glandarius	Great Spotted Cuckoo	23
Columba palumbus	Common Woodpigeon	105
Cuculus canorus	Common Cuckoo	-32
Cyanecula svecica	NA	NA
Delichon urbicum	Northern House Martin	-22
Egretta garzetta	Little Egret	NA
Emberiza hortulana	Ortolan Bunting	-90
Emberiza melanocephala	Black-headed Bunting	NA
Falco tinnunculus	Common Kestrel	-24
Ficedula albicollis	Collared Flycatcher	141
Ficedula hypoleuca	European Pied Flycatcher	-29
Fringilla montifringilla	Brambling	-48
Gallinago gallinago	Common Snipe	-48
Grus grus	Common Crane	513
Haematopus ostralegus	Eurasian Oystercatcher	-6
Hippolais icterina	Icterine Warbler	-49
Hippolais polyglotta	Melodious Warbler	-29
Hirundo rustica	Barn Swallow	-9
Iduna pallida	NA	NA
Jynx torquilla	Eurasian Wryneck	-64
Lanius collurio	Red-backed Shrike	-18
Lanius minor	Lesser Grey Shrike	NA
Lanius senator	Woodchat Shrike	-20
Larus ridibundus	Black-headed Gull	-48
Limosa limosa	Black-tailed Godwit	-59
Locustella fluviatilis	River Warbler	-74
Locustella naevia	Common Grasshopper-warbler	-73
Luscinia luscinia	Thrush Nightingale	-33
Luscinia megarhynchos	Common Nightingale	-67
Merops apiaster	European Bee-eater	120
Motacilla cinerea	Grey Wagtail	9
Motacilla flava	Western Yellow Wagtail	-76
Muscicapa striata	Spotted Flycatcher	-50
Numenius arquata	Eurasian Curlew	-36
Numenius phaeopus	Whimbrel	77
Oenanthe cypriaca	Cyprus Wheatear	NA
Oenanthe hispanica	Black-eared Wheatear	-36
Oenanthe oenanthe	Northern Wheatear	-71
Oriolus oriolus	Eurasian Golden Oriole	16
Phoenicurus phoenicurus	Common Redstart	8
Phylloscopus bonelli	Western Bonelli’s Warbler	-15
Phylloscopus collybita	Common Chiffchaff	119
Phylloscopus sibilatrix	Wood Warbler	-44
Phylloscopus trochilus	Willow Warbler	-33
Pluvialis apricaria	Eurasian Golden Plover	-13
Podiceps cristatus	Great Crested Grebe	5
Saxicola rubetra	Whinchat	-88
Spinus spinus	NA	NA
Streptopelia turtur	European Turtle-dove	-80
Sylvia borin	Garden Warbler	-28
Sylvia cantillans	Subalpine Warbler	176
Sylvia communis	Common Whitethroat	23
Sylvia curruca	Lesser Whitethroat	-16
Sylvia hortensis	Western Orphean Warbler	160
Sylvia nisoria	Barred Warbler	-66
Tachybaptus ruficollis	Little Grebe	12
Tadorna tadorna	Common Shelduck	27
Tringa erythropus	Spotted Redshank	NA
Tringa glareola	Wood Sandpiper	-13
Tringa nebularia	Common Greenshank	1
Tringa ochropus	Green Sandpiper	2
Tringa totanus	Common Redshank	-54
Turdus iliacus	Redwing	-16
Turdus pilaris	Fieldfare	18
Turdus torquatus	Ring Ouzel	7
Upupa epops	Common Hoopoe	244
Vanellus vanellus	Northern Lapwing	-55

A total of 85 is retained in this dataset.

1 GEE: Harmonic Regression

3 Species assemblage trends