Import and update of vocabularies used in eposb into PostgreSQL¶

Vocabularies used in eposb¶

Names and source:¶

• Boreholepurposetype vocabulary: INSPIRE
• Boreholestatustype vocabulary: WP15 based on BoreholeML3
• Boreholeusetype vocabulary, same as BoreholePurpose, i.e. INSPIRE
• Collarheadworktype vocabulary: GWML2 hosted by WP15
• Drillingmethodtype vocabulary: IUGS CGI Boreholedrillingmethod extended by WP15
• Elevationmeasurementmethodtype: GWML2 hosted by WP15
• Elevationtype GWML2 collarElevationType hosted by WP15
• Inclinationtype vocabulary: GeoSciML hosted by WP15
• Legalaccessleveltype vocabulary: ISO 19115
• Materialtype vocabulary: ODM2
• Roletype vocabulary: ISO 19115
• Specimentype vocabulary: ODM2
• Startpointtype vocabulary: GeoSciML hosted by WP15

To be implemented:

• Sampleaccesstype (IGSN)
• Sealingtype (GWML2?)
• Sealingmaterialtype (GWML2?)

Vocabulary URLs:¶

all WP15: (https://epos.brgm-rec.fr/datalift/skos/) SPARQL endpoint https://epos.brgm-rec.fr/datalift/sparql
INSPIRE Boreholepurposetype: http://inspire.ec.europa.eu/codelist/BoreholePurposeValue/
ISO 19115 Legalaccesstype: in https://raw.githubusercontent.com/ISO-TC211/GOM/master/isotc211_GOM_harmonizedOntology/iso19115/2006/ConstraintInformation.rdf
ISO 19115 Roletyp: in https://raw.githubusercontent.com/ISO-TC211/GOM/master/isotc211_GOM_harmonizedOntology/iso19115/2006/CitationAndResponsiblePartyInformation.rdf
ODM2 Materialtype: http://vocabulary.odm2.org/api/v1/medium/?format=skos
ODM2 Specimentype: http://vocabulary.odm2.org/api/v1/specimentype/?format=skos

Harvesting of the vocabularies in a SQL script for use in Postgresql¶

Requirements:¶

• Postgresql V9.5 or higher
• python3
• rdflib
• curl

Each vocabulary (names as listed above) has an own database table with three columns that hold the essential information about the used terms: the term itself, its definition and URL (either resolving or only URI in vocabulary namespace, depending on vocabulary). All higher information (ontologies, other relationships) is lost and also not of importance for this purpose:

CREATE TABLE schema."Vocabularyname" (
    "Term" varchar(100) NOT NULL,
    "Definition" varchar NULL,
    "URL" varchar NULL,
    CONSTRAINT "PK_Vocabularyname" PRIMARY KEY ("Term")
);

In addition, one table is used for vocabulary import/update:

CREATE TABLE epos_dev."Vocabulary_import" (
    "Term" varchar(100) NOT NULL,
    "Definition" varchar NULL,
    "URL" varchar NULL,
    CONSTRAINT "PK_Vocabulary_import" PRIMARY KEY ("Term")
);

The vocabularies are queried via SPARQL:

• if present, at the SPARQL endpoint and retrieved by curl
• xml/rdf files are retrieved and analysed by a SPARQL query in rdflib. This requires short python scripts to be stored in a location ("scriptpath") accessible to the database server.

A) skos-query.py:

import rdflib
import argparse

parser = argparse.ArgumentParser()
parser.add_argument("href")
args = parser.parse_args()

g = rdflib.Graph()
g.parse((args.href), format="xml")

qstr = """SELECT DISTINCT ?Label ?Definition ?URL
   WHERE {
      ?URL <http://www.w3.org/2004/02/skos/core#prefLabel> ?Label ;
           <http://www.w3.org/2004/02/skos/core#definition> ?Definition .
   }""" 

for row in g.query(qstr):
    print(f'"{row.Label}","{row.Definition}","{row.URL}"')

B) iso-CI_RoleCodeType-query.py

import rdflib
import argparse

parser = argparse.ArgumentParser()
parser.add_argument("href")
args = parser.parse_args()

g = rdflib.Graph()
g.parse((args.href), format="xml")

