library(arealDB)
library(tidyverse)
DBDir <- tempdir()
gazDir <- "directory/to/gazetteer.rds"
ontoDir <- "directory/to/ontology.rds"Areal data are any data that summarise a certain aspect related to a particular region. Those data are relevant in many applications in the environmental and socio-economic sciences, such as biodiversity check-lists, agricultural statistics, or socio-economic surveys. For applications that surpass the spatial, temporal or thematic scope of any single data source, data must be integrated from several heterogeneous sources. Inconsistent concepts, definitions, or messy data tables make this a tedious and error-prone process.
arealDB has been developed for the purpose of providing an easy-to-use way to integrate areal data together with associated geometries into a standardised database. In the current version it makes use of the ontologics R-package to harmonise the names of territories (the geometries) and the target variables (the tables).
A previous version of this tutorial can be found in the replication script appendix/source of the pre-print. This version is not an exact replication script, as it is built mostly with dummy-code. However, it should still serve to exemplify how to use arealDB in the current version.
To ensure that databases that are built with arealDB are properly standardized, the R-package makes use of a particular data-structure in which concepts and and their hierarchical relationships are recorded. The idea for this structure is based on a broader effort that shall make the internet more interoperable and machine-readable, the "Semantic Web". This can be useful for both, the names of territories (which are typically different across languages and across statistical agencies) and also the values of the variables that shall be recorded. For both registers, arealDB makes use of the R-package ontologics, that allows recording of various concepts in such a data-structure.
[…] More simply, an ontology is a way of showing the properties of a subject area and how they are related, by defining a set of concepts and categories that represent the subject. [wikipedia]
Any target variable can (and should be) recorded in a standardized fashion. It may be extremely tricky to identify a domain-specific standard because this usually involves social processes (people need to agree upon it), however this is not part of this tutorial. For example, concepts such as biological species are part of a complex taxonomy of terms that identify kingdoms, families, species, varieties and many more. Species are nested within families and other hierarchical and synonymous relationships exist in such taxonomies. All these issues are addressed in an ontology (or typology, etc).
A gazetteer is a geographical index or directory used in conjunction with a map or atlas… [wikipedia]
Territory names are recorded in a gazetteer, which can be regarded as an ontology of territorial names. Territorial names are typically nested within regions at several levels, such as counties that are nested in a nation. To showcase how a gazetteer (and also an ontology by extension) should be configured, we are building one for the United Nations geoscheme here.
# The regions and subregions we want to include into the gazetteer
un_region <- c("AFRICA", "AMERICAS", "ANTARCTICA", "ASIA", "EUROPE", "OCEANIA")
un_subregion <- tibble(concept = c(
"Eastern Africa", "Middle Africa", "Northern Africa", "Southern Africa", "Western Africa",
"Caribbean", "Central America", "Northern America", "Southern America",
"Antarctica",
"Central Asia", "Eastern Asia", "Southeastern Asia", "Southern Asia", "Western Asia",
"Eastern Europe", "Northern Europe", "Southern Europe", "Western Europe",
"Australia and New Zealand", "Melanesia", "Micronesia", "Polynesia"),
broader = c(rep(un_region[1], 5), rep(un_region[2], 4), rep(un_region[3], 1),
rep(un_region[4], 5), rep(un_region[5], 4), rep(un_region[6], 4)))
# To start building the gazetteer, we first need to record some meta-data ...
gazetteer <- start_ontology(
name = "gazetteer",
path = paste0(DBDir, "/tables/"),
version = "1.0.0",
code = ".xxx",
description = "UN geomscheme gazetteer",
homepage = "https://en.wikipedia.org/wiki/United_Nations_geoscheme",
license = "CC-BY-4.0",
notes = "This gazetteer nests each nation into the United Nations geoscheme.")
# ... then we define the ontology-specific classes used for recording items of the geoscheme.
# Here we define the three classes 'un_region', 'un_subregion' and 'nation'.
gazetteer <- new_class(
new = "un_region",
target = NA,
description = "region according to the UN geoscheme",
ontology = gazetteer
) %>% new_class(
new = "un_subregion",
target = "un_region", # un_subregion is nested into un_region
description = "sub-region according to the UN geoscheme",
ontology = .
) %>% new_class(
new = "nation",
target = "un_region",
description = "groups of geometries that together form a nation",
ontology = .)
# Finally, the new concepts can be added, they need to associated to a classes we just defined
gazetteer <- new_concept(
new = un_region,
class = "un_region",
ontology = gazetteer)
# when adding concepts that are nested in other concepts, we need to provide the full concept in
# 'broader = '. We can get this with the 'get_concept()' function.
gazetteer <- new_concept(
new = un_subregion$concept,
broader = get_concept(table = un_subregion %>% select(label = broader),
ontology = gazetteer),
class = "un_subregion",
ontology = gazetteer)
write_rds(x = gazetteer, file = gazDir)As mentioned above, any data can be the thematic data of a areal database. For an areal database these are, however, typically data that are some attribute of the region or territory they are associated to or metrics that describe a population, inventory or other set of entities within an area. Thematic data come in all forms and shapes and standardization is often rather poor. This is largely due to the fact that there are no clear and commonly agreed-upon rules on how to set up tables, and most tools that are typically used for recording and storing data do this in vastly different data-structures. To reshape data into a common format, arealDB makes use of schema-descriptions that are built with the tabshiftr R-package (more on this below).
Any areal database connects the thematic data to some set of spatial data (they are called geometries here). Spatial data are typically already quite well standardized so that we only need to record how exactly the data are structured. Spatial data are primarily the geometries themselves, but most of the time they also come with an ID that identifies each geometry and a set of ancillary attributes to each geometry. For spatial data that are supposed to be useful for more than being visualized there should be a name of the territorial units or meta-data that allow identification of those names.
arealDBTo build a new database with arealDB, one has to start with the start_arealDB() function. Also when resuming to work with that database, it is required to run this function again, as it pastes the current root directory into the options of R, based on which paths for files are derived. The argument gazetteer = is mandatory because without the information recorded in a gazetteer it would not be possible to determine the spatial/topological relation between geometries. The argument top = takes that class in the ontology that shall be regarded as the top-most class at which territorial units are distinguished (this might be different to the top-most level in the gazetteer). It is, however, optional to provide an ontology. In case the thematic data do already contain well harmonized value terms, this can be omitted.
start_arealDB(root = DBDir,
gazetteer = gazDir, top = "al1",
ontology = list("targetVar" = ontoDir))As there is sometimes more than one target variable recorded in an areal database, it is possible to provide more than one ontology. When providing the link between target variable and an ontology, it is important that the name of the target variable is a column in the (harmonized) thematic data. In the above example, "targetVar" would have to be the column in any of the thematic data tables that are stored in the database.
Additionally to starting the database, it is part of stage 1 to identify all thematic and geometric data and storing them in the respective folders in DBDir/adb_tables/ and DBDir/adb_geometries/.
In this example we are working with three dataseries, GADM (the Database of global administrative areas), IBGE (the Brazilian Statistical and Geographic agency) and USDA (the United States Department of Agriculture). The second step in any areal database is in registering the dataseries that are relevant. Thematic and spatial data are primarily described by the meta-data that are represented by the dataseries.
regDataseries(name = "gadm",
description = "Database of Global Administrative Areas",
homepage = "https://gadm.org/index.html",
version = "3.6",
licence_link = "https://gadm.org/license.html",
update = TRUE)
regDataseries(name = "ibge",
description = "Instituto Brasileiro de Geografia e Estatistica",
version = "2023.12",
homepage = "https://sidra.ibge.gov.br",
update = TRUE)
# in case no version is available, use the last date you checked the source
regDataseries(name = "usda",
description = "US Dept. of Agriculture",
version = "2023.12",
homepage = "https://www.nass.usda.gov/Quick_Stats/Lite",
update = TRUE)Next, the geometries are registered. Here, we need to provide the dataseries for geometries in gSeries = and various other meta-data. If the geometries are valid for only a particular territorial unit, this can be specified with a dynamic name = value combination. name must, in this case, be a class in the gazetteer (such as nation in our gazetteer). In case the geometries contain several territorial units that shall be handled separately, the territory names are taken from what has been defined as top = in start_arealDB(). Relations between the columns/attributes in the geometry and the gazetteer are defined in the argument label =, where a list that links them is provided.
# Often it makes sense to start with a global set of geometries, especially if databases encompas
# several nations
regGeometry(gSeries = "gadm",
label = list(al1 = "NAME_0"),
archive = "gadm36_levels_gpkg.zip|gadm36_levels.gpkg",
archiveLink = "https://biogeo.ucdavis.edu/data/gadm3.6/gadm36_levels_gpkg.zip",
updateFrequency = "unknown")
regGeometry(gSeries = "gadm",
label = list(al1 = "NAME_0", al2 = "NAME_1"),
archive = "gadm36_levels_gpkg.zip|gadm36_levels.gpkg",
archiveLink = "https://biogeo.ucdavis.edu/data/gadm3.6/gadm36_levels_gpkg.zip",
updateFrequency = "unknown")
regGeometry(gSeries = "gadm",
label = list(al1 = "NAME_0", al2 = "NAME_1", al3 = "NAME_2"),
archive = "gadm36_levels_gpkg.zip|gadm36_levels.gpkg",
archiveLink = "https://biogeo.ucdavis.edu/data/gadm3.6/gadm36_levels_gpkg.zip",
updateFrequency = "unknown")
# For Brazil, there are dedicated geometries available, not so for the US
regGeometry(nation = "Brazil",
gSeries = "ibge",
label = list(al2 = "NM_ESTADO"),
archive = "BR.zip|BRUFE250GC_SIR.shp",
archiveLink = "https://mapas.ibge.gov.br/bases-e-referenciais/bases-cartograficas/malhas-digitais",
updateFrequency = "notPlanned")
regGeometry(nation = "Brazil",
gSeries = "ibge",
label = list(al3 = "NM_MUNICIP"),
archive = "BR.zip|BRMUE250GC_SIR.shp",
archiveLink = "https://mapas.ibge.gov.br/bases-e-referenciais/bases-cartograficas/malhas-digitais",
updateFrequency = "notPlanned")Finally, the thematic data are registered. Here, we first need to define the schema description. To learn more about this, consult the documentation of the tabshiftr R-package. In addition to the gSeries =, thematic data also have a dSeries =, which documents the thematic dataseries that can deviate from the geometry dataseries. In case several tables are available for one dataseries, the argument subset = can be used to specify dynamically what is reported in the respective table. As no two tables can have the same name at stage2 (the result of registering the data), this argument also serves the purpose to give the tables different names.
schema_ibge <-
setCluster(id = "year", left = 1, top = 3, height = 400536) %>%
setIDVar(name = "al2", columns = 1, split = "(?<=\\().*(?=\\))") %>%
setIDVar(name = "al3", columns = 1, split = "^.*?(?=\\s\\()") %>%
setIDVar(name = "year", columns = 3) %>%
setIDVar(name = "commodity", columns = 2) %>%
setObsVar(name = "planted", unit = "ha", columns = 4) %>%
setObsVar(name = "harvested", unit = "ha", columns = 5) %>%
setObsVar(name = "production", unit = "t", columns = 6) %>%
setObsVar(name = "yield", unit = "t/ha", factor = 0.001, columns = 7)
regTable(nation = "Brazil",
subset = "crops",
label = "al3",
dSeries = "ibge",
gSeries = "ibge",
schema = schema_ibge,
begin = 2000,
end = 2018,
archive = "ibge.7z|tabela5457_1990.csv",
archiveLink = "https://sidra.ibge.gov.br/tabela/5457",
nextUpdate = "unknown",
updateFrequency = "annually",
metadataLink = "https://metadados.ibge.gov.br/consulta/estatisticos/operacoes-estatisticas/PA",
metadataPath = "unavailable")
schema_usda <-
setFormat(na_values = "(D)", thousand = ",") %>%
setIDVar(name = "al2", columns = 18) %>%
setIDVar(name = "al3", columns = 23) %>%
setIDVar(name = "year", columns = 32) %>%
setIDVar(name = "commodities", columns = 5) %>%
setObsVar(name = "planted", unit = "ha", factor = 0.4046856422, columns = 39,
key = 9, value = "AREA PLANTED") %>%
setObsVar(name = "harvested", unit = "ha", factor = 0.4046856422, columns = 39,
key = 9, value = "AREA HARVESTED") %>%
setObsVar(name = "production", unit = "t", factor = 0.907185, columns = 39,
key = 9, value = "PRODUCTION") %>%
setObsVar(name = "yield", unit = "kg/ha", factor = 2241.7, columns = 39,
key = 9, value = "YIELD")
regTable(nation = "UnitedStatesOfAmerica",
level = 3,
subset = "surveyFieldCrops",
dSeries = "usda",
gSeries = "gadm", # here, dSeries and gSeries deviate
schema = schema_usda,
begin = 1918,
end = 2018,
archive = "qs.crops_20220129.txt.gz|usda_tons_crops_l3.csv",
archiveLink = "https://quickstats.nass.usda.gov/",
updateFrequency = "annually",
nextUpdate = "unknown",
metadataLink = "https://www.nass.usda.gov/Quick_Stats/index.php",
metadataPath = "unknown")Finally, we need to align all data so that they follow the same structure. This is called normalization,in arealDB, and is carried out with the functions normGeometry() and normTable(), for the respective input data. Geometries are normalized first, because the thematic data are associated to the standardized versions of them.
This process of normalization has been designed so that a user has to interact with the function as little as possible, but some interactions are still required. Especially when working with the names of territories, it is not always completely clear how the names of one dataset translate to names of another dataset. However, in the current version or arealDB this has been simplified further. Via a spatial match, new geometries are intersected with the already harmonised basis and based on the topological relationships (whether a new geometry matches well, is larger or is smaller), the matches are denoted with close, narrower or broader labels and the respective overlap with the harmonised basis. Names and IDs are then either derived (when an overlap is sufficiently large) or the original names are inserted into the gazetteer.
normGeometry(pattern = "Brazil",
outType = "gpkg")
normGeometry(pattern = "UnitedStatesOfAmerica",
outType = "gpkg")When finally normalizing the thematic tables, interaction is required when the ontoMatch = argument has been provided, otherwise no matching of terms of the target variables with the ontology is carried out. Also here, hierarchical relationships can be included by providing the target variable terms in the respective columns in the translation table (that opens automatically when required), or simple 1:1 translations or terms from other taxonomies or languages can be specified.