Export structure

Introduction

While most users can rely entirely on obs_read() to handle data preparation, understanding the underlying structure of Observation.org exports is valuable for advanced debugging, custom SQL queries, or integrating with external database tools.

This vignette details the layout of SQLite, CSV, and Excel exports, and explains precisely how obsR transforms them into a unified, analysis-ready format.

(Note: For details on language handling and spatial filtering, see vignette("labels") and vignette("spatial-filters")).

library(obsR)
library(DBI)
library(RSQLite)

db <- obs_example_db()

Export Formats Overview

Observation.org provides three primary export formats. obsR normalises all of them into the same canonical column structure.

Format	Supported in `obsR`	Language Dependencies
SQLite	Yes (Native)	Catalog values (e.g., `species.name`, `validation_status.name`) vary by export language.
CSV	Yes	Cell values vary by language. Column headers are always in English.
XLSX	Yes (requires `readxl`)	Same as CSV. Data is located in the `Data export` sheet.

When you specify locale = "es" (for example), obs_read() translates the coded categorical fields using built-in dictionaries. It does not rename the standardised English column headers, nor does it translate species common names (which remain exactly as exported, e.g., House Sparrow or Gorrión común).

The SQLite Relational Structure

The SQLite export is a relational database. The bundled Seville sparrows file is a typical example:

con <- dbConnect(SQLite(), db, flags = SQLITE_RO)
dbListTables(con)
#>  [1] "activity"            "country"             "country_division"   
#>  [4] "life_stage"          "location"            "metadata"           
#>  [7] "observation"         "observation_details" "observation_method" 
#> [10] "observation_source"  "species"             "species_group"      
#> [13] "species_type"        "substrate"           "user"               
#> [16] "validation_status"
dbGetQuery(con, "SELECT COUNT(*) AS n FROM observation")
#>      n
#> 1 1455
dbDisconnect(con)

The core observation table acts as the fact table. It uses integer foreign keys (e.g., species, location, user) that point to separate catalog tables.

con <- dbConnect(SQLite(), db, flags = SQLITE_RO)

# View observation table schema
dbGetQuery(con, "PRAGMA table_info(observation)")[, c("name", "type")]
#>                    name    type
#> 1                    id INTEGER
#> 2                  date    TEXT
#> 3                  time    TEXT
#> 4               species INTEGER
#> 5                number INTEGER
#> 6                   sex    TEXT
#> 7                 notes    TEXT
#> 8            life_stage INTEGER
#> 9              activity INTEGER
#> 10               method INTEGER
#> 11            is_escape INTEGER
#> 12           is_certain INTEGER
#> 13             location INTEGER
#> 14         embargo_date    TEXT
#> 15    validation_status    TEXT
#> 16   external_reference    TEXT
#> 17                 uuid    TEXT
#> 18            substrate INTEGER
#> 19                point    TEXT
#> 20                  lat    REAL
#> 21                  lng    REAL
#> 22              local_x    REAL
#> 23              local_y    REAL
#> 24               source INTEGER
#> 25             modified    TEXT
#> 26     country_division INTEGER
#> 27              country INTEGER
#> 28         validated_by INTEGER
#> 29 last_validation_date    TEXT
#> 30                 user INTEGER

# View linked catalog examples
dbGetQuery(con, "SELECT id, scientific_name, name, `group` FROM species LIMIT 3")
#>       id                   scientific_name                              name
#> 1    122                 Passer domesticus                     House Sparrow
#> 2   1579             Passer hispaniolensis                   Spanish Sparrow
#> 3 260927 Passer domesticus balearoibericus House Sparrow ssp balearoibericus
#>   group
#> 1     1
#> 2     1
#> 3     1
dbGetQuery(con, "SELECT * FROM validation_status")
#>   id                            name
#> 1  O                         unknown
#> 2  J        accepted (with evidence)
#> 3  P            accepted (plausible)
#> 4  A accepted (automatic validation)
#> 5  I                         pending
#> 6  N                        rejected
#> 7  U       cannot be validated (yet)

dbDisconnect(con)

Conceptually, the schema looks like this:

observation (Fact Table)
    ├─ species ──────────────> species (catalog)
    ├─ user ─────────────────> user (catalog)
    ├─ location ─────────────> location ─> country / country_division
    ├─ validation_status ────> validation_status (codes: A, J, P, O, N, U, I)
    ├─ life_stage, activity, observation_method, substrate (coded)
    └─ observation_details ──> Holds mixed-count breakdowns (SQLite only)

Privacy Note: In the bundled example, the user table contains one-way anonymised IDs and labels (e.g., Observer a1b2c3d4). When working with your own exports, always use obs_anonymise() before publishing or sharing data.

The `obsR` Transformation: What It Adds

Manually joining these tables is tedious. obs_read() performs these JOIN operations automatically and returns a flat, tidy tibble (obs_df). It intelligently creates paired columns: a human-readable label column and a stable *_code column.

