Modelo de datos para colecciones biológicas

hacia la consolidación de datos Conabio-Inecol

conceptos
datos
propuestas
Autores/as

Miguel Equihua

Elio Lagunes

Fecha de publicación

Xalapa, Ver., 13 febrero 2024

Hemos recibido de Conabio los datos de los ejemplares de las colecciones biológicas del Inecol. Se trata de los registros derivados de los proyectos de sistematización digital que esa institución ha apoyado. Nos compartieron la información como una base de datos relacional en el dialecto Postgresql. El modelo de datos se ilustra en la Figura 1. Puede apreciarse que el contenido medular de las colecciones se distribuye en tres tablas: ejemplar, nombre y geografía. Hay otras cinco tablas en el modelo de datos, que básicamente son catálogos necesarios para normalizar la captura de los datos y sistemas de referencia usados por Conabio para operar en forma estandarizada en el contexto internacional principalmente.

Figura 1. Modelo de datos de la información sobre ejemplares existentes en colecciones biológicas

Figura 1. Modelo de datos de la información sobre ejemplares existentes en colecciones biológicas

Organización de las tablas

El modelo de datos mostrado en la Figura 1 es sugerente de la naturaleza e intensión de usos de la abstracción propuesta al sistematizar los datos de colecciones biológicas en general. Un dato adicional nos lo da la cantidad de piezas de información recolectadas, lo que corresponde a las columnas en las tablas. Hagamos ahora un recuento de las columnas que tiene cada tabla en el modelo.

Código 
q_cols_tabs <- paste("select table_name, count(column_name)",
                     "from information_schema.columns", 
                     "where table_schema = 'public'",
                     "group by table_name",
                     "order by table_name")

q_num_cols <- dbSendQuery(con, q_cols_tabs)
cols_tbls <- dbFetch(q_num_cols)
dbClearResult(q_num_cols)

cols_tbls %>% 
  select(table_name, count) %>% 
  flextable() %>%
  set_header_labels(table_name = "Tabla", count = "Núm. columnas") %>% 
  width(width = c(5, 3), unit = "cm")

 

Tabla

Núm. columnas

catalogoejemplar

3

catalogonombre

3

conabioejemplar

4

conabiogeografia

55

conabionombre

113

ejemplar

45

geografia

31

nombre

26

 

 

Tabla 1. Tamaño de las tablas en número de colmnas que registran.

 

Información recibida

El número de registros que tienen las tablas que recibimos es el siguiente.

Código 
q_reg_tabls <- paste("WITH tbl AS",
                     "(SELECT table_schema, table_name",
                      "FROM information_schema.tables",
                      "WHERE table_name not like 'pg_%' AND table_schema in ('public'))",
                      "SELECT table_schema, table_name,",
                      "       (xpath('/row/c/text()',", 
                      "              query_to_xml(format('select count(*)",
                      "                                  as c from %I.%I',",
                      "                                  table_schema, table_name),", 
                      "                                   FALSE, TRUE, ''))",
                      "       )[1]::text::int AS rows_n",
                      "FROM tbl ORDER BY rows_n DESC")

q_tam_tabls <- dbSendQuery(con, q_reg_tabls)
tam_tbls <- dbFetch(q_tam_tabls)
dbClearResult(q_tam_tabls)

tam_tbls %>% 
  select(table_name, rows_n) %>% 
  flextable() %>%
  set_header_labels(table_name = "Tabla", rows_n = "Núm. registros") %>% 
  width(width = c(5, 3), unit = "cm")

Tabla

Núm. registros

catalogoejemplar

4,675,149

conabioejemplar

569,287

ejemplar

569,287

conabiogeografia

76,273

geografia

72,343

nombre

56,924

conabionombre

56,772

catalogonombre

28,231

 

Tabla 2. Registros existentes en cada una de las tablas recibidas.

 

Código 
q_proy <- paste("SELECT proyecto, COUNT(ejemplar) AS ejemplares",
                "  FROM ejemplar", 
                "GROUP BY proyecto", 
                "ORDER BY proyecto")

q_proy <- dbSendQuery(con, q_proy)
ejemp_proy <- dbFetch(q_proy)
dbClearResult(q_proy)


ejemp_proy %>% 
  mutate(ejemplares = as.integer(ejemplares)) %>% 
  flextable() %>%
  set_header_labels(table_name = "Proyecto", rows_n = "Núm. registros") %>% 
  width(width = c(5, 3), unit = "cm") %>% 
  colformat_int(j = 2, big.mark = ",")

proyecto

ejemplares

AA014

2,562

EE005

22,180

HA010

55,373

K005

5,069

ME014

321,707

Q017

162,396

 

Tabla 3. Número de registros relacionados con cada proyecto apoyado por Conabio.

 

Paseo por los datos

Para propósitos curatoriales, sin duda, la unidad básica es el ejemplar y hay precisamente una tabla al respecto. A continuación mostramos a manera de ejemplo una consulta sencilla para explorar las fuentes de las colecciones del Inecol registradas en esta tabla.



Código 
q_cont <- dbSendQuery(con, "SELECT nombrecoleccion, count(llaveejemplar) FROM ejemplar group by nombrecoleccion")
conteo <- dbFetch(q_cont)
dbClearResult(q_cont)

tot <- sum(as.numeric(conteo$count))
num_colecs <- length(conteo$count)

conteo <- rbind(conteo, 
                    c(nombrecoleccion = "Total", 
                      count = as.character(tot)))

borde <- officer::fp_border(color = "red", style = "solid", width = 1)


conteo %>%
  mutate(count = as.integer(count)) %>% 
  flextable() %>%
  set_header_labels(nombrecoleccion = "Colección", count = "Ejemplares") %>% 
  width(j = 1, 7) %>% 
  colformat_int(j = 2, big.mark = ",") %>% 
  hline(i = num_colecs, border = borde)

Colección

Ejemplares

Arthropodes & Entomologie

21

Barney D. Streit Personal Collection (Coleoptera)

1

Coleção de Insetos

2

Colección de Artrópodos

2

Colección de Federico Escobar Sarria (Entomológica)

3,412

Colección de Hongos

5,012

Colección del Servicio Entomológico Autónomo

2

Colección Entomológica

14,020

Colección Herpetológica

247

Colección Mastozoológica

45

Colección Miguel Ángel Morón (Coleoptera, Lamellicornia)

437

Colección Nacional de Anfibios y Reptiles

1

Colección Nacional de Insectos

86

Colección Nacional de Insectos, Estación de Biología Chamela

22

Colección Nacional Entomológica Forestal 'Biól. Raúl Muñiz Vélez'

1

Colección Particular de Aristeo Cuauhtémoc Deloya López

143

Colección Particular de Bruce D. Gill (Scarabaeidae, Coleoptera)

2

Colección Particular de Carmen Huerta

2

Colección Particular de Darío Navarrete Gutiérrez

4,413

Colección Particular de Delbert A. La Rue (Scarabaeidae, Coleoptera)

1

Colección Particular de Eduardo Pineda Arredondo

21

Colección Particular de Enrique Montes de Oca T.

3,141

Colección Particular de Federico Solórzano

117

Colección Particular de Fernando Z. Vaz de Mello (Coleoptera)

1,870

Colección Particular de Francisco J. Cabrero-Sañudo

3

Colección Particular de Giovanni Dellacasa (Coleoptera)

239

Colección Particular de Gonzalo Halffter

1,503

Colección Particular de L. E. Rivera

18

Colección Particular de Luis Leonardo Delgado

7

Colección Particular de Mario Zunino (Coleoptera)

1

Colección Particular de Mathieu, J.

10

Colección Particular de R. Sánchez G.

128

Colección Particular de Roberto Arce Pérez

1

Colección Particular de Sofía Anduaga

261

Colección Pedro Reyes Castillo (Coleoptera, Passalidae)

3,916

Collection d'insectes

11

Collection of Herpetology

2,092

Collezioni Zoologiche

280

Dudley Herbarium

145

Entomology Collection

106

Gray Herbarium

1,827

Henry and Anne Howden Personal Collection (Entomology)

291

Herbario

292,958

Herbario-Hortorio

28

Herbario 'Graciela Calderón y Jerzy Rzedowski'

217,770

Herbario 'Mtra. Graciela Calderón Díaz-Barriga y Dr. Jerzy Rzedowski Rotter'

1

Herbario Alfredo Barrera Marín

17

Herbario Arturo Gómez-Pompa

1

Herbario Etnobotánico

1

Herbario Jerzy Rzedowski y Graciela Calderón

646

Herbario María Agustina Batalla

6

Herbario Nacional de México, Plantas Vasculares

1,188

Herbario Nacional Forestal Biól. Luciano Vela Gálvez

1

Herbarium

841

Herbarium of the Arnold Arboretum

153

Herbier

103

Herpetology Collection

541

Insect Collection

616

Insecta Collection

1

Insectario

7

Insects, Arachnids and Myriapods Collection

10

Invertebrate Zoology Collection

3

Jardín Botánico Francisco Javier Clavijero

391

John G. Searle Herbarium

178

Kew Herbarium

405

National Entomological Collection

109

NO APLICA

2,441

NO DISPONIBLE

6,487

Paul K. Lago Personal Collection (Entomology)

1

Recent Invertebrate Collection

7

Richard A. Cunningham Personal Collection (Coleoptera)

1

Scott McCleve Personal Collection (Entomology)

2

United States National Herbarium

123

W. D. Edmonds Personal Collection (Coleoptera)

14

Xiloteca Dr. Faustino Miranda

376

Total

569,287

 

Tabla 4. Número de ejemplares por colección.

 



Tabla ejemplar

Explorando el contenido de la Tabla de ejemplares podemos ver que tiene 45 columnas, lo que junto con la estructura de relaciones puede expresar en forma muy rica la naturaleza geográfica, ecológica, morfológica y taxonómica de los ejemplares.

La base de datos está ampliamente documentada internamente. La consulta que sigue nos permite averiguar detalles del propósito o forma de uso de las distintas columnas. Por ejemplo, puede interesarnos comprender claramente como operan las que se usan como claves externas que controlan la vinculación con otras tablas.



Código 
# En SQL postgres, los operadores || = concatena y :: = cast a tipo de dato
# regclass es un tipo de dato "mágico" es una 
# especie de alias de oid: "object identifier".
q_descr <- paste("SELECT column_name, col_description(",
                 "(table_schema||'.'||table_name)::regclass::oid, ",
                 "ordinal_position) as column_comment",
                 "FROM information_schema.columns",
                 "WHERE table_schema = 'public'",
                 "and table_name = 'ejemplar'")

q_describe <- dbSendQuery(con, q_descr)
cols_describe <- dbFetch(q_describe)
dbClearResult(q_describe)

cols_describe %>% 
  datatable(colnames = c("Columna", "Descripción"), rownames = FALSE,
            options = list(initComplete = 
                             JS("function(settings, json) {",
                                paste0("$(this.api().table().header()).css(",
                                       "{'font-size': '14px', ", 
                                       "'background-color': '#c2d1f0', ",
                                       "'color': '#000'});"),
                                             "}"))) %>%  
  formatStyle(columns = colnames(.$x$data), `font-size` = '14px')

 

Tabla 5. Descripción del contenido en Tabla ‘ejemplar’.

 



Uso de la base de datos

Distribución geográfica de Tabebuia

Ahora queremos mostrar un ejemplo del potencial relacional del modelo de datos. Lo haremos interrogando a la base de datos sobre las especies del género Tabebuia. Hay que notar que hacer esto implica recurrir a la relación entre las tablas ejemplar y nombre, pues la primera sólo registra datos del origen (el colector por ejemplo) y de la situación de preservación del espécimen, pero no registra ningún dato taxonómico. Ni siquiera el binomio latino. Algo similar ocurre con la localización de los sitios de colecta. Para obtener sus datos debemos recurrir a la tabla geografia. El resultado de hacer todo esto se muestra a continuación.

Código 
q_sp_geo <- paste(
           "SELECT llaveejemplar as especimen, nombrevalidocatscat as sp,",
           "       paisoriginal as pais,  estadooriginal as estado,",
           "       nombrecolector as colector, latitudgrados as lat_g,",
           "       latitudminutos as lat_m, latitudsegundos as lat_s,",
           "       longitudgrados as lon_g, longitudminutos as lon_m,",
           "       longitudsegundos as lon_s",
           "FROM conabionombre", 
           "  JOIN ejemplar on ejemplar.llavenombre=conabionombre.llavenombre",
           "  JOIN geografia on ejemplar.llavesitio = geografia.llavesitio",
           "WHERE conabionombre.grupo='Plantas' and",
           "      conabionombre.nombrevalidocatscat like 'Tabebuia%'",
           "ORDER BY  sp")

# Realiza la consulta a la base de datos
q_sp_geo <- dbSendQuery(con, q_sp_geo)
sp_geo <- dbFetch(q_sp_geo)
dbClearResult(q_sp_geo)

sp_geo %>% select(sp, colector, estado, pais, especimen) %>% 
  datatable(colnames = c("Especie", "Colector", "Estado o provincia", 
                         "País", "ejemplar ID"), filter = "top", 
            rownames = FALSE,
            options = list(initComplete = 
                             JS("function(settings, json) {",
                                paste0("$(this.api().table().header()).css(",
                                       "{'font-size': '14px', ", 
                                       "'background-color': '#c2d1f0', ",
                                       "'color': '#000'});"),
                                             "}"))) %>%  
  formatStyle(columns = colnames(.$x$data), `font-size` = '12px')

 

Tabla 6. Datos relacionados con las especies de Tabebuia.

 



 

Podemos hacer un mapa a partir de los datos disponibles, pues la tabla geografia también registra la latitud y la longitud de los sitios de colecta.

Código 
# Prepra los datos geográficos

sp_geo <- sp_geo %>% 
          mutate(lat_dec = lat_g + lat_m / 60 + lat_s / 3600,
                 lon_dec = -(lon_g + lon_m / 60 + lon_s / 3600)) # west

library(leaflet)
m <- leaflet() %>% setView(lng = -100, lat = 23, zoom = 4.5)
m %>% addProviderTiles(providers$Esri.NatGeoWorldMap) %>% 
  addCircles(data = sp_geo, lng = ~lon_dec, lat = ~lat_dec, 
             color =  c("blue", "green", "red")) %>% 
  addLegend("topright", colors = c("blue", "green", "red"), 
            labels = unique(sp_geo$sp),
            title = "Tabebuia spp",
    opacity = 1
  )

Figura 2. Mapa de distribución de los ejemplares de Tabebuia, registrados en la base de datos.




¿Cuántos invertebrados hay regsitrados?

En el proceso de valoración de lo que amablemente recibimos de Conabio, nos interesó saber cuántos registros de invertebrados habría en la base de datos compartida. Hicimos una consulta para obtener esta información y los resultados se muestran en el cuadro siguiente.

Código 
q_invert <- paste("SELECT NO.grupo, NO.divisionphylumcat, NO.clasecat, NO.ordencat, EJ.proyecto, count(*)", 
                  "  FROM ejemplar AS EJ", 
                  "  JOIN conabiogeografia as GEO", 
                  "    ON EJ.llaveregionsitiosig = GEO.llaveregionsitiosig", 
                  "  JOIN conabionombre as NO",
                  "    ON EJ.llavenombre = NO.llavenombre", 
                  "WHERE NO.grupo = 'Invertebrados'",
                  "GROUP BY NO.grupo, NO.divisionphylumcat, NO.clasecat, NO.ordencat, EJ.proyecto")

# Realiza la consulta a la base de datos
q_invert <- dbSendQuery(con, q_invert)
invertebrados <- dbFetch(q_invert)
dbClearResult(q_invert)

invertebrados %>% select(grupo, proyecto, divisionphylumcat, clasecat, ordencat, count) %>% 
  datatable(colnames = c("Grupo", "Proyecto", "Phylum", "Clase", 
                         "Orden", "Núm. registros"), filter = "top", 
            rownames = FALSE,
            options = list(initComplete = 
                             JS("function(settings, json) {",
                                paste0("$(this.api().table().header()).css(",
                                       "{'font-size': '14px', ", 
                                       "'background-color': '#c2d1f0', ",
                                       "'color': '#000'});"),
                                             "}"))) %>%  
  formatStyle(columns = colnames(.$x$data), `font-size` = '12px')

Tabla 7. Registros de inverterados en la base de datos.

El SNIB

La Figura 2 ilustra los ámbitos que convergen en la configuración de los datos sobre biodiversidad, desde la perspectiva del Sistema Nacional de Información sobre Biodiversidad de México (SNIB). Mucha más información sobre el modelo de datos que usa actualmente la Conabio puede encontrarse en el sitio Web del SNIB.

Figura 3. Diagrama conceptual de dominios que se involucran en la gestión de la información sobre biodiversidad

Figura 3. Diagrama conceptual de dominios que se involucran en la gestión de la información sobre biodiversidad



También puede interesar saber que el modelo de datos que utiliza el SNIB actualmente es más amplio que el que nos compartió Conabio con los datos de las colecciones del Inecol.