GeoSciML Reference Dataset

Introduction

This is a reference dataset for GeoSciML 3.0. It consists of Oracle and Postgres/PostGIS SQL files for a schema that contains data for sample instances of various features and associated example web service configurations.

Current Release

Component Location
Version 1.0 https://www.seegrid.csiro.au/subversion/GeoSciML/cgi-reference-dataset/tags/1.0/
Postgres/PostGIS schema https://www.seegrid.csiro.au/subversion/GeoSciML/cgi-reference-dataset/tags/1.0/data/database/cgi-reference-postgres.sql
Oracle schema https://www.seegrid.csiro.au/subversion/GeoSciML/cgi-reference-dataset/tags/1.0/data/database/cgi-reference-oracle.sql
Database model https://www.seegrid.csiro.au/subversion/GeoSciML/cgi-reference-dataset/tags/1.0/doc/cgi-reference.eap
  https://www.seegrid.csiro.au/subversion/GeoSciML/cgi-reference-dataset/tags/1.0/doc/cgi-reference-uml.xml
Geoserver WFS configuration https://www.seegrid.csiro.au/subversion/GeoSciML/cgi-reference-dataset/tags/1.0/services/geoserver/

Acknowledgements

This product is derived from data compiled by the Earth Resources Development Division, Department of Primary Industries, Victoria, Australia (http://www.dpi.vic.gov.au/earth-resources).

Disclaimer

This dataset should not be considered a scientific or geological dataset. While it is intended to be geologically plausible, it has been, and be may further, augmented with content created for illustrative purposes alone. It should therefore not be used for any geological or decsion making purpose. No responsibility is taken for inappropriate use of these data.

Assumptions

This document does not provide instructions for the deployment of the databases in question, or the related web service instances. It assumes that the user has sufficient proficiency in the database applications, and web service software to undertake a successful deployment. The Useful Links table contains links to instructions and documentation about the software and applications in question.

Useful links

Topic Location
GeoSciML 3.0 Cookbook http://www.geosciml.org/some/where
Postgres http://www.postgresql.org/
PostGIS http://postgis.refractions.net/
Oracle http://www.oracle.com/index.html
Deegree 3 http://deegree.org/
Geoserver http://geoserver.org/display/GEOS/Welcome
- app-schema http://docs.geoserver.org/stable/en/user/data/app-schema/index.html

Database

Database Schema Mapping

The mapping of database schema to the GeoSciML 3.0 schema is explicitly declared in the database SQL files and the Enterprise Architect model. It is also implicit in the table and field naming conventions.Appendix One summarizes the coverage of the GeoSciML model and includes feature type to table mappings.

SQL/DDL files

The provided SQL files contain DDL statements to CREATE tables, constraints and indexes and INSERT statements to populate the tables. The scripts are deliberately simple, focusing on the creation of tables and data (using binary import/export files can cause issues on some Oracle installations). While the scripts may be run 'as is,' it is the responsibility of the user to incorporate them into more robust scripts (for example transactions) according to local policies and procedures.

The files are:
  • Postgres/PostGIS data: cgi-reference-postgres.sql
  • Oracle data: cgi-reference-oracle.sql

Running the SQL

  1. Ensure that a schema called 'cgireference' exists in the database. If you replace 'cgireference' with another schema, ensure that:
    1. the PostGIS spatial metadata table insert statement is updated to reflect the new schema (line 2483 of cgi-reference-postgis.sql); and
    2. the 'cgi.schema' variable in app-schema.properties is updated for the relevent Geoserver web feature service instance.
  2. Ensure that the user running the script has sufficient privileges to create and alter tables, create indexes and insert data.

Editing the Oracle SQL file

  • Note that SQL*Plus cannot handle files containing lines longer the 2499 characters.
  • cgi-reference-oracle.sql has been edited to ensure no line is longer than this length - carriage returns have been inserted into long lines in the GSML_MAPPEDFEATURE insert statements.
  • If this file is edited to meet local requirements ensure that the file is not re-formatted in a way that creates one line per insert statement - otherwise a number of GSML_MAPPEDFEATURE inserts will fail.

Model

The database model is provided as an Enterprise Architect (EA) project (cgi-reference.eap) and EA XMI export (cgi-reference-uml.xml). The model provided is reverse engineered from the Postgres/PostGIS database, however the Oracle version differs only in the names (UPPERCASE) and data types. Notes provided detail the differences.

CGI reference dataset database schema

Design conventions

The database is designed to represent the GeoSciML 3.0 data model as a relational database. While it aims for a one-to-one mapping, it will simplify the model if it helps to simplify the database and reduce the number of tables and joins required.
  • Where a property is single valued, a value inside a complex type may be collapsed into a single field on the table mapping to the type that 'owns' the property.
  • Where a property is multi-valued values will be held in a related table.
    • If the value is a simple type, e.g. an XML literal, the name of the table will be the name of the target property.
    • If the value is a complex type, the name of the table will be the name of the target type.
    • If the name of the target type is not unique (the type is shared by other types) then the name will be an amalgamation.

Indexing

Primary keys have been created, where appropriate, to assist with deployment. No indexes have been created on foreign keys, or commonly queried fields, as the database not large enough to warrant indexes. Spatial indexes have been created.

Naming conventions

Table names
  • Tables are named using the following convention: [namespace]_[feature or type name].
    • For example gsmlgu_geologicunit for gsmlgu:GeologicUnit.
  • Some names have been truncated due to database identifier length constraints.
    • For example gsmlgs_sheardispstructure for gsmlgs:ShearDisplacementStructure.
  • Where a table has been created to meet the needs of a particular WxS application, it will be given a suffix unique to the application.
    • _gsvr for Geoserver specific tables
    • _dgr for Deegree specific table
    • For example gsmlgu_geologicunit_gsvr was created to allow Geoserver to serve multiple gml:name values for a gsmlgu:GeologicUnit (see below).
Field names
  • Where the namespace of the target property is in the same namespace of the target feature type/type: [property name].
    • For example, gsml_mappedfeature.shape for gsml:MappedFeature/gsml:shape.
  • Where the namespace of the target property is in a different namespace to the target feature type/type: [namespace]_[property name].
    • For example, gsmlgu_geologicunit.gml_identifer for gsmlgu:GeologicUnit/gml:identifier.
  • Where the several fields contain data for same property: [namespace*]_[property name]_[disambiguator]. * if required, see above.
    • For example, in gsmlgu_geologicunit.geologicunittype_href for gsmlgu:GeologicUnit/gsmlgu:geologicUnitType/@xlink:href and
    • ~.geologicunittype_title for ~/gsmlgu:geologicUnitType/@xlink:title.

Web Services

Geoserver

Geoserver configurations are provided as an example implementation, demonstrating various ways of populating values.
  • The folders in the data folder should replace or be merged with the folders in the data folder in a default Geoserver deployment.
  • The configuration files make reference to constants defined in the provided app-schema.properties file and it must be copied to the %GEOSERVER_HOME%/WEB-INF/classes and updated with the appropriate local settings.
  • Geoserver cannot serve multivalued simple types (eg gml:name) from joined tables. In this case denormalised sources are required (see here). The table gsmlgu_geologicunit_gsvr was created to meet this implementation requirement for gsmlgu:GeologicUnit.
  • Note for Oracle XE deployments: that the configuration cannot be loaded in its entirety as Geoserver requires more database connections than XE will allow.
  • At the time of testing (January 2012) the stable versions of Geoserver and app-schema did not work - successful deployments have been made from Geoserver nightly builds.
    • There are known issues with Geoserver escaping repeated elements - see the gsml-mappedfeature.xml instance document for an example.

Deegree

Provided when available

Appendix One: Model Mapping

Feature Type/Type Property Source Has NILs? Comment
gsml:MappedFeature        
  @gml:id gsml_mappedfeature.gml_id N  
  gml:identifier gsml_mappedfeature.gml_identifier N  
  gsml:observationMethod/swe:Category/swe:label gsml_observationmethod.swe_category_label N  
  gsml:observationMethod/swe:Category/swe:identifier gsml_observationmethod.swe_category_identifier N  
  gsml:positionalAccuracy/swe:Quantity/swe:value gsml_mappedfeature.positionalaccuracy N  
  gsml:positionalAccuracy/swe:Quantity/swe:uom/@code gsml_mappedfeature.positionalaccuracy_code N  
  gsml:positionalAccuracy/swe:Quantity/swe:uom/@xlink:href gsml_mappedfeature.positionalaccuracy_href N  
  gsml:positionalAccuracy/swe:Quantity/swe:uom/@xlink:title gsml_mappedfeature.positionalaccuracy_title N  
  gsml:resolutionScale/gmd:MD_RepresentativeFraction/gmd:denominator/gco:Integer gsml_mappedfeature.resolutionscale_denominator N  
  gsml:samplingFrame/@xlink:href gsml_mappedfeature.samplingframe_href N  
  gsml:samplingFrame/@xlink:title gsml_mappedfeature.samplingframe_title N  
  gsml:specification gsml_mappedfeature.specification_id N gsml_mappedfeature.specification_id determines target gsml:GeologicFeature
  gsml:shape gsml_mappedfeature.shape N  
gsmlgs:Contact        
  @gml:id gsmlgs_contact.gml_id N  
  gml:identifier gsmlgs_contact.gml_identifier N  
  gsml:purpose gsmlgs_contact.gsml_purpose N  
  gsmlgs:contactType/@xlink:title gsmlgs_contact.contacttype_title N  
  gsmlgs:contactType/@xlink:href gsmlgs_contact.contacttype_href N
gsmlgs:ShearDisplacementStructure        
  @gml:id gsmlgs_sheardispstructure.gml_id N  
  gml:identifier gsmlgs_sheardispstructure.gml_identifier N  
  gml:description gsmlgs_sheardispstructure.gml_description N  
  gml:name gml_name.gml_name N  
  gsml:purpose gsmlgs_sheardispstructure.gsml_purpose N  
gsmlgu:GeologicUnit        
  @gml:id gsmlgu_geologicunit.gml_id N  
  gml:identifier gsmlgu_geologicunit.gml_identifier N  
  gml:description gsmlgu_geologicunit.gml_description N  
  gml:name gml_name.gml_name N  
  gsml:purpose gsmlgu_geologicunit.gsml_purpose N  
  gsml:relatedFeature/gsmlga:GeologicHistory   N See gsmlga:GeologicHistory mapping
  gsmlgu:geologicUnitType/@xlink:title gsmlgs_contact.geologicunittype_title N  
  gsmlgu:geologicUnitType/@xlink:href gsmlgs_contact.geologicunittype_href N  
  gsmlgu:rank/@xlink:title gsmlgs_contact.rank_title N  
  gsmlgu:rank/@xlink:href gsmlgs_contact.rank_href N  
  gsmlgu:composition/gsmlgu:CompositionPart   N See gsmlgu:CompositionPart mapping
gsmlga:GeologicHistory        
  @gml:id gsmlga_geologichistory.gml_id N  
  gsml:relationship/@xlink:title gsmlga_geologichistory.gsml_relationship_title N  
  gsml:relationship/@xlink:href gsmlga_geologichistory.gsml_relationship_href N  
  gsml:sourceRole/@xlink:title gsmlga_geologichistory.gsml_sourcerole_title N  
  gsml:sourceRole/@xlink:href gsmlga_geologichistory.gsml_sourcerole_href N  
  gsml:targetRole/@xlink:title gsmlga_geologichistory.gsml_targetrole_title N  
  gsml:targetRole/@xlink:href gsmlga_geologichistory.gsml_targetrole_href N  
  gsml:relatedFeature/gsmlga:GeologicEvent   N See gsmlga:GeologicEvent mapping
gsmlga:GeologicEvent        
  @gml:id gsmlga_geologicevent.gml_id N  
  gsml:purpose gsmlga_geologicevent.gsml_purpose N  
  gsmlga:eventProcess/@xlink:title gsmlga_eventprocess.eventprocess_title N  
  gsmlga:eventProcess/@xlink:href gsmlga_eventprocess.eventprocess_href N  
  gsmlga:youngerNamedAge/@xlink:title gsmlga_geologichistory.youngernamedage_title N  
  gsmlga:youngerNamedAge/@xlink:href gsmlga_geologichistory.youngernamedage_href N  
  gsmlga:olderNamedAge/@xlink:title gsmlga_geologichistory.oldernamedage_title N  
  gsmlga:olderNamedAge/@xlink:href gsmlga_geologichistory.oldernamedage_href N  
  gsmlga:eventEnvironment/swe:Category/swe:label gsmlga_eventenvironment.swe_category_label N  
  gsmlga:eventEnvironment/swe:Category/swe:identifier gsmlga_eventenvironment.swe_category_identifier N  
gsmlgu:CompositionPart        
  gsmlgu:role/@xlink:title gsmlgu_compositionpart.role_title N  
  gsmlgu:role/@xlink:href gsmlgu_compositionpart.role_title N  
  gsmlgu:material/gsmler:RockMaterial   N See gsmler:RockMaterial mapping
  gsmlgu:proportion/swe:QuantityRange/swe:value gsmlgu_compositionpart.proportion_lower_value & .proportion_upper_value N  
  gsmlgu:proportion/swe:QuantityRange/swe:uom/@code gsmlgu_compositionpart.proportion_uom_code N  
  gsmlgu:proportion/swe:QuantityRange/swe:uom/@xlink:title gsmlgu_compositionpart.proportion_uom_title N  
  gsmlgu:proportion/swe:QuantityRange/swe:uom/@xlink:href gsmlgu_compositionpart.proportion_uom_href N  
gsmler:RockMaterial        
  gsmler:color/@xlink:title gsmler_color.color_title Y  
  gsmler:color/@xlink:href gsmler_color.color_href Y  
  gsmler:purpose gsmler_rockmaterial.purpose N  
  gsmler:consolidationDegree/@xlink:title gsmler_rockmaterial.consolidationdegree_title N  
  gsmler:consolidationDegree/@xlink:href gsmler_rockmaterial.consolidationdegree_href N  
  gsmler:lithology/@xlink:title gsmler_lithology.lithology_title N  
  gsmler:lithology/@xlink:href gsmler_lithology.lithology_href N  

Appendix Two: Subversion Repository

The repository is subdivided into trunk and tags folders. All development is undertaken in the trunk folder while versioned releases are made into a tag folder named with the version number. The structure within trunk and tag folders is (currently) the same.

Folder Structure

Each folder contains a READ-ME.txt file with notes on its contents
Folder Description
data Reference database data
- database Database SQL files (table DDL and data INSERT statements)
- instances GeoSciML 3.0 instance documents generated from the reference data
- shapefiles ESRI shapefiles as an alternative data source for gsml:MappedFeature
doc Database models and documentation
services Example configurations for web service applications
- geoserver Geoserver configuration files
-- oracle Oracle configurations
--- data Geoserver data directory
-- postgres Postgres/PostGIS configurations
--- data Geoserver data directory
- deegree Deegree configurations - to be created ...
Topic revision: r10 - 26 Jul 2012, AlistairRitchie
 

Current license: All material on this collaboration platform is licensed under a Creative Commons Attribution 3.0 Australia Licence (CC BY 3.0).