obs <- obs_read(db)
obs[1:3, c(
  "scientific_name", "common_name", "species_code",
  "validation", "validation_code",
  "sex", "sex_code"
)]
#> <obs_df> with 3 observations from obsR_example_8cf966998a6a.sqlite [en]
#> Species: 1
#> Validation: unknown (2), accepted (automatic validation) (1)
#> # A tibble: 3 × 3
#>   scientific_name   validation                      sex        
#>   <chr>             <chr>                           <chr>      
#> 1 Passer domesticus unknown                         Unspecified
#> 2 Passer domesticus accepted (automatic validation) Unspecified
#> 3 Passer domesticus unknown                         Unspecified

Understanding Validation and Certainty

The validation column standardises statuses. Filtering by validation = "validated" retains codes A (accepted by automatic validation), J (accepted with evidence), and P (accepted as plausible).
Other codes include O (not evaluated), N (rejected), U (uncertain), and I (under study).
The is_certain column is a separate, independent flag set by the observer. It is not a synonym for formal validation.

Handling Mixed Counts

Observation.org allows users to record a total count (e.g., “20 individuals”) while explicitly detailing the subgroup composition (e.g., “10 Male; 7 Female; 3 Juvenile”). - In SQLite exports, this is stored in the observation_details table. obs_read() flags these rows with is_multiple = TRUE and stores the text breakdown in the details column. - CSV and Excel exports do not include this granular breakdown; for these formats, is_multiple is always FALSE and details is NA. - To analyse these subgroups, use obs_expand(). This function efficiently duplicates only the mixed-count rows into individual records, leaving the rest of the dataset untouched.

Handling Large Datasets (Lazy Evaluation)

For large SQLite exports, loading millions of rows into R memory at once is inefficient. By default, obs_read() applies a collect = 100000 threshold.

If the query matches fewer than 100,000 rows, it returns a standard, materialised tibble.
If it matches more, it returns a lazy obs_df (a dplyr database connection). The query remains in the database until you explicitly call obs_collect() or use a function that requires the full dataset.

You can control this behaviour: - collect = TRUE: Forces immediate loading into memory (use with caution for large files). - collect = FALSE: Keeps the query lazy regardless of size, allowing you to chain dplyr filters before materialising. - collect = 50000: Sets a custom row threshold.

# Keep the query lazy
pending <- obs_read(db, collect = FALSE)

# Materialise the data into R memory when ready
final_data <- obs_collect(pending)

This lazy evaluation, combined with the automatic schema mapping, makes obsR a robust tool for scaling citizen science data analysis from small local studies to national-level datasets.

Appendix: SQLite Export Schema Reference

This section provides a comprehensive, field-level reference of the tables found in a standard Observation.org SQLite export. It is intended for advanced users writing custom SQL queries, debugging data structures, or integrating with external database tools.

1. Core Fact Table: `observation`

The primary table containing one row per observation record.

Column Name	Data Type	Description / Meaning
`id`	INTEGER	Unique identifier for the observation.
`date`	TEXT	Date of the observation (YYYY-MM-DD).
`time`	TEXT	Time of the observation (HH:MM), if recorded.
`species`	INTEGER	Foreign key linking to the `species` catalog table.
`number`	INTEGER	Total count of individuals observed in this record.
`sex`	TEXT	Short code for the sex of the observed individuals (e.g., `M`, `F`, `U`).
`notes`	TEXT	Free-text comments provided by the observer.
`life_stage`	INTEGER	Foreign key linking to the `life_stage` catalog table.
`activity`	INTEGER	Foreign key linking to the `activity` catalog table (observed behaviour).
`method`	INTEGER	Foreign key linking to the `observation_method` catalog table.
`is_escape`	INTEGER	Boolean flag (1/0). Indicates if the individual is a captive or escaped specimen. A non-wild presence.
`is_certain`	INTEGER	Boolean flag (1/0). The observer’s own certainty about the identification.
`location`	INTEGER	Foreign key linking to the specific site name in the `location` table.
`embargo_date`	TEXT	Date until which the observation is hidden (used for sensitive species). Defaults to `1980-01-01` when no embargo is active.
`validation_status`	TEXT	Foreign key linking to `validation_status` (e.g., `A`, `J`, `P`, `O`, `N`, `U`, `I`).
`external_reference`	TEXT	Optional external ID (e.g., bird ringing number, museum specimen ID).
`uuid`	TEXT	Universal Unique Identifier, useful for external linking or deduplication.
`point`	TEXT	Optional GeoJSON geometry string for the location (typically a `Point` feature).
`lat`	REAL	Latitude in WGS84 (EPSG:4326).
`lng`	REAL	Longitude in WGS84 (EPSG:4326).
`local_x`, `local_y`	REAL	Projected coordinates. Used only for specific regions (e.g., EPSG:31370 for Belgium, EPSG:28992 for the Netherlands). Often empty.
`source`	INTEGER	Foreign key linking to `observation_source` (e.g., mobile app, web form, specific project).
`modified`	TEXT	Timestamp of the last modification to this record (YYYY-MM-DD HH:MM:SS.fraction).
`country_division`	INTEGER	Foreign key linking to the `country_division` table (e.g., province or state).
`country`	INTEGER	Foreign key linking to the `country` table.
`validated_by`	INTEGER	Foreign key linking to the `user` who performed the validation (if applicable).
`last_validation_date`	TEXT	Date of the most recent validation action.
`user`	INTEGER	Foreign key linking to the `user` (observer) who recorded the data.

2. Mixed Counts Breakdown: `observation_details`

Populated only when an observation contains a total count that is explicitly broken down by subgroups (e.g., “10 Male; 7 Female”). In obsR, records linked to this table are flagged with is_multiple = TRUE.

Column Name	Data Type	Description / Meaning
`id`	INTEGER	Unique identifier for the detail record.
`observation`	INTEGER	Foreign key linking to the parent `observation.id`.
`number`	INTEGER	Count of individuals in this specific subgroup.
`sex`	TEXT	Sex code for this subgroup.
`life_stage`	INTEGER	Foreign key linking to the `life_stage` of this subgroup.
`activity`	INTEGER	Foreign key linking to the `activity` of this subgroup.

3. Primary Catalog Tables

These tables provide the descriptive metadata linked by foreign keys in the observation table.

species

Column	Type	Description
`id`	INTEGER	Unique species identifier.
`scientific_name`	TEXT	Accepted Latin/binomial name of the taxon.
`name`	TEXT	Common name of the species in the language of the export.
`group`	INTEGER	Foreign key to `species_group` (e.g., 1 = Birds).
`type`	TEXT	Foreign key to `species_type` (e.g., `S` = species, `I` = subspecies).

user

Column	Type	Description
`id`	INTEGER	Unique user identifier.
`name`	TEXT	Observer’s display name. (Note: Use `obs_anonymise()` before sharing to keep this information private).

location

Column	Type	Description
`id`	INTEGER	Unique location identifier.
`name`	TEXT	Name of the specific site or place name (e.g., “Coto Doñana”).

country & country_division

Column	Type	Description
`id`	INTEGER	Unique identifier.
`name`	TEXT	Country or administrative division name (e.g., “Sevilla”).
`iso_alpha2`	TEXT	(Country only) Two-letter ISO country code (e.g., “ES”).

4. Categorical Lookup Tables

These tables map integer IDs or short codes to their human-readable names in the export language. They all follow a similar structure: id (primary key), name (translated label), and optionally group (linking to species_group to ensure, for example, that specific activities are only suggested for birds).

validation_status: Maps codes to their descriptive status: A (accepted automatic), J (accepted with evidence), P (accepted plausible), O (unknown), I (pending), N (rejected), U (cannot be validated yet).
species_type: S (species), I (subspecies), F (forma), V (variety), Y (synonym), H (hybrid), M (aggregate species).
species_group: High-level taxonomic groups (e.g., 1: Birds, 2: Mammals, 4: Butterflies, 10: Plants, 11: Fungi, 30: Disturbances).
life_stage: Maps IDs to stages (e.g., 1: unknown, 2: adult, 3: adult breeding, 5: juvenile, 1009: nestling).
activity: Maps IDs to observed behaviours (e.g., 1: present, 2: foraging, 4: courtship/singing, 3114: occupied nest with eggs, 3119: nest building).
observation_method: Maps IDs to detection methods (e.g., 48: seen, 49: heard, 51: seen and heard, 588: sound trapped).
substrate: Maps IDs to microhabitats. (Note: Often recorded as “not applicable” for birds, but actively used for taxa like fungi or lichens).
observation_source: Maps IDs to the data entry method or project. Ranges from generic sources (e.g., 42: API, 92: ObsMapp, 409: ObsIdentify, 421: iNaturalist) to specific monitoring projects (e.g., 27: Klapekstertelling, 107: SOVON autoclustering).

5. System Metadata: `metadata`

A simple key-value store containing export-level information.

Column	Type	Description
`key`	TEXT	Metadata property name (e.g., `source`, `export_date`, `coordinate reference systems`).
`value`	TEXT	The corresponding value. Note: The CRS key explicitly states that `point`, `lat`, and `lng` use WGS84 (EPSG:4326), while `local_x` and `local_y` use regional projections where applicable.

Tip: You can explore these tables dynamically in R using DBI::dbListTables(con) and inspect specific schemas or sample data with DBI::dbGetQuery(con, "SELECT * FROM table_name LIMIT 5"), as demonstrated earlier in this vignette.