How to map data to clowder geostreams API / PostGIS Schema

[dlebauer]

Description

We need to map relevant information to the Clowder PostGIS database.
#101 is the first step - mapping field of view to the datapoint table in the Clowder PostGIS database.

Here we need to determine additional information that we want to store in the Geostreams API.

Context

The role of Clowder is to store data related to the field scanner operations and sensor box, including bounding box of each image / dataset (#101) as well as location of the sensor, data types and processing level, scanner missions.

By contrast, BETYdb will be the primary place to store plots and other regions of interest (e.g. fields, rows, plants) that are associated with agronomic experimental design / meta-data (what was planted where, field boundaries, treatments, etc).

It is okay if there we need to mirror data across the databases (store identical information in different schemas) e.g. to improve performance / usability, as long as it is clear where the canonical information is stored.

Further Suggestions / Request for Feedback

• @caicai89- please post the Clowder PostGIS schema
  • we need the tables, field names and types, and table relationships (are these many-many?)
• what other information from the field scanner do we want to store (e.g. in sensor, stream) tables?
• See also KSU database schema
  terraref_KSU_uas_htp_data_management_ 20160330.pdf

[caicai89-]

The above is the schema I draw. I think current relationship is one-many, but it can support many-many and many-one. For the exactly type, maybe @robkooper can add them on.

[robkooper]

It is a one to many. How is the progress, did you manage to get datapoints into the server?

[caicai89-]

@robkooper I have problems when creating the sensor. I tried two ways.

1. use the "Sensor->create" page, but it has no response after clicking the "Create" button
2. use the api directly and the following is the body. However, it returns a 500 error.
   {
   "name": "test",
   "type": "Feature",
   "geometry": {
   "coordinates": [-111.974805692, 33.0763081106, 0]
   },
   "properties": {
   "region": "SU",
   "type": { "id": "seabird", "title": "SeaBird"},
   "name": "SU12",
   "popupContent" : "SeaBird site SU12"
   }
   }

Any suggestions?

[caicai89-]

Another question is how to insert a polygon into the geometry?
"geometry": {
"coordinates": [[x1,y1,z1],[x2,y2,z2]]
}
Is that correct?

[dlebauer]

@caicai89- I am not familiar with the geostreams API but geojson (and PostGIS) require the first point to be repeated at the end, e.g. "coordinates": [[x1,y1,z1],[x2,y2,z2],[x3,y3,z3],[x1,y1,z1]]

I think we also need to define and specify the site-specific coordinate reference system (terraref/reference-data#32)

Here is the geojson specifications: http://geojson.org/geojson-spec.html
And a nice overview: http://www.macwright.org/2015/03/23/geojson-second-bite.html

[caicai89-]

@dlebauer You are right, for polygon in GeoJSON, the first and the end points should be the same. However, for the PostGIS, it seems do not have the requirement.
https://www.postgresql.org/docs/9.5/static/datatype-geometric.html
In addition, if we use the type box for field of view, we might only need to provide 2 points.

[max-zilla]

From emails between @caicai89- @robkooper and myself:

Yaping getting errors about datapoints.created column missing in PostGIS database.

Appears the SQL that was executed does not create this column.

Brock suggests running with update flag:
./sbt -Dpostgres.db=gltg -DPOSTGRESUPDATE=1 -DMONGOUPDATE=1 run

Not sure where this is handled in a service context like we have on Clowder dev. Yaping was able to create stream and sensor with no issues.

[max-zilla]

Based on recent discussions:

• SENSOR will be the plot/source of the data, e.g. UA_MAC, DANFORTH.
• STREAM will be the sensor, e.g. co2Sensor, SWIR.
• DATAPOINT will be the geometry associated with a dataset (a single capture from a single sensor)