Harvest the survey data and metadata on European Natural Sciences institutiond and collections gathered by CETAF and DISSCO during the "passport" (self description by managers) activities. These data are collected via Google Forms, Google Sheets, and for the detail of collection, raw Excel documents uploaded in the Google cloud. Data are exposed through an API (not trully a REST but JSON over HTTP).
- This API can keep an history with the differen versions of data, by presenting the most recent ones by default.
- The API can expose the different existing identifiers for collections and institutions, and use them as resolver or query arguments :
- ROR
- GrSCiCOLL (GBIF)
- Grid
- Wikidata
- CETAF specific identifiers (eg. BE-RBINS, FR-CIRAD, ES-ICM-CSIC)
- Index Herbarium
- The API will expose the following types of object
- Institutions (location, contact coordinates, summary of the collections and their size)
- Collections (with link to parent institutions, taxonomic scope)
- Facilities (storage, equipments and labs)
- Expertise (registry of persons, compliant with GDPR)
- We seek interoperability with GBIF, and DISSCO, and plan to map the datastructure by using Latimercore terms
- the API must document the different networks a Museum belongs to (CETAF, DISSCO etc...)
- Most recent data can be pushed to ElasticSearch indexes as this is platform used by the CETAF website and other project partners
- The API is developped in Django (using a Python 3.11 env and brdged to apache via Gunicorn)
- Data are stored in a PostgreSQL database (using JSON field to store the core data)
- Data are read from Google Sheet and Excel spreadhette uploaded in the Google cloud
- Data are pushed to ElasticSearch (aside of its own endpoint in the Web)
Main Python librairies
- Pandas
- pygsheets (to read Google Sheet)
- cUrL
- elasticsearch
- google.oauth2.credentials (to read uploaded Excel)
- googleapiclient (to read uploaded Excel)
- openpyxl (to read uploaded Excel)
Overview :
flowchart LR;
A[Google sheet] -->|Command parser 1| C[Django API]
B[Excel in google cloud] -->|Command parser 2| C[Django API]
C[Django API] -->|command parser 3| D[ElasticSearch index]
Linked Django commands (via shell at the root of the project - manage.py level)
- Command parser 1 and
- Command parser 2
- python3 manage.py loadindb_institutions --extra_apis institution_overview (load institutions from Google Sheet, by default do nothing if more recent data in DB)
- python3 manage.py loadindb_institutions --extra_apis institution_overview --force true (load institutions from Google Sheet, force Google sheet data as being the most recent)
- python manage.py loadindb_institutions --extra_apis grscicoll_institutions grscicoll_collections_from_institutions (loads institutions from GoogleSheet, if the GRSciColl ID is given get metadata from GriSciColl (grscicoll_institutions) and forces the cration of the declared collections (grscicoll_collections_from_institutions)
- python3 manage.py loadindb_institutions --extra_apis collection_overview create collections from the summary Google sheet of the institutions
- python3 manage.py loadindb_collections create collections from uploaded collections sheets (see settings.CETAF_DATA_ADDRESS for params)
- Command parser 3
- python manage.py copy_es --target_index institutions pushes institutions to target ElasticSearch
- python manage.py copy_es --target_index collections pushes collections to target ElasticSearch
classDiagram
class cetaf_api_import_run{
-pk
-institutions_added:jsonb
-institutions_deleted:jsonb
-collections_added:jsonb
-collections_deleted:jsonb
-harvesting_date:timestamp
}
class cetaf_api_institutions_normalized{
-pk
-uuid
-data:jsonb (identifier list)
-creation_date:timestamp
-modification_date:timestamp
-version:int
-current:boolean
}
class cetaf_api_collections_normalized{
-pk
-uuid
-fk_institution_normalized:int
-uuid_institution_normalized
-data:jsonb (identifier list)
-creation_date:timestamp
-modification_date:timestamp
}
class cetaf_api_institutions{
-pk
-uuid
-fk_institution_normalized: int
-fk_parent_collections: int
-uuid_institution_normalized
-cetaf_identifier
-data:jsonb
-harvesting_date:timestamp
-creation_date:timestamp
-is_current:boolean
-version:int
}
class cetaf_api_collections{
-pk
-uuid
-fk_to_parent_collection: int
-fk_to_child_collections: int
-fk_institution_normalized: int
-uuid_institution_normalized
-fk_collection_normalized: int
-uuid_collection_normalized
-cetaf_identifier
-data:jsonb
-harvesting_date:timestamp
-creation_date:timestamp
-is_current:boolean
-version:int
-source_uri:varchar
}
cetaf_api_institutions-->cetaf_api_institutions_normalized
cetaf_api_institutions-->cetaf_api_import_run
cetaf_api_collections_normalized-->cetaf_api_institutions_normalized
cetaf_api_collections-->cetaf_api_institutions_normalized
cetaf_api_collections-->cetaf_api_collections_normalized
cetaf_api_collections-->cetaf_api_import_run
cetaf_api_collections-->cetaf_api_collections
So far (nov. 2024 only the institutions and collections parts are functionnal)
- List all institutions (note the paging mechanism)
- get institutions by identifiers (note the possibilty to change the protocols) :
- https://naturalheritage.africamuseum.be/cetaf_survey_api/institutions/?operation=get_by_id&protocol=ror&values=02v6zg374
- https://naturalheritage.africamuseum.be/cetaf_survey_api/institutions/?operation=get_by_id&protocol=grscicoll&values=LMOB
- https://naturalheritage.africamuseum.be/cetaf_survey_api/institutions/?operation=get_by_id&protocol=index_herbarium&values=LI
- Parameter has path via .htaccess or rewriterule :https://naturalheritage.africamuseum.be/cetaf/api/institutions/ror/02v6zg374
- Fuzzy query (institutions):
- list of collections :
- collections by collectiob identifier :
- https://naturalheritage.africamuseum.be/cetaf_survey_api/collections/?operation=get_by_id&protocol=cetaf&values=BE-MBG%20-%20Algae,%20Fungi,%20Plants%20(Old%20Botany) 7 collections by identifier of the parent institution
- https://naturalheritage.africamuseum.be/cetaf_survey_api/collections/?operation=get_by_institution_id&protocol=wikidata&values=Q655542
Elasticsearch institutions https://naturalheritage.africamuseum.be/cetaf_dissco_institutions_dev
Elasticsearch collections https://naturalheritage.africamuseum.be/cetaf_dissco_collections_dev
Definition of the JSON structure is in settings.py. Principle : the number is the index (0-based of the column in the soruce Google sheet), the term and nested level define the structure of the JSON output
MAPPING_G_SHEET={
"institutions": {
"timestamp":0,
"timestamp_format":"%d/%m/%Y %H:%M:%S",
"timestamp_default":"01/01/0001 00:00:00",
"fields":
{
8: "name_institution_en",
9: "name_institution_en_alternate",
12: "original_name",
"address":
{
21 : "street",
22: "city",
23 :"province",
24: "postcode" ,
25: "country"
}
,
"contact":
{
27: "phone",
28: "mail"
},
"social_network":
{
30: "X",
31: "Facebook",
32: "Instagram",
33: "LinkedIn",
34: "YouTube",
},
"institution_data":
{
10: "logo",
29: "website",
},
"direction":
{
41: "title",
43 : "first_name",
44: "last_name",
45: "phone",
46: "mail",
47: "orcid"
},
"organisation":
{
48: "chart_description",
49: "chart_url",
50: "chart_file",
51: "governing_and_exectutive_bodies",
52: "year_data"
},
58: "cetaf_membership",
"cetaf_representative":
{
59: "title",
61 : "first_name",
62: "last_name",
64: "phone",
63: "mail",
65: "orcid",
66: "role_in_institution"
},
"cetaf_executive_1":
{
69: "title",
71: "first_name",
72: "last_name",
73: "phone",
74: "mail",
75: "orcid",
76: "role_in_institution"
},
"cetaf_executive_2":
{
79: "title",
81: "first_name",
82: "last_name",
83: "phone",
84: "mail",
85: "orcid",
86: "role_in_institution"
}
},
"identifiers_api":
{
35: "ror"
},
"main_identifier_db": {
"fields" :
{
8 :"name_institution_en",
9: "name_institution_en_alternate",
12: "original_name",
},
"null_value":"Other"
},
"other_identifiers":
{
35: "ror",
36: "grid",
37: "index_herbarium",
38: "wikidata",
39: "isni",
40: "grscicoll",
}
,
"apis":
{
#keep this order for grscicoll
"ror":35,
"grscicoll_institutions":40,
"grscicoll_collections_from_institutions" : None
}
}
}
#alternate mapping identifier in column
MAPPING_G_SHEET_OVERVIEW={
"cetaf_collection_overview":
{
"institution_id_fields":
{
"institution_name":4,
"alternate_institution_name":5
},
"appended_excel":
{
"collection_size": { "col":25, "sheet": "Collection overview"}
}
}
}
list_identifiers": [
{
"type": "cetaf",
"value": "AT-BC"
},
{
"type": "cetaf_complete",
"value": "AT-BC Upper Austrian State Museum - Biology Centre"
},
{
"uri": "https://ror.org/No",
"type": "ror",
"value": "No"
},
{
"type": "index_herbarium",
"value": "LI"
},
{
"type": "wikidata",
"value": "Q2011578"
},
{
"uri": "https://scientific-collections.gbif.org/institution/b6d73ad2-2b45-45b0-9a72-6bc7f6251038",
"type": "grscicoll",
"value": "LMOB"
}
],
list_identifiers": [
{
"type": "cetaf",
"value": "EE-EMÜ-ULS - Algae, Fungi, Plants (Old Botany)"
}
],
"institution_list_identifiers": [
{
"type": "cetaf",
"value": "EE-EMÜ-ULS"
},
{
"type": "cetaf_complete",
"value": "EE-EMÜ-ULS Estonian University of Life Sciences"
},
{
"type": "ror",
"value": "00s67c790"
},
{
"type": "grid",
"value": "grid.16697.3f"
},
{
"type": "index_herbarium",
"value": "TAA, TAAM, IZBE"
},
{
"type": "wikidata",
"value": "Q655542"
},
{
"type": "isni",
"value": 106711127
},
{
"type": "grscicoll",
"value": "TAA\nTAAM\nIZBE"
}
]
}
Franck Theeten : Africamuseum Belgium (franck.theeten@africamuseum.be)
Patrick Semal : Head of collection, RBINS, Belgium (p.semal@africamuseum.be)