Causes, consequences, and cross-scale linkages of climate-driven phenological mismatch across three trophic levels

This repository contains scripts to help research and outreach efforts. There are other GitHub repositories for this project maintained by the various research labs.

All data is stored in either a SQLite3 or PostgreSQL (PostGIS) database. We have gathered and consolidated the data from the sources below. Each source dataset records different things and therefore contains very different data. Our schema distills the common information needed for our study into a relational model and keeps the unique data, for further analysis, in NoSQL JSON blobs which are attached to each relational record.

1. North American Breeding Bird Survey (BBS).
2. MAPS: Monitoring Avian Productivity and Survivorship (MAPS).
3. eBird Basic Dataset. This dataset has been culled to take everything between 20⁰ and 90⁰ latitude and -95⁰ and -50⁰ longitude. We are also only taking "complete" and "approved" checklists. Additionally, we are also only taking data for ~120 migratory bird species.
4. Pollard butterfly dataset.
5. NABA butterfly dataset.
6. Caterpillar Counts dataset.
7. NestWatch dataset.
8. USGS Bird Banding Laboratory dataset.

There are 5 primary tables in the database:

1. The Taxa table that hold data specific to each taxon.
2. The Dataset table that hold information about where and when we extracted the data.
3. The Place table contains where observations occur. Most datasets sample data repeatedly at designated locations. NoSQL place data is contained in the place_json field.
4. The Event table holds when, how, and by whom data about the observation. NoSQL event data is in the event_json field.
5. The Count table holds what was observed and how many birds or leps were observed during the event. count_json holds the NoSQL data.

Some record counts for the datasets:

See this R script for how to access the sqlite database in R.

Most of the scripts in the lib directory access the database. I have moved common code into this library. A some sample_queries that use this library are in this Python script.