Slegger: Semantic web resources for Slegge database

The resources listed here are for use with Ontology-Based Data Access (OBDA). They are described in detail in our report (an extended version of Ontology-Based Data Access to Slegge, accepted for publication in the ISWC17 resource track). The resources are the results of an industrial use case in the EU project Optique, and are gathered from a database in use, with queries from users of that database.

Slegge OBDA system specification

The specification of an OBDA system consists of ontology, mappings, database schema(s), queries and data. We provide all these below except the data, which are private and valuable and cannot be shared. To mitigate the lack of data we publish some simple statistics on the schemas. The resources presented here are licensed under the CC Attribution 4.0 International Public License. We have also made all files available in an archive archive.zip

Overview diagram of the mappings

Ontology

The ontology was constructed to describe parts of the petroleum subsurface exploration domain and capture the classes and properties from the user information needs, e.g., fields, wells and wellbores. We have provided it in the formats Turtle and RDF/XML.

Information Needs and SPARQL Queries

There are two types of queries listed:

Mappings

The mappings are toward the SLEGGE schema, although the SLEGGE_EPI schema is also accessed indirectly via views. We also made a diagram to get an easier overview over the mappings. The numbers in the diagram refer to the last part of the IRIs of the individual mappings in the mapping file.

Schemas

Overview diagram of the slegge and
	slegge_epi schemas

The schema consists of four files: Two sql scripts and two java source code files:

The java file is used for a stored procedure to calculate depths along the wellbore.

The database schemas come from a database actually in use in an international oil company. We have extracted only the parts used by the mappings, so as not to make a too confusing installation. Apart from this, we have tried to keep as close to the original schema as is useful. Note that there are two schemas included, and both must be installed, although only one of the schemas is explicitly queried from the mappings.

The database schemas are made for Oracle Database, and will not work with other databases. The smaller Oracle Express versions do not support loading external java code, which is needed for important parts of the schema. The original database is on version 10g, but we have used 12c for testing the schema as presented here. It is non-trivial and destructive to the schema to translate this into other databases, so we have only provided the most realistic setup. This is not an endorsement of Oracle, only an attempt at staying true to the original setup.

Schema installation

Schema installation with Java <= 6

As the instructions above, with two changes:

Statistics

The statistics show the number of rows in each table in slegge_epi, and the number of nulls and distinct values for each column in these tables. Only these are shown, since the other schema (slegge) contains views and materialized views generated from these in slegge_epi.

Example

To illustrate the context in which this resource is used, we use an example from the paper OBDA to Slegge. An example of a typical query from the domain experts (geologiststs and geoscientists) is:

Give me the names and chronostratigraphic units found in the available wellbores as well as the top depths of the intervals they were found in; the values should be in the standard units and from standard reference points (depth in metres along the drill string). Also return all the lithostratigraphic zones from depths overlapping with the depths at which the chronostratigraphic zones were found.

This query can be expressed as a SPARQL query over the subsurface-exploration ontology as follows:

SELECT ?wellbore ?chronostrat_unit ?top_md_m ?lithostrat_unit
WHERE {
  ?w a :Wellbore ;
    :name ?wellbore ;
    :hasWellboreInterval ?intv .
  ?intv a :StratigraphicZone ;
    :hasUnit ?cu ;
    :hasTopDepth ?top .
  ?cu :name ?chronostrat_unit ;
    :ofStratigraphicColumn [ a :ChronoStratigraphicColumn ] .
  ?top a :MeasuredDepth ;
    :valueInStandardUnit ?top_md_m .
  ?intv :overlapsWellboreInterval ?litho_intv .
  ?litho_intv :hasUnit ?lu .
  ?lu :name ?lithostrat_unit ;
    :ofStratigraphicColumn [ a :LithoStratigraphicColumn ] .
}

However, the end user will not have to write this query, as there exist several tools to construct SPARQL queries visually. This example is made with the OptiqueVQS.

Finally, when using an optimised rewriting-based OBDA tool, the SPARQL query above together with the ontology and mappings provided here, will lead to this SQL query:

SELECT 
  QVIEW1."IDENTIFIER" AS "wellbore", 
  QVIEW2."STRAT_UNIT_IDENTIFIER" AS "chronostrat_unit", 
  QVIEW15."STRAT_UNIT_IDENTIFIER" AS "lithostrat_unit", 
  QVIEW2."STRAT_ZONE_ENTRY_MD" AS "top_md_m"
 FROM 
  "WELLBORE" QVIEW1,
  "STRATIGRAPHIC_ZONE" QVIEW2,
  "ROCK_FEATURE" QVIEW3,
  "COMPONENT_MATERIAL" QVIEW4,
  "DATA_COLLECTION" QVIEW5,
  "DATA_COLLECTION_CONTENT" QVIEW6,
  "ROCK_FEATURE" QVIEW7,
  "MATERIAL_CLASS" QVIEW8,
  "CLASSIFICATION_SYSTEM" QVIEW9,
  "DATA_COLLECTION_CONTENT" QVIEW10,
  "MATERIAL_CLASSIFICATION" QVIEW11,
  "STRATIGRAPHIC_ZONE" QVIEW15,
  "ROCK_FEATURE" QVIEW16,
  "COMPONENT_MATERIAL" QVIEW17,
  "DATA_COLLECTION" QVIEW18,
  "DATA_COLLECTION_CONTENT" QVIEW19,
  "ROCK_FEATURE" QVIEW20,
  "MATERIAL_CLASS" QVIEW21,
  "CLASSIFICATION_SYSTEM" QVIEW22,
  "DATA_COLLECTION_CONTENT" QVIEW23,
  "MATERIAL_CLASSIFICATION" QVIEW24
WHERE 
  QVIEW1."REF_EXISTENCE_KIND" = 'actual' AND
  QVIEW1."IDENTIFIER" IS NOT NULL AND
  QVIEW1."IDENTIFIER" = QVIEW2."WELLBORE" AND
  QVIEW2."STRAT_ZONE_DEPTH_UOM" = 'm' AND
  QVIEW2."STRAT_COLUMN_IDENTIFIER" IS NOT NULL AND
  QVIEW2."STRAT_INTERP_VERSION" IS NOT NULL AND
  QVIEW2."STRAT_ZONE_IDENTIFIER" IS NOT NULL AND
  QVIEW2."STRAT_UNIT_IDENTIFIER" IS NOT NULL AND
  QVIEW2."STRAT_UNIT_IDENTIFIER" = QVIEW3."DESCRIPTION" AND
  QVIEW4."ENTITY_TYPE_NAME" = 'COMPONENT_MATERIAL' AND
  QVIEW3."ROCK_FEATURE_S" = QVIEW4."INCORPORATE_S" AND
  QVIEW3."ROCK_FEATURE_S" = QVIEW6."COLLECTION_PART_S" AND
  QVIEW5."DATA_COLLECTION_S" = QVIEW6."PART_OF_S" AND
  QVIEW2."STRAT_COLUMN_IDENTIFIER" = QVIEW5."NAME" AND
  QVIEW5."REF_DATA_COLLECTION_TYPE" = 'stratigraphic hierarchy' AND
  QVIEW9."KIND" = 'chronostratigraphy' AND  
  QVIEW8."CLASSIFICATION_SYSTEM" = QVIEW9."NAME" AND
  QVIEW7."ROCK_FEATURE_S" = QVIEW10."COLLECTION_PART_S" AND
  QVIEW5."DATA_COLLECTION_S" = QVIEW10."PART_OF_S" AND
  QVIEW7."ROCK_FEATURE_S" = QVIEW11."MATERIAL_S" AND
  QVIEW8."MATERIAL_CLASS_S" = QVIEW11."MATERIAL_CLASS_S" AND
  QVIEW2."STRAT_ZONE_ENTRY_MD" IS NOT NULL AND
  QVIEW1."IDENTIFIER" = QVIEW15."WELLBORE" AND
  QVIEW15."STRAT_ZONE_DEPTH_UOM" = 'm' AND
  (((QVIEW15."STRAT_ZONE_EXIT_MD" >= QVIEW2."STRAT_ZONE_ENTRY_MD") AND (QVIEW15."STRAT_ZONE_ENTRY_MD" <= QVIEW2."STRAT_ZONE_EXIT_MD")) OR 
      ((QVIEW2."STRAT_ZONE_EXIT_MD" >= QVIEW15."STRAT_ZONE_ENTRY_MD") AND (QVIEW2."STRAT_ZONE_ENTRY_MD" <= QVIEW15."STRAT_ZONE_EXIT_MD"))) AND
  QVIEW15."STRAT_COLUMN_IDENTIFIER" IS NOT NULL AND
  QVIEW15."STRAT_INTERP_VERSION" IS NOT NULL AND
  QVIEW15."STRAT_ZONE_IDENTIFIER" IS NOT NULL AND
  QVIEW15."STRAT_UNIT_IDENTIFIER" IS NOT NULL AND
  QVIEW15."STRAT_UNIT_IDENTIFIER" = QVIEW16."DESCRIPTION" AND
  QVIEW17."ENTITY_TYPE_NAME" = 'COMPONENT_MATERIAL' AND
  QVIEW16."ROCK_FEATURE_S" = QVIEW17."INCORPORATE_S" AND
  QVIEW15."STRAT_COLUMN_IDENTIFIER" = QVIEW18."NAME" AND
  QVIEW18."REF_DATA_COLLECTION_TYPE" = 'stratigraphic hierarchy' AND
  QVIEW16."ROCK_FEATURE_S" = QVIEW19."COLLECTION_PART_S" AND
  QVIEW18."DATA_COLLECTION_S" = QVIEW19."PART_OF_S" AND
  QVIEW22."KIND" = 'lithostratigraphy' AND
  QVIEW21."CLASSIFICATION_SYSTEM" = QVIEW22."NAME" AND
  QVIEW20."ROCK_FEATURE_S" = QVIEW23."COLLECTION_PART_S" AND
  QVIEW18."DATA_COLLECTION_S" = QVIEW23."PART_OF_S" AND
  QVIEW20."ROCK_FEATURE_S" = QVIEW24."MATERIAL_S" AND
  QVIEW21."MATERIAL_CLASS_S" = QVIEW24."MATERIAL_CLASS_S"

What does "Slegge" mean anyway?

"Slegge" is Norwegian for sledgehammer.

Contributors