Skip to end of metadata
Go to start of metadata
You are viewing an old version of this page. View the current version. Compare with Current  |   View Page History

See the Release 3 Milestone Summary page.

WBS 1.2.3.21.40.01
ID M112
Description Capability extensions to index and query datasets by geospatial metadata beyond geospatial points supported in R2
Deliverable ID D053
Deliverable Data Access Service
Development Owner M. Manning
Development Team Data Processing
Developers  
Release R3
Status Pending
Start Date TBD
End Date TBD
Comments  

Milestone Scoping and Requirements

Requirements and Capabilities Tracing

html-include: java.net.UnknownHostException: architecture.oceanobservatories.org

Use Cases

Define key oceanographic geospatial search scenarios

Find data within 2-d bounding box or named area
  • Refinement: Overlapping With, Wholly Contained In, Mostly Contained In
  • Refinement: Find raw data of an instrument that does not incorporate location in its data records (mooring case)
  • Refinement: Find raw data of an instrument that does not incorporate location in its data records (glider case)
  • Refinement: Take location error bars into account
Find data within 3-d bounding cube or named volume
  • Refinement: Mostly Contained In
  • Refinement: relative to specified datum (e.g., WGS-84) — reference ellipsoid or sea level
  • Refinement: relative to benthic surface (e.g., within this lat/lon, between 30m and 60m above sea floor)
Find data within bounding area that meets the additional condition of time within a given range
Find data within bounding area that meets the additional condition of a variable (e.g., temperature) within a given range
  • Refinement: Multiple additional conditions
Advanced: Find out how many entities meet the search criteria.
  • Refinement: Provide an indication of how many entities meet the search criteria in real-time (for immediate update in the user interface).

Assumptions and Prerequisites

Dependencies and Gaps
  • M166 PostgreSQL data store
  • M114 Coverage Model Integration
  • M116 Coverage Model Performance and Extensibility

Steps

Milestone Tasks

Task Description
Identify Geospatial search requirements Analyze existing requirements and propose revisions for milestone work. Communicate with Marine IOs and other stakeholders to determine specific detailed features and analyze available documentation as needed. Work with the System Engineer on a requirements revision proposal.
Design Geospatial search behavior model Develop detailed designs of this milestone's capabilities as needed for subsequent implementation and integration with the production system and other components. Identify core interfaces and dependencies to the system and to other compoents. Describe core interfaces provided. Get review from system architect and make design artifacts available in the CI architecture documentation.
Define enhanced geospatial indexes in database Implement database indexes as designed and scoped using the integrated database technology. Develop tests to demonstrate the correct operation of the indexes.
Enhance discovery to use enhanced indexes Implement the capability as designed and scoped. Develop unit and integration tests to demonstrate the correct operation of the code.
Enhance resource attributes for geospatial resources Implement the capability as designed and scoped. Develop unit and integration tests to demonstrate the correct operation of the code.
Enhance business logic for geospatial resources Implement the capability as designed and scoped. Develop unit and integration tests to demonstrate the correct operation of the code.
Integrate and test with production environment Take all developed software capabilities of this milestone and integrate them with the remainder of the system. Demonstrate the correct function of the additions through successful automatic tests running against a fully launched system and by interactive demonstration on the test/alpha system.

Milestone Design

Identify Geospatial search requirements

Analyze existing requirements and propose revisions for milestone work. Communicate with Marine IOs and other stakeholders to determine specific detailed features and analyze available documentation as needed. Work with the System Engineer on a requirements revision proposal.

Design Geospatial search behavior model

Develop detailed designs of this milestone's capabilities as needed for subsequent implementation and integration with the production system and other components. Identify core interfaces and dependencies to the system and to other compoents. Describe core interfaces provided. Get review from system architect and make design artifacts available in the CI architecture documentation.

Define enhanced geospatial indexes in database

Implement database indexes as designed and scoped using the integrated database technology. Develop tests to demonstrate the correct operation of the indexes.

Consider OpenGEO Indexing Tutorial. For tables in PostGIS that will have geospatial support, consideration for how to index and creating indexes will need to be designed and implemented. This logic is probably best suited for wherever the CREATE TABLE logic is implemented. A simple scan of the resource fields to identify any fields that are geometries should suffice, and then add an index to the database.

Enhance discovery to use enhanced indexes

Implement the capability as designed and scoped. Develop unit and integration tests to demonstrate the correct operation of the code.

Indexes are inherently used when available in PostgreSQL, without an index a brute-force or exhaustive search is used.

Enhance business logic for geospatial resources

Implement the capability as designed and scoped. Develop unit and integration tests to demonstrate the correct operation of the code.

We will define a resource type hierarchy that supports geometries that are intended to be geospatially indexed.

  • Geometry
  • Point
  • Circle
  • Square
  • Polygon

A Resource that intends to have a field or subset of fields that are geospatially indexed will include a field that is of a geometric type:

The resource registry will need to be modified so when the tables are created and a field of type Geometry is created an appropriate PostGIS data type is selected and a proper index is created to geospatially index the resource.

We will refactor the existing discovery code to use PostGIS capabilities for search and navigation as well as geospatial search. We will expose GIS searching capabilities through discovery service.

  • 3D Closest Point
  • 3D Distance
  • 3D Within
  • 3D Fully Within
  • 3D Intersects
  • 3D Longest Line
  • 3D Max Distance
  • 3D Shortest Line
  • Area
  • Azimuth
  • Centroid
  • Closest Point
  • Contains
  • Contains Properly
  • Covers
  • Covered By
  • Crosses
  • Line Crossing Direction
  • Disjoint
  • Distance
  • Hausdorff Distance
  • Max Distance
  • Distance Sphere
  • Distance Spheroid
  • D-Fully Within
  • D-Within
  • Equals
  • Has Arc
  • Intersects
  • Length
  • Length 2D
  • 3D Length
  • Length Spheroid
  • Length 2D Spheroid
  • Length 3D Spheroid
  • Longest Line
  • Ordering Equals
  • Overlaps
  • Perimeter
  • Perimeter 2D
  • Perimeter 3D
  • Point on Surface
  • Project
  • Relate
  • Relate Match
  • Shortest Line
  • Touches
  • Within
Integrate and test with production environment

Take all developed software capabilities of this milestone and integrate them with the remainder of the system. Demonstrate the correct function of the additions through successful automatic tests running against a fully launched system and by interactive demonstration on the test/alpha system.

Design References and Context

Design Notes

R3 ElasticSearch Design etherpad

includes postgis notes

http://etherpad.oceanobservatories.org/r3elasticsearch

PostGIS and Location Aware Resources

After our migration efforts for milestone M166 PostgreSQL data store, we should be able to leverage the featureset of PostGIS to provide OOIN and clients with geospatial awareness for all system resoures that have a geospatial identity. Once PostGIS is installed and the PostgreSQL database has the GIS extension installed then extended resources to include GIS aware objects is simple.

GIS Objects
The GIS objects supported by PostGIS are a superset of the "Simple Features" defined by the OpenGIS Consortium (OGC). As of version 0.9, PostGIS supports all the objects and functions specified in the OGC "Simple Features for SQL" specification.

PostGIS extends the standard with support for 3DZ,3DM and 4D coordinates.

The OpenGIS specification defines two standard ways of expressing spatial objects: the Well-Known Text (WKT) form and the Well-Known Binary (WKB) form. Both WKT and WKB include information about the type of the object and the coordinates which form the object.

Examples of the text representations (WKT) of the spatial objects of the features are as follows:

  • POINT(0 0)
  • LINESTRING(0 0,1 1,1 2)
  • POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
  • MULTIPOINT(0 0,1 2)
  • MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))
  • MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))
  • GEOMETRYCOLLECTION(POINT(2 3),LINESTRING(2 3,3 4))

The database provides the capability to query against spatial relationships. With standard geometrical relationships: contains, within, touches, etc.

Here is a quick SQL example of the geospatial capabilities:

PostGIS also supports parsers for standard industry shapefiles including KMZ, ESRI Shape files etc. This may play a role if we provide users with the capability of inputing system resources and defining shape boundaries for the resources.

Implementation Notes

Discussion Notes

Discussion Tuesday October 29th,

These are the steps I took in order to install postgis on a near-fresh machine:

The prerequisites is that python2.7 is installed via brew

To verify that it was installed correctly:

Discussion Tuesday 31 October

Prototype using a single column in the resource table to contain the geodata (each row represents a single resource)

  • define the types of geometries required to represent various resource types: point, rectangle
  • if a resource noes not have a geo-location then simple leave as null
  • queries should be a standard PostGIS select and efficient:
    • find all instrument devices of model CTDSMP37 in this rectangle
  • OGC externalization plans are next phase
    • see how much of the standard we can support with the above simple model.

Initial Prototyping

1 Nov

MMeisinger
All, you can now try out the Postgres resource registry branch. It is ready to use for initial investigations and for call tracing. It works with the full demo of R2 alpha preload, UI and streaming except for discovery service/ES integration. No changes to coi-services required, other than change pyon and ion-definitions submodules, install postgresql and driver and add a bit of pyon.local.yml:
https://confluence.oceanobservatories.org/display/CIDev/Postgres+Datastore (see at bottom)

It's very easy to use and you can switch back and forth coi-services master and coi-services postgres_merge branch without issues. You don't even have to change pyon.local.yml

I've added this and other information to the "central" Postgres page on Confluence:
https://confluence.oceanobservatories.org/display/CIDev/Postgres+Datastore

I just enhanced the Postgres datastore to set a geometry column (currently based on the geospatial_point_center value). This works nicely for the BETA preload:

ion_sterling_ion=# select id,name,ST_AsEWKT(geom) from ion_sterling_resources where geom is not null;
id | name | st_asewkt
----------------------------------------------------------------------------------------------------------------
632ced50e66443cabe6c2db5f593f782 | Beta Demo Site Alpha | SRID=4326;POINT(-117.23214 32.88237)
f2370c2be1d743379b4e98e1c97d3ef0 | Beta Demo Site Beta | SRID=4326;POINT(-125.399828 44.600045)
990648bf8416463392f83f925cff6301 | Beta Demo Site Gamma | SRID=4326;POINT(-122.249952 47.650011)
bdbcf25cd38a4ce59bfd123e32f656e9 | Beta Demonstration Station One | SRID=4326;POINT(-117.23214 32.88237)
6f164466ecf34e4ebbfc577be6d9dcaf | Profiler 200m Platform 104 Site | SRID=4326;POINT(-117.23214 32.88237)
c1d8a819caf44f46a5215b2640ba5b95 | Glider 001 - Mobile Assets Station | SRID=4326;POINT(-125.399828 44.600045)
680ef26c2f41405aa538d8cded2f97d9 | Instrument site 2 Demonstration | SRID=4326;POINT(-117.23214 32.88237)
f3db2a6f82be4ec1974930426651772d | Instrument site 7 Demo | SRID=4326;POINT(-117.23214 32.88237)
013768adcd394f32932e97b6778b3eaa | SBE37SMP CONDWAT L1 | SRID=4326;POINT(-122.249952 47.650011)
9c76441033ed42cc9c1f9054c5ad9454 | Platform Engineering Data | SRID=4326;POINT(-117.23214 32.88237)
e61c5cf24b324a3996c59c65a1550893 | Platform Engineering Data 200m Platform | SRID=4326;POINT(-117.23214 32.88237)
4801748bf896448c98153a093f81cca4 | SBE37SMP Raw | SRID=4326;POINT(-122.249952 47.650011)
44d8c1176fda4bfc9d418f8e8e2c4fbe | SBE37SMP Parsed | SRID=4326;POINT(-122.249952 47.650011)
4ab484fa8e044139a9f893cfee939a34 | SBE37SMP TEMPWAT L1 | SRID=4326;POINT(-122.249952 47.650011)
830cac9cd3c64ff4b87c79b5fb865f97 | SBE37SMP PRESWAT L1 | SRID=4326;POINT(-122.249952 47.650011)
443042055ec244a68d5108751d7fefe9 | SBE37SMP PRACSAL L2 | SRID=4326;POINT(-122.249952 47.650011)
a9798b8e6c63400084e1d710326321c4 | SBE37SMP DENSITY L2 | SRID=4326;POINT(-122.249952 47.650011)
a30829da02354b7c9f9c7e06398a08f9 | SBE Simulator Parsed Data Product | SRID=4326;POINT(-125.399828 44.600045)
(18 rows)

Then I tried a bounding box query:

ion_sterling_ion=# select id,name,ST_AsEWKT(geom) from ion_sterling_resources where geom && ST_MakeEnvelope(-120, 0, 0, 90, 4326);
id | name | st_asewkt
--------------------------------------------------------------------------------------------------------------
632ced50e66443cabe6c2db5f593f782 | Beta Demo Site Alpha | SRID=4326;POINT(-117.23214 32.88237)
bdbcf25cd38a4ce59bfd123e32f656e9 | Beta Demonstration Station One | SRID=4326;POINT(-117.23214 32.88237)
6f164466ecf34e4ebbfc577be6d9dcaf | Profiler 200m Platform 104 Site | SRID=4326;POINT(-117.23214 32.88237)
680ef26c2f41405aa538d8cded2f97d9 | Instrument site 2 Demonstration | SRID=4326;POINT(-117.23214 32.88237)
f3db2a6f82be4ec1974930426651772d | Instrument site 7 Demo | SRID=4326;POINT(-117.23214 32.88237)
9c76441033ed42cc9c1f9054c5ad9454 | Platform Engineering Data | SRID=4326;POINT(-117.23214 32.88237)
e61c5cf24b324a3996c59c65a1550893 | Platform Engineering Data 200m Platform | SRID=4326;POINT(-117.23214 32.88237)
(7 rows)

It seems to work. An arbitrary number of extensions are thinkable

LCampbell
If you want to add PostgreSQL to your supervisor config scripts so that it's managed as a daemon by supervisor:

[program:psql]
command=/usr/local/bin/postgres -D /usr/local/var/postgres
autostart=false
autorestart=false
stopsignal=INT

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.