"Seegrid will be due for a migration to confluence on the 1st of August. Any update on or after the 1st of August will NOT be migrated"

Test Data for GeoSciML Testbed 3

Contents

Test data provided by Geoscience Australia

  • Test_Data.doc: Brief documentation of structure and example query.

Importing the Geoscience Australia test data set into Oracle Spatial 11g

  • Create an account gsmltb3. This account will require DBA privileges for the import to succeed as the dump file was created by a DBA.
  • Import the dump file:
    • imp gsmltb3/your-password-goes-here file=subgeoci_05022008.dmp log=subgeoci_05022008.log fromuser=subgeosci touser=gsmltb3
    • There may be some permissions errors at the end of the import.
  • Test the import:
    • sqlplus gsmltb3/your-password-goes-here
    • select count(*) from gsml_eastoz_geology; should yield a row count of 624.
    • select geom from gsml_eastoz_geology where objectid = 53165; should yield a lot of coordinates.
  • Add spatial metadata. This step is required for GeoServer to access the data.
    • sqlplus gsmltb3/your-password-goes-here
    • At the SQL> prompt:
      INSERT INTO user_sdo_geom_metadata VALUES (
          'gsml_eastoz_geology',
          'geom',
          MDSYS.SDO_DIM_ARRAY(
              MDSYS.SDO_DIM_ELEMENT('longitude', -180, 180, 0.000001),
              MDSYS.SDO_DIM_ELEMENT('latitude', -90, 90, 0.000001)),
          0);
      
    • Note that the SRID has been set to zero because this value matches the value in the geometry objects.

Accessing Oracle dump file with Oracle XE

  • Oracle XE is a free (gratis) cut-down version of Oracle 10g. It can be obtained, in exchange for your personal details (you need to register), from:
  • I installed Oracle XE 10.2.0 on my Windows XP desktop. I could not install it under Debian as our virtual machines have insufficient RAM.
  • The installer requires you to set a password for the system user.
  • The installer sets up the database as a service. If you do not want Oracle running all the time, you need to change this. Go to Control Panel >Administrative Tools>Services and set everything beginning with Oracle to "Manual".
  • The default port for the web administration tool is 8080. This conflicts with the usual default port for local GeoServer debugging, so I changed it:
    • sqlplus /nolog (or just use the "Run SQL Command Line" shortcut)
    • exec dbms_xdb.sethttpport(8888)
    • Edit the Database_homepage and Online_help links in C:\oracvlexe\app\oracle\product\10.2.0\server to fix the port.
  • Login as system via the web admin page: http://localhost:8888/apex
  • Create a user (e.g.) gsmltb3 with a password, and tick the DBA box (needed as the GA dump file was exported by a DBA).
  • Use the imp command line tool to import the dump file. The installer will have put this on your path.
    • Open a Windows Command Prompt.
    • imp gsmltb3/your-password-goes-here file=subgeoci_05022008.dmp log=subgeoci_05022008.log fromuser=subgeosci touser=gsmltb3
  • Login as gsmltb3 via the web admin page: http://localhost:8888/apex
  • Use the Object Browser to see the table definitions and data.
  • Tables can be dumped to text in Home>Utilities>Data Load/Unload>Unload>Unload to Text
    • Unfortunately, this tool does not support SDO_GEOMETRY types, rendering it useless for our purposes.

Test data provided by GeoScience Victoria

  • NOTE: The test PostGIS database has been updated and moved to: http://www.gsv-tb.dpi.vic.gov.au/DataDownload/gsmltestbed.zip
    • Instructions for deploying the database in PostGIS are included in gsmltestbed.zip/0.README.txt.
  • GSV-DB.zip: GSV Testbed Database (v1) DDL, SQL and ASCII data files.
  • From a PostgreSQL 8.2.4 database with PostGIS 1.2.1
  • See GSV-DB Readme file in the zip archive for instructions.

Updated 03 July 2008

  • Added gsv.geologicalenvironment table (DDL and data)
  • Added DDL for the following views (used when mapping data to GeoServer for each use case):
    • gsv.geoserver_uc2a
    • gsv.geoserver_uc2b
    • gsv.geoserver_uc2b_constituent (used by gsv.geoserver_uc2b)
    • gsv.geoserver_uc2b_geologichistory (used by gsv.geoserver_uc2b)
  • Added instance document with mapping information for each use case:
    • TB3_UC2A_Exemplar_GSVMapping.xml - maps to gsv.geoserver_uc2a
    • TB3_UC2B_Exemplar_GSVMapping.xml - maps to gsv.geoserver_uc2b

Updated 10 October 2008

  • New copy of the database finally used to serve GSV's Testbed 3 services using Deegree.
  • PostgreSQL dump file generated using pg_dump: GSV-gsmltb3.dump
  • See below for loading instructions.

Loading the GeoScience Victoria GSV-DB.zip dataset into PostGIS

  • Procedure tested with PostgreSQL 8.1.11 with PostGIS 1.1.6 on Debian Etch.
  • Already have a spatial database gsmltb3 with user gsmltb3 owning all tables. Dataset is loaded into this database.
  • Unpack the zip file. In this example, the file is unzipped in /usr/local/gsmltb3. All contents are in the GSV-DB subdirectory.
  • Change into the GSV-DB subdirectory. For example:
    • cd /usr/local/gsmltb3/GSV-DB
  • Download geoserver_outputsummary.sql, a view to summarise expected features in geoserver (written by Alistair Ritchie).
  • Rewrite the file paths in the COPY statements in Step2 and Step4. For example:
    • perl -pi -e 's|U:/WFSDevelopment/Postgres|/usr/local/gsmltb3|g' Step[24]-*.sql
  • The COPY statements in Step2 and Step4 require superuser access. For convenience, run all scripts as superuser. Ensure each step succeeds:
    su postgres
    psql -d gsmltb3 -f Step1-gsmltb3-DDL.sql
    psql -d gsmltb3 -f Step2-gsmltb3-importdata.sql
    psql -d gsmltb3 -f Step3-gsmltb3-import-mappedfeature_geolunit.sql
    psql -d gsmltb3 -f Step3.1-gsmltb3-import-mappedfeature_geolunit.sql
    psql -d gsmltb3 -f Step4-gsmltb3-import-mf_gu_interpmethod.sql
    psql -d gsmltb3 -f geoserver_outputsummary.sql
    exit
    
  • Verify successful load:
    • psql -h localhost -d gsmltb3 -U gsmltb3 -W
    • Show tables: \dt+ gsv.*
    • Show views: \dv+ gsv.*
    • Ensure this yields a MULTIPOLYGON: select AsText(shape) from gsv.mappedfeature_geolunit where id = 20202;
    • Summarise geoserver views: select * from gsv.geoserver_outputsummary;
  • To remove the dataset (for example, to replace it with a new version), drop the containing schema (and cascade):
    su postgres
    psql -d gsmltb3
    drop schema gsv cascade;
    \q
    exit
    

Loading the GeoScience Victoria GSV-gsmltb3.dump dataset into PostGIS

  • Procedure tested with PostgreSQL 8.2.4 with PostGIS 1.2.1 on Windows XP SP2 (shame on me).
  • Download the .dump file to a location accessible by the postgres user (if necessary)
  • DROP any existing instances of the gsmltb3 database on the PostgreSQL server
  • Create a new instance of gsmltb3 (note, it is important to create the database using the template0 template - no other) and restore the dump file.
    psql -c "CREATE DATABASE gsmltb3 WITH ENCODING='UTF8' OWNER=gsmltb3 TEMPLATE=template0;" -U postgres
    pg_restore -i -h localhost -p 5432 -U postgres -d gsmltb3 -v "[LOCATION OF]\gsmltb3.dump"
  • Verify a successful load as per the GSV-DB.zip load.

Test data provided by British Geological Survey

Boreholes

BGS-BH.zip: BGS test borehole data

Channel tunnel borehole data in two tables. SQL DDL and insert statements generated from Oracle Spatial database instance.

  • Table 1 : CHANNEL_TUNNEL_SEL_SOBI has borehole index data including SHAPE field with 3D point data in EPSG:7405
  • Table 2 : CHANNEL_TUNNEL_SEL_BOGE has borehole log data with 1-many relationship between SOBI and BOGE with BGS_ID as key.
  • File: BoreholeTemplate.xml is example GeoSciML instance with parts to be filled by database fields in curly brackets {}.

Map data

Files:

  • uk625k.dmp - Oracle Spatial dump of tables UK_625K_BEDROCK_WGS84, LEX_UK_625_BEDROCK, LEX_RCS_UK_625_BEDROCK, RCS_UK_625_BEDROCK
  • uk_625k_bedrock_geol_unit.sql - SQL script to create view UK_625K_BEDROCK_GEOL_UNIT
  • uk_625k.xml - Data converted to use case 2A/3A profile GeoSciML
  • uk625ktemplate.xml - One feature instance showing mapping from database fields to GeoSciML by indicating database fields in curly brackets {}.

The GeoSciML is generated with one MappedFeature for each row in the UK_625K_BEDROCK_WGS84 table and the MappedFeature/specification/!GeologicUnit properties taken from the UK_625K_BEDROCK_GEOL_UNIT view row with the same LEX and RCS values. Many MappedFeatures point to the same GeologicUnit; repeated GeologicUnits are serialised as internal document references (#uc4sf.gu.MMGSDST syntax).
Topic attachments
I Attachment Action Size Date Who Comment
BGS-BH.zipzip BGS-BH.zip manage 7.3 K 01 Jul 2008 - 23:51 MarcusSen BGS test borehole data
BGS-UK625K.zipzip BGS-UK625K.zip manage 27114.3 K 10 Jul 2008 - 00:34 MarcusSen UK 625K Bedrock mapping data
Test_Data.docdoc Test_Data.doc manage 496.0 K 05 Feb 2008 - 14:42 DalePercival Brief documentation of structure and example query.
geoserver_outputsummary.sqlsql geoserver_outputsummary.sql manage 1.0 K 08 Jul 2008 - 12:11 BenCaradocDavies View to summarise expected features in geoserver (written by Alastair Ritchie)
subgeoci_05022008.dmpdmp subgeoci_05022008.dmp manage 21038.0 K 05 Feb 2008 - 14:40 DalePercival Oracle Spatial dump file of Geoscience Australia test data.
Topic revision: r21 - 15 Oct 2010, UnknownUser
 

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