qstr = """SELECT DISTINCT ?Label ?Definition ?URL
   WHERE {
      <http://def.isotc211.org/iso19115/2006/CitationAndResponsiblePartyInformation/code/CI_RoleCodeCollection> <http://www.w3.org/2004/02/skos/core#member> ?URL .
      ?URL <http://www.w3.org/2004/02/skos/core#prefLabel> ?Label .
      ?URL <http://www.w3.org/2000/01/rdf-schema#isDefinedBy> ?Definition .
   }""" 

for row in g.query(qstr):
    print(f'"{row.Label}","{row.Definition}","{row.URL}"')

C) iso-MD_RestrictionCodeType-query.py

import rdflib
import argparse

parser = argparse.ArgumentParser()
parser.add_argument("href")
args = parser.parse_args()

g = rdflib.Graph()
g.parse((args.href), format="xml")

qstr = """SELECT DISTINCT ?Label ?Definition ?URL
   WHERE {
      <http://def.isotc211.org/iso19115/2006/ConstraintInformation/code/MD_RestrictionCodeCollection> <http://www.w3.org/2004/02/skos/core#member> ?URL .
      ?URL <http://www.w3.org/2004/02/skos/core#prefLabel> ?Label .
      ?URL <http://www.w3.org/2000/01/rdf-schema#isDefinedBy> ?Definition .
   }""" 

for row in g.query(qstr):
    print(f'"{row.Label}","{row.Definition}","{row.URL}"')

Eventually, the following SQL script will import all vocabulary terms, defintions & URLs into the respective database table (=> PROCEDURE in future PostgreSQL V11 and higher). It imports the terms of a vocabulary into the Vocabulary_import table. Then the terms are copied to the respective vocabulary table. Existing terms will be updated, new ones added. However, terms that are no longer part of the vocabulary will not be touched (an operator is need to judge how this affects the data) and no warning will be issued either! This allows also the addition of own terms.
Vocabularies are in alphabetical order for easier editing (implement it in python if you don't have a place where the server can access the python script or if you just want less and nicer code ...)

-- IMPORT or UPDATE vocabularies in the borehole database for eposb
-- all python scripts on dev machine are in /opt/dev/epos

/* To be implemented:
 *    - Sampleaccesstype (IGSN)
 *    - Sealingtype (GWML2?)
 *    - Sealingmaterialtype (GWML2?)
 *    - Locationmeasurmenttype ?
 *    - Datastatustype ?
 */

-- Empty import helper database table
truncate epos_dev."Vocabulary_import";

-- Boreholepurposetype vocabulary: INSPIRE
copy epos_dev."Vocabulary_import" from PROGRAM 'python3 /opt/dev/epos/skos-query.py http://inspire.ec.europa.eu/codelist/BoreholePurposeValue/BoreholePurposeValue.en.rdf' with (FORMAT csv);

insert into epos_dev."Boreholepurposetype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Boreholepurposetype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Boreholepurposetype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Boreholepurposetype"."Term" = "Vocabulary_import"."Term");

    -- Add additional value until approved by INSPIRE
    insert into epos_dev."Boreholepurposetype" 
       values ('multidisciplinary scientific research', 'A borehole drilled for purely scientific reasons.', 'http://inspire.ec.europa.eu/codelist/BoreholePurposeValue/BoreholePurposeValue/multidisciplinaryScientificResearch')
    on conflict on constraint "PK_Boreholepurposetype" 
       do nothing;         

truncate epos_dev."Vocabulary_import";

-- Boreholestatustype vocabulary: WP15 based on BoreholeML3
copy epos_dev."Vocabulary_import" from PROGRAM 'curl -H "Accept: text/csv" "https://data.geoscience.earth/ncl/system/query?query=SELECT+DISTINCT+%3FTerm+%3FDefinition+%3FURL+WHERE%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23prefLabel%3E%3FTerm.OPTIONAL%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23definition%3E%3FDefinition%3B%7DFILTER%28regex%28str%28%3FURL%29%2C%22https%3A%2F%2Fdata.geoscience.earth%2Fncl%2FBoreholeStatus%2F%22%29%29%7D"' with (FORMAT csv, header);

insert into epos_dev."Boreholestatustype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Boreholestatustype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Boreholestatustype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Boreholestatustype"."Term" = "Vocabulary_import"."Term");

    -- Add additional value until set up at WP15
    insert into epos_dev."Boreholestatustype" 
       values ('abandoned', 'A borehole that is not accessible any more and is overgiven by its operator.', 'https://data.geoscience.earth/ncl/BoreholeStatus/abandoned')
    on conflict on constraint "PK_Boreholestatustype" 
       do nothing;         

truncate epos_dev."Vocabulary_import";

-- Boreholeusetype vocabulary, same as BoreholePurpose but kept separately in case this will change
copy epos_dev."Vocabulary_import" from PROGRAM 'python3 /opt/dev/epos/skos-query.py http://inspire.ec.europa.eu/codelist/BoreholePurposeValue/BoreholePurposeValue.en.rdf' with (FORMAT csv);

insert into epos_dev."Boreholeusetype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Boreholeusetype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Boreholeusetype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Boreholeusetype"."Term" = "Vocabulary_import"."Term");

    -- Add additional value until approved by INSPIRE
    insert into epos_dev."Boreholeusetype" 
       values ('multidisciplinary scientific research', 'A borehole drilled for purely scientific reasons.', 'http://inspire.ec.europa.eu/codelist/BoreholePurposeValue/BoreholePurposeValue/multidisciplinaryScientificResearch')
    on conflict on constraint "PK_Boreholeusetype" 
       do nothing;         

truncate epos_dev."Vocabulary_import";

-- Collarheadworktype vocabulary: GWML2 hosted by WP15
copy epos_dev."Vocabulary_import" from PROGRAM 'curl -H "Accept: text/csv" "https://data.geoscience.earth/ncl/system/query?query=SELECT+DISTINCT+%3FTerm+%3FDefinition+%3FURL+WHERE%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23prefLabel%3E%3FTerm.OPTIONAL%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23definition%3E%3FDefinition%3B%7DFILTER%28regex%28str%28%3FURL%29%2C%22https%3A%2F%2Fdata.geoscience.earth%2Fncl%2FCollarHeadworkType%2F%22%29%29%7D"' with (FORMAT csv, header);

insert into epos_dev."Collarheadworktype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Collarheadworktype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Collarheadworktype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Collarheadworktype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Drillingmethodtype vocabulary: WP15 based on IUGS CGI Boreholedrillingmethod
copy epos_dev."Vocabulary_import" from PROGRAM 'curl -H "Accept: text/csv" "https://data.geoscience.earth/ncl/system/query?query=SELECT+DISTINCT+%3FTerm+%3FDefinition+%3FURL+WHERE%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23prefLabel%3E%3FTerm.OPTIONAL%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23definition%3E%3FDefinition%3B%7DFILTER%28regex%28str%28%3FURL%29%2C%22https%3A%2F%2Fdata.geoscience.earth%2Fncl%2FDrillingMethod%2F%22%29%29%7D"' with (FORMAT csv, header);

insert into epos_dev."Drillingmethodtype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Drillingmethodtype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Drillingmethodtype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Drillingmethodtype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Elevationmeasurementmethodtype vocabulary: GWML2 hosted by WP15
copy epos_dev."Vocabulary_import" from PROGRAM 'curl -H "Accept: text/csv" "https://data.geoscience.earth/ncl/system/query?query=SELECT+DISTINCT+%3FTerm+%3FDefinition+%3FURL+WHERE%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23prefLabel%3E%3FTerm.OPTIONAL%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23definition%3E%3FDefinition%3B%7DFILTER%28regex%28str%28%3FURL%29%2C%22https%3A%2F%2Fdata.geoscience.earth%2Fncl%2FElevationMeasurementMethod%2F%22%29%29%7D"' with (FORMAT csv, header);

insert into epos_dev."Elevationmeasurementmethodtype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Elevationmeasurementmethodtype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Elevationmeasurementmethodtype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Elevationmeasurementmethodtype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Elevationtype vocabulary: GWML2 hosted by WP15
copy epos_dev."Vocabulary_import" from PROGRAM 'curl -H "Accept: text/csv" "https://data.geoscience.earth/ncl/system/query?query=SELECT+DISTINCT+%3FTerm+%3FDefinition+%3FURL+WHERE%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23prefLabel%3E%3FTerm.OPTIONAL%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23definition%3E%3FDefinition%3B%7DFILTER%28regex%28str%28%3FURL%29%2C%22https%3A%2F%2Fdata.geoscience.earth%2Fncl%2FElevationType%2F%22%29%29%7D"' with (FORMAT csv, header);

insert into epos_dev."Elevationtype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Elevationtype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Elevationtype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Elevationtype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Inclinationtype vocabulary: GeoSciML hosted by WP15
copy epos_dev."Vocabulary_import" from PROGRAM 'curl -H "Accept: text/csv" "https://data.geoscience.earth/ncl/system/query?query=SELECT+DISTINCT+%3FTerm+%3FDefinition+%3FURL+WHERE%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23prefLabel%3E%3FTerm.OPTIONAL%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23definition%3E%3FDefinition%3B%7DFILTER%28regex%28str%28%3FURL%29%2C%22https%3A%2F%2Fdata.geoscience.earth%2Fncl%2FBoreholeInclinationType%2F%22%29%29%7D"' with (FORMAT csv, header);

insert into epos_dev."Segmentinclinationtype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Segmentinclinationtype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Segmentinclinationtype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Segmentinclinationtype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Legalaccessleveltype vocabulary: ISO TC211 GOM hosted on GitHub
copy epos_dev."Vocabulary_import" from PROGRAM 'python3 /opt/dev/epos/iso-MD_RestrictionCodeType-query.py https://raw.githubusercontent.com/ISO-TC211/GOM/master/isotc211_GOM_harmonizedOntology/iso19115/2006/ConstraintInformation.rdf' with (FORMAT csv);

insert into epos_dev."Legalaccessleveltype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Legalaccessleveltype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Legalaccessleveltype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Legalaccessleveltype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Materialtype vocabulary: ODM2
copy epos_dev."Vocabulary_import" from PROGRAM 'python3 /opt/dev/epos/skos-query.py http://vocabulary.odm2.org/api/v1/medium/?format=skos' with (FORMAT csv);

insert into epos_dev."Materialtype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Materialtype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Materialtype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Materialtype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Roletype vocabulary: ISO TC211 GOM hosted on GitHub
copy epos_dev."Vocabulary_import" from PROGRAM 'python3 /opt/dev/epos/iso-CI_RoleCodeType-query.py https://raw.githubusercontent.com/ISO-TC211/GOM/master/isotc211_GOM_harmonizedOntology/iso19115/2006/CitationAndResponsiblePartyInformation.rdf' with (FORMAT csv);

insert into epos_dev."Roletype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Roletype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Roletype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Roletype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Specimentype vocabulary: ODM2
copy epos_dev."Vocabulary_import" from PROGRAM 'python3 /opt/dev/epos/skos-query.py http://vocabulary.odm2.org/api/v1/specimentype/?format=skos' with (FORMAT csv);

insert into epos_dev."Specimentype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Specimentype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Specimentype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Specimentype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Startpointtype vocabulary: GeoSciML hosted by WP15
copy epos_dev."Vocabulary_import" from PROGRAM 'curl -H "Accept: text/csv" "https://data.geoscience.earth/ncl/system/query?query=SELECT+DISTINCT+%3FTerm+%3FDefinition+%3FURL+WHERE%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23prefLabel%3E%3FTerm.OPTIONAL%7B%3FURL%3Chttp%3A%2F%2Fwww.w3.org%2F2004%2F02%2Fskos%2Fcore%23definition%3E%3FDefinition%3B%7DFILTER%28regex%28str%28%3FURL%29%2C%22https%3A%2F%2Fdata.geoscience.earth%2Fncl%2FStartPoint%2F%22%29%29%7D"' with (FORMAT csv, header);

insert into epos_dev."Startpointtype" 
     select "Term", "Definition", "URL" from epos_dev."Vocabulary_import" 
on conflict on constraint "PK_Startpointtype" 
     do update
       set "Definition" = (select "Definition" from epos_dev."Vocabulary_import" where "Startpointtype"."Term" = "Vocabulary_import"."Term"),
           "URL" = (select "URL" from epos_dev."Vocabulary_import" where "Startpointtype"."Term" = "Vocabulary_import"."Term");

truncate epos_dev."Vocabulary_import";

-- Custodiantype local vocabulary: for handling different types of custodians in the scientific drilling database

insert into epos_dev."Custodiantype" ("Term", "Definition")
     values
       ('data', 'The data custodian takes care of the curation and archiving of the original data from a borehole.'),
       ('material', 'The borehole material custodian takes care of the curation and archiving of the sample material from a borehole.');