View Source

See the [Release 3 Milestone Summary|R3 Deliverables and Milestones] page.

{metadata-list}
|| 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 | B. McKenna ||
|| Release | R3 ||
|| Status | Done ||
|| Start Date | 1/6/2014 ||
|| End Date | 4/2014 ||
|| Comments | ||
{metadata-list}

h2. Milestone Scoping and Requirements

h3. Requirements and Capabilities Tracing

The requirements in this section come from the requirements spreadsheet finalized and agreed to between the CI Development Manager and CI System Engineer. That document is [M112 Requirements Spreadsheet|^M112_Requirements_2014-02-18_ver_1-02.xls]


h5. The following table contains all of the Milestone Service Requirements

|| Reqt ID || Requirement Text || Rationale and Description || Proposed Change || ||
| L4-CI-DM-RQ-124 | Search & Navigation | | | |
| L4-CI-DM-RQ-219 | Search and navigation shall implement resource discovery through complex query  | For example, resource type and a specific metadata attribute | | |
| L4-CI-DM-RQ-91 | Search and navigation shall implement geospatial query for resources | For example, by specifying a lat/lon/depth box | | |
| L4-CI-DM-RQ-194 | Search and navigation shall combine resource discovery with resource access | Subject to policy | | |
| NEW DM-1 | | | Search and Navigation Geospatial Query shall support bounding box spatial searches using the following spatial query operators from the OGC Reference Model:  a. overlaps; b. contains;  c. within; and d. disjoint. | \[https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L332-L421 \\
\] |
| NEW DM-3 | | applies to all types of searches, including geospatial and temporal. | Search and Navigation shall provide a count of the result-set from a search in near real-time. | \[https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L310-L321 \\
\] |


h5. The following table contains the Milestone UX requirements

|| Reqt ID || Requirement Text || Proposed Change ||
| L4 UX RQ 117 | A user interface for search and navigation shall be provided | |
| L4 UX RQ 119 | The search and navigation user interface shall present all supported query options | |
| NEW UX-1 | | A user interface for geospatial search shall be provided. |
| NEW | | The Geospatial Search user interface shall allow a user to search for geolocated data products by specifying the latitude, longitude, and depth at which the data were collected. |
| NEW | | The Geospatial Search user interface shall allow a user to search for deployed marine infrastructure resources by specifying the latitude, longitude, and depth at which the resource is deployed. |
| NEW | | The Geospatial Search user interface shall allow a user to specify depth by entering or selecting values, units, and whether to measure up or down from -from the ocean floor or- the ocean surface. |
| NEW | | The Geospatial Search user interface shall allow a user to specify a  latitude and longitude bounding box  by entering values of north and south latitude and east and west longitude. |
| NEW | | The Geospatial Search user interface shall allow a user to specify a latitude and longitude bounding box by drawing the bounding box on a map. |
| NEW | | The Geospatial Search user interface shall allow a user to perform bounding box spatial searches using the following spatial query  operators from the OGC Reference Model:  a. overlaps; b. contains;  c. within; and d. disjoint. |
| NEW | other search types include temporal search, search by name, and search by type | The Geospatial Search user interface shall allow a user to combine a geospatial search with one or more other search types, by "and'ing" the geospatial search criteria with the search criteria from the other search type(s). |
| NEW | | The Geospatial Search user interface shall provide contextual help. |
| NEW | | The Geospatial Search user interface shall provide help documentation. |
| NEW | | The search and navigation user interface shall allow a user to limit the number of results returned from a search query. |
| NEW | | The search and navigation user interface shall present to  a user the number of results returned from a search query. |


{hidden-data}
{html-include:url=http://architecture.oceanobservatories.org/ooinet/milestonereq/M112.html}
{hidden-data}

h3. Use Cases

h5. Find resources using OGC Reference Model Geospatial Queries

!Screen Shot 2014-01-08 at 16.05.29 .png|border=1!
* Overlaps
** A user may select "overlaps" as the query operator. The result set are all resources where the resource's geospatial region intersects with the query bounding box.
* Within
** A user may select "within" as the query operator. The result set are all resources where the resource's entire geospatial region is wholly contained within the query bounding box.
* Contains
** A user may select "contains" as the query operator. The result set are all resources where the query bounding box is wholly contained within the resource's geospatial region.
* Disjoint
** A user may select "disjoint" as the query operator. The result set are all resources that contain a geospatial region that do not intersect with the query box.

h5. Find data within bounding area that meets the additional condition of a variable (e.g., temperature) within a given range
{note}
This use case is under consideration.
{note}
* Resources can be identified using geospatial search with data conditions.
** A user can specify a range for a data variable and the results will include data products that intersect the range.

h5. Advanced: Find out how many entities meet the search criteria.

* Provide the UI with a count of the result-set from a query in near real-time.


h3. Related Search issues in Jira

* [OOIION-1136 Inconsistent Search results using Advanced Search and Simple Text Search options |https://jira.oceanobservatories.org/tasks/browse/OOIION-1136]
Any inconsistencies should be eliminated now that Postgres will handle all queries. The logic for a ‘simple search’ was different than that used in the ‘advanced search’, but both should be passed to Postgres now for consistent results (and limits, order, etc.)

* [OOIION-1137 Advanced Search results do not allow scrolling through list |https://jira.oceanobservatories.org/tasks/browse/OOIION-1137]
UI related, not service related.
* [OOIION-1407 Maximum list size is 100 for Advanced Search results|https://jira.oceanobservatories.org/tasks/browse/OOIION-1407]
* [OOIION-1409 Advanced Search results page is not constructed properly |https://jira.oceanobservatories.org/tasks/browse/OOIION-1409]
UI related, not service related.
* [OOIION-1439 Advanced Search needs more documentation, prompts, and tool tips |https://jira.oceanobservatories.org/tasks/browse/OOIION-1439]
* [OOIION-1611 Advanced Search Temporal Search is not tailored by resource, and therefore gives unexpected results to the end user |https://jira.oceanobservatories.org/tasks/browse/OOIION-1611]
This isn’t really a bug. The users all searched for data with timestamps before the ion-alpha system was preloaded. They shouldn’t have found anything with the dates in the comments. However, the question of “what field is searched using the temporal bounds inputs on UI” was still unclear. The additional Postgres field geom_temp now has time bounds which can be searched with Overlaps, Within, etc similar to above to find matching sets.



h4. Steps


h2. Milestone Testing

For skip (offset). First we [create a large number of resources|https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L236-L238] then we test using the [QueryLanguage|https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L290-L296] and the [Discovery Intermediate Format|https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L302-L308]


For the count of total results in database, [should return 1 value|https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L310-L315]


For the enhanced geospatial, we run two sets of tests:

The [first|https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L393-L410] uses a WKT Polygon object to represent a box and test the overlaps, contains and within operators.

The [second|https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/test/discovery_test.py#L412-L421] demonstrates the ability to specify a latitude/longitude point and an associated buffer (AKA radius) and search relative to the circle formed by that radius.



h4.



h2. Milestone Pull Requests
{panel}

h5. Additional search parameters (skip) and query for total results in DB
{panel}

* h3. [https://github.com/ooici/coi-services/pull/1773]
* h3. [https://github.com/ooici/coi-services/pull/1773]
* h3. [https://github.com/ooici/coi-services/pull/1776]
* h3. [https://github.com/ooici/coi-services/pull/1779]

{color:#000000}{*}Milestones - Tasks - Actions addressed{*}{color}
* M112-T131-A002: Return number of total results available in datastore from DiscoveryService
* M112-T131-A005: DiscoveryService processes an offset (skip) parameter for pagination

DiscoveryService currently supports a '*limit*' parameter, allowing the client to specify how many results to return from server. An additional '*skip*' parameter is implemented to allow the client to request the next set of n results, skipping the first m results. This addition will allow the UI to implement pagination. For example, to get results 501 to 600 from the server:

{noformat}
{'limit': 100, 'skip': 500}
{noformat}

DiscoveryService can now also be used to obtain the '*total number of results available*' in the datastore. Using the standard query object and adding

{noformat}
search_args={'count': True}
{noformat}

will return an array containing a single integer value, the total number of results available in the datastore. This call should be followed (or preceded) by the same query object without the {{*\{'count':True\}{*}}} to obtain the actual results with the limit and/or offset applied.
{panel}

h5. Implementation of WKT string for geospatial query (with optional buffer specified), this supports the point with radius search
{panel}

* h3. [https://github.com/ooici/coi-services/pull/1795]
* h3. [https://github.com/ooici/coi-services/pull/1815]
* h3. [https://github.com/ooici/pyon/pull/469]

*Milestones - Tasks - Actions addressed*

* M112-T133-A001: Implement WKT qmatcher in ds_discovery
* M112-T133-A002: Pyon DatastoreQuery language support for WKT
* M112-T133-A003: Pyon PostgresQuery language support for WKT

DiscoveryService query can now recognize a request of the form:

{noformat}
{'wkt': <WKT_STRING>, 'buffer': <decimal_degrees>}  
{noformat}

This capability supports the requirement for point based (with radius) search to be made available in the UI. The point must be encoded in WKT format, eg. POINT(10.0, 10.0) and the associated buffer parameter will be used for the radius. This geospatial object (point with buffer) is passed directly to the PostGIS layer making it the most efficient and optimal to reference a circle against the stored locations in the database

h5. Update geospatial Device records upon deployment

* h3. [https://github.com/ooici/coi-services/pull/1857]

*Milestones - Tasks - Actions addressed*

* M112-T132-A001 - Upon deployment, updates Devices with Site and Deployment attributes


h2. Milestone User Interface

[M112 - Wireframe v1 (030114)|^M112-Wireframe.jpg]

[M112 - Wireframe v2 (031714)|^New-Wireframe-circle.jpg]



h2. 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. |
| Add Spatial Operator (view) | (ion-ux) Add button group to Advanced Search "GEOSPATIAL BOUNDS" form: ('spatial_operator' options - overlap/intersects,within,contains,disjoint)&nbsp;\[UI task\] \\ |
| Add Spatial Operator (controller) | (ion-ux) Create&nbsp;spatial_operator key in service API to pass to discovery service |
| Add Spatial Operator (service) \\ | (coi-services)&nbsp;Add spatial_operator parameter to discovery service&nbsp;\[_qmatcher_geo_loc\] |
| User Defined Limits (view) | (ion-ux) Add form dropdown for number of desired results to return from search (eg. 100,200,500)&nbsp;\[UI task\] \\ |
| User Defined Limits (controller) \\ | (ion-ux) Handle 'limit' field in service API (limit currently set in code not user option) \\ |
| Return number of total results (view) | (ion-ux) Display total number of search results available in DB. eg. showing 0-10 of 100 (14,567 available)&nbsp;\[UI task\] \\ |
| Return number of total results (service) \\ | (coi-services) Return total results available in DB from discovery service (beyond specified limit) \\ |
| Search Offset (view) | (ion-ux)&nbsp;Add "next _n_ button/link below search result navigation to get next set of results past limit. eg. showing 91-100 of 100 (click to retrieve next 101-200)&nbsp;\[UI task\] |
| Search Offset (controller) | (ion-ux) Create 'offset' key&nbsp;with value in service API to pass to discovery service |
| Search Offset (service) | (coi-services) Process an offset parameter in discovery service to pass to Postgres OFFSET value (allows search to skip _n_ records) \\ |


h2. Milestone Design

h5. 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.


h5. 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 components. Describe core interfaces provided. Get review from system architect and make design artifacts available in the CI architecture documentation.

!https://docs.google.com/drawings/d/1rgejW4nYb7H98fYqM3v9ew-XD7LHh2sUO7_FIaCHVSY/pub?w=960&h=720!

h5. Define geospatial capabilities 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|http://workshops.opengeo.org/postgis-intro/indexing.html]. 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.


h6. The new resource registry postgres implementation supports and fills 4 geometry/temporal columns:

* geom: the geospatial center point
* geom_loc: the area bounding box for the resource
* vertical_range: the vertical range for the resource - postgres numrange type
* temporal_range: the temporal range for the resource \-&nbsp;postgres numrange type

So besides the point queries, we now also support intersect, overlap and containment queries against a resources bbox.

The geom colum is filled from the geospatial_point_center attribute, the geom_loc and vertical_range columns are filled based on the constraint_list and the north/south/east/west and depth min/max coordinate values.

See more details here: [https://confluence.oceanobservatories.org/display/CIDev/Postgres+Datastore]


h5. Enhance discovery to use geospatial information in data store

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

Documented example queries here. Please feel free to add to this list:
[https://confluence.oceanobservatories.org/display/CIDev/Postgres+SQL+Snippets]


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

h7. The way to query geospatial via the discovery service is this code:
[https://github.com/ooici/coi-services/blob/master/ion/services/dm/presentation/discovery_service.py#L1128-L1130]
[https://github.com/ooici/pyon/blob/master/pyon/datastore/datastore_query.py#L137-L150]
[https://github.com/ooici/pyon/blob/master/pyon/datastore/postgresql/pg_query.py#L64-L75]



h5. 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:

h7. See here for the code that fills the geom\* columns in the resource registry:
[https://github.com/ooici/pyon/blob/master/pyon/datastore/postgresql/base_store.py#L469]
[https://github.com/ooici/pyon/blob/master/pyon/datastore/postgresql/base_store.py#L369-L437]
_Please discuss any modifications here with MMEisinger._


{code}
Geometry:!Extends_Basic
Point:!Extends_Geometry
value: []
Circle: !Extends_Geometry
center: []
radius: 0.0
Polygon: !Extende_Geometry
points: []

DataProduct:
location: !Polygon
{code}

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.
- Contains&nbsp;[http://postgis.refractions.net/documentation/manual-1.4/ST_Contains.html]
- Disjoint [http://postgis.refractions.net/documentation/manual-1.4/ST_Disjoint.html]
- Intersects [http://postgis.refractions.net/documentation/manual-1.4/ST_Intersects.html]
- -Overlaps-&nbsp;_we're using Intersects which is what the user expects_
- Within [http://postgis.refractions.net/documentation/manual-1.4/ST_Within.html]

h5. 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.

h3. Design References and Context

* [cidev:Define key oceanographic geospatial search scenarios]
* [cidev:Dataset Metadata Indexing]
* [syseng:CIAD DM OV Discovery Service]
* [syseng:CIAD DM OV Index Management Service]
* [cidev:R2Cx Searches and Catalogs]
* [cidev:Evaluate GeoPortal for Externalization of Catalog and Discovery repository]
* [cidev:M166 PostgreSQL data store]
* [PostGIS|http://postgis.net/]
* [PostGIS Features|http://postgis.net/features]
* [Introduction to PostGIS|http://workshops.opengeo.org/postgis-intro/geometries.html]


h3. Design Notes

h4. R3 ElasticSearch Design etherpad

_includes postgis notes_

[http://etherpad.oceanobservatories.org/r3elasticsearch]


h4. PostGIS and Location Aware Resources

After our migration efforts for milestone [cidev: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*|http://workshops.opengeo.org/postgis-intro/spatial_relationships.html]. With standard geometrical relationships: contains, within, touches, etc.

Here is a quick SQL example of the geospatial capabilities:

{code}
CREATE TABLE gis_example(id INTEGER PRIMARY KEY, geom GEOMETRY);
INSERT INTO gis_example VALUES (0, 'POINT(0 0)');
INSERT INTO gis_example VALUES (1, 'POINT(2 2)');
INSERT INTO gis_example VALUES (2, 'POINT(1 1)');
INSERT INTO gis_example VALUES (3, 'POINT(0.99 1)');
INSERT INTO gis_example VALUES (4, 'POINT(0.99 0.99)');
SELECT id,ST_AsText(geom) AS geom_text,ST_Contains(ST_GeomFromText('POLYGON((-1 -1, -1 1, 1 1, 1 -1, -1 -1))'), geom) AS contains FROM gis_example ORDER BY contains DESC;
id | geom_text | contains
----+------------------+----------
0 | POINT(0 0) | t
4 | POINT(0.99 0.99) | t
1 | POINT(2 2) | f
2 | POINT(1 1) | f
3 | POINT(0.99 1) | f
(5 rows)


{code}

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.



h4. Application Resources that are geo-aware

|| Resource || Location type || Notes ||
| Observatory | point or polygon | |
| PlatformSite | point | |
| InstrumentSite | point | |
| DataProduct | point or polygon | polygon for glider |
| Deployment | point or polygon | |
| Site | point or polygon | |

h4. Application Resources that are geo-searchable

h5. Add geospatial and temporal attributes to the Device resource

* Deployment activation/deactivation processing can assign the location from the deployed site and the temporal extent from the deployment resource to the device.
** [https://github.com/ooici/ion-definitions/blob/master/objects/data/shared.yml#L214]
*** this is subclassed by InstrumentDevice, PlatformDevice, SensorDevice (which is not used)
** transfer the geospatial information (if any is provided) for the associated Site resource (PlatformSite, InstrumentSite) which is in the FrameOfReference parent class in the geospatial_point_center attribute (do we need to update this attributes?) into the geospatial attribute of the Device.
*** update or remove the geo / temporal attributes at the same time that the hasDevice association is made.
*** At that time you know the Site and device pairing so you dont need to 'find' anything:
**** [https://github.com/ooici/coi-services/blob/master/ion/services/sa/observatory/observatory_management_service.py#L625-L633]
( line 628 and 633 has the pairing)
*** In the remove case (633, 681) you dont need to pull anything from the site, just blank out the geo/temporal attributes in the device as it is no longer deployed.
**** [https://github.com/ooici/coi-services/blob/master/ion/services/sa/observatory/observatory_management_service.py#L676-L682]
(line 681 has the pairing)
** transfer the temporal information in the Deployment resource
*** in the FrameOfReference subclass inside the constraint_list attribute, if provided, there would be a TemporalBounds instance)



h2. Implementation Notes


h3. Discussion Notes

h4. 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
{code}
brew update
brew install postgresql
deactivate # Deactivate any current virtualenv in python
which python # Should say /usr/local/bin/python if it doesn't then you need to fix your $PATH
pip install numpy
brew install postgis
# -- At this point there was an error with lzlib being installed and the SHA didn't match --
# Copy the SHA that it says it should be
vim /usr/local/Library/Formula/lzlib.rb
# Change the sha1 call to match what it was copied
brew install lzlib
brew install postgis

{code}

To verify that it was installed correctly:
{code}
createdb gis_example
psql gis_example
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
<ctrl-d>
{code}

h4. 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.


h3. Initial Prototyping


h4. 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:
{code}
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)
{code}
Then I tried a bounding box query:
{code}
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)
{code}
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:

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



h3. Status Discussion 31 Jan with Luke, Brian, Michael, Tim and Maurice

* Issues working thru the several layers of discovery search. Brian feels he has a handle on it now.
* Need to focus on a full suite of integration tests that demonstrate various search types ( overlap, bounding box, etc) for multiple search types
** There are 3 weeks allocated to integration with the UI, this is the time that will test UI-created searches
* Must define which application level resources needed for search
** which need location attributes
** which would be found by searching for an associated resource and how would that work
*** A device does not have location attributes but it is deployed to a site which does have location. Search for all devices in a bounding box.
** Temporal and geo search scenarios?

h3. Geospatially indexed types email thread. MMeisinger 4 Feb

I suggest to run ooi_beta.yml preload for a full set of resources to investigate for search. Hint: you can run it once and then just restart the system after code changes without a new reload.

(1) Current Situation

Currently we have geospatial information for these resources:
- Observatory - only geospatial
- PlatformSite - only geospatial
- InstrumentSite - only geospatial
- DataProduct

Site is an abstract base type and will never exist as a resource in the system. Subsite is not used for OOI deployments.

Currently these resource types have temporal information:

- Deployment - only temporal
- DataProduct

(2) Desirable information

- Deployment is associated with a Site via a hasDeployment association. This means that the Deployment has access to a unique set of geospatial metadata and could be queried this way, even historically or for the future.

- Devices when deployed as primary are associated with a site via a hasDevice association. This means that the Device has access to a unique set of geospatial metadata and could be queried this way for certain epochs (note: not historically)

- Devices obviously have a physical life span (date of purchase until date of decommissioning/retiring). This information could be added as metadata with an easy change but it's not currently in the YML/preload

- DataProducts have metadata that is manually set. Obviously it may be desirable to update this metadata recurringly (e.g. in a batch process) every so often given the actual coverage. The question is semantical: The DataProduct metadata has the nominal temporal range inside, not the actual. The actual data may have gaps so you are always dealing with bounding boxes, not with exact matches.

- It may be desirable to issue historic queries: Find all devices that were deployed at area1 in historic temporal range. We have this information through Deployment resources but the query may be a bit more complicated

- Dataset resources may be interesting too, but maybe less so to the user

- There is a dependency between the derived DataProducts for a device (e.g. L1) and the parsed DataProduct. These would need to be kept in sync

- There will be additional complication with site DataProducts that have different device associations that are vaild for certain time periods only.

(3) Next steps

I believe we should discuss these directions:

a: Can we enhance the resource metadata for more resource types?

b: Can we define processes that update resource metadata recurringly to make it more reliable? Or on certain service calls, e.g. activate_deployment

c: Can we extend the discovery service / datastore query classes with abstracted SQL queries that use associations to find more resources - careful here.

d: What are the user expectations for searching and finding, for navigating resource trees and for maintaining resource metadata and do we meet these right now.



h3. Design and planning discussion. MMeisinger, TAmpe, MManning 11 Feb

Given the remaining time on this milestone we will need to provide a trimmed set of geosearch capabilities that still allows the user to locate critical resources.

* Add geospatial and temporal attributes to the Device resource
** Deployment activation/deactivation processing can assign the location from the deployed site and the temporal extent from the deployment resource to the device.
* Data Product resource geospatial and temporal information will be updated via batch processing.

Note that currently in preload OOI sites have geospatial information but do not have temporal rage. Non-OOI sites currently have both geospatial and temporal information. Ideally this could be align with a script is time allows.

MMeisinger has defined an approach to support circle and polygon shaped bounding boxes in Discovery and will implement this near the end of this milestone delivery date.