Table of Contents
...
The purpose of this document is to describe the data integration architecture for the New Mexico Water Data Initiative (NNWDINMWDI). It describes in a “bottom-up” approach the NMWDI. It proceeds as follows
...
The goal of the New Mexico Water Data Initiative is to make available to the public data collected by multiple agencies about water resources in New Mexico in a common format. The aspirational user story is as follows:
“As a nonexpert non-expert community member interested in New Mexico’s water resources, I want to discover and access water information relevant to the location or geographical area I care about from all of the organizations that hold data about it, so that I don’t have to have special knowledge to access some information and so I don’t miss some potentially relevant information. I want the data I discover to be delivered to me in a common format regardless of organization, so that I can visualize and analyze data from all of the organizations without substantial preprocessing on my part.”
An aspirational demonstration application can be found here, where monitoring wells wells with water data can be visualized, sourced from multiple agencies, with parameters and measurements searched for in a common interface and returned in a csv file with common column names. This application is based on a workflow where multiple agencies' data have been transformed and provided with independent instances of a standard API. The single application can then allow users to interact seamlessly with data from multiple agencies being provided by multiple APIs. This NMWDI architecture is being developed to enable this use case.
Currently, many (but not all) agency data are already published online through services such as ESRI web maps, excel files, or in some cases public APIs. However, important aspects for a given data type (such as water table groundwater level measurements from wells) such as including date/time formats, geospatial projections, column names, and units, vary from agency to agency and even from dataset to dataset within agencies. In order to allow users to access data from multiuple multiple agencies in one format, the NMWDI architecture will route all agency data through one Web API standard with one corresponding underlying data model that references one common statewide water data controlled vocabulary. As long as each agency somehow serves their data through the common Web API, data storage can be federated (i.e. not centralized), although some degree of centralization can be accomodated accommodated if that is most convenient for a given dataset. Each agency’s standardized API will be published through a central portal with an NMWDI administered API Management Platform. Users can send API requests to the management platform, which will route these requests to the agency APIs and in turn forward the responses to users. However, whether data storage is federated across agencies or centralized, all contributing agency data will be required to be mapped to the common data model amd and controlled vocabulary and transformed into the common format before being delivered to users. This basic data flow is illustrated in Figure 1.
...
The above basic data flow requires a state-wide statewide data model, API standard, and controlled vocabulary. The NMWDI has chosen the OGC SensorThings API (STA) as both the data model and API standard. The OGC is the Open Geospatial Consortium, an international standards organization that creates and publishes open standards for geospatial data management, processing, and sharing. The NMWDI will create a moderated controlled vocabulary service to enable consistent transformation of agency datasets to a common presentation for users.
...
The NMWDI will provide a controlled vocabulary service. This service provides a list of terms that can be used to translate agency data to a common vocabulary. For example, NMBGMR may report the values of its groundwater level measurements in units of “ft” while OSE may report such values in units of “feet”. This is a minor inconsistency. However, a user may want to integrate data from BMBGMR NMBGMR and OSE depth measurements in the same units. A controlled vocabulary service can be used to ensure that each agency’s metadata as provided by SensorThings API is consistent (i.e., uses the same terms for the same concepts).
A controlled vocabulary service provides both a human-readable and machine-readable version of a term that has a URI (Uniform Resource Identifier) that can serve as a common reference. For the example above, rather than NMBGMR contributing data with metadata indicating the units are “ft” while OSE indicates “feet”, both could instead indicate the units as the URI “http://qudt.org/vocab/unit/FT”. This is not only a string that unambigiously unambiguously identifies the unit that is variously symbolized by strings such as “ft”, “feet”, “FT”, etc., but also is a URL that redirects to a reference page generated by the vocabulary service about the unit. Moreover, the vocabulary service also embeds a machine-readable set of metadata that can be used to provide standardized representations of the unit in any output:
...
In the data mapping and transformation processes described below, a controlled vocabulary service can play a critical role in enforcing interoperability for key metadata fields such as observed properties/parameters/phenomena, units of measurement, types of sensors/observation systems/procedures, and so on.
The SensorThings API (STA) data model
The STA data model is based on the Observations and Measurements data model of the OGC, which itself underlies many environmental science data systems that integrate data from many independent organizations. Examples include the CUAHSI HydroClient that provides centralized access to global streamgage, monitoring well, and meteorological networks; and the National Groundwater Monitoring Network that provides centralized access to standardized high-frequency groundwater level and quality data from federal, state, and local agencies. The STA data model provides a unifying metadata standard and data structure standard that can model any data generated about point or polygonal locations on earth. It is important to be able to map agency data to this data model in order to structure each agency’s data in a compatible format and to provide a seamless data request experience to users. The STA data model shown in Figure 2 below, and full specified in this OGC Specification.
...
SensorThings Entity | Description | NMOSE Water Withdrawal Monitoring - Points of Diversion | ||||||
|---|---|---|---|---|---|---|---|---|
Metadata | Location | A unique coordinate or area on the surface of the earth | Location in latitude and longitude or UTM easting and Northing (UTM Zone 13, NAD83) | Street Address (possibly with associated latitude and longitude). (e.g. 3960 PRINCE ST) - geoCoding required | Location in easting and northing (UTM NAD83 in meters) | Thing | Some real-world entity with which one or more Sensors are associated | Well Point ID WL-0150meters) |
Thing | Some real-world entity with which one or more Sensors are associated | Well Point ID WL-0150 (Thing/Property - Well Use (e.g., pumping well)) (Thing/Property - Well Status (permitted, active, capped, plugged, etc.)) → could see another use case focused on Well Status as the observed property | Sample Pt RT236I | Point of Diversion POD Number A 00008 AS | ||||
Datastream | A collection of Observations about an ObservedProperty produced by a Sensor associated with a Thing | Time series, Hydrograph | Sample Results | Meter Readings (Quarterly) | ||||
Datastream/observationType | The type of observation, codified in the Observations and Measurements data standard. Types include Categorical (defined text), Count (integer), Measurement (continuous number), Observation (free text), and TruthObservation (True/False) | Measurement | Categorical or TruthObservation multiDataStream if there are multiple analytes | Measurement | ||||
Datastream/unitOfMeasurement | A three-item definition of the unit of measurement, including its name, symbol, and link to the definition (preferably to one provided in an established ontology such as http://unitsofmeasure.org/ucum.html or http://qudt.org/) | feet (e.g. http://qudt.org/vocab/unit/FT) | TCR Result | FT) | TCR Result (binary indicator: absence/presence) ug/l (micrograms/liter; also = ppb), mg/l (also = ppm), nTu, deg C, %, us/cm (microSiemens/centimeter), “pH” (techically dimless), rads, | Acre-Feet (e.g. http://qudt.org/vocab/unit/AC-FT) | ||
Sensor | The procedure used to provide a Datastream. Can be a particular data recording device model, or a defined procedure followed by a human observer. If applicable, a specific instance (e.g. a sensor model and serial number) | Steel-tape measurement; Continuous acoustic sounder; e-probe; pressure transducer; airline | 9223B-PA (https://www.standardmethods.org/doi/10.2105/SMWW.2882.194) | MCCROMETER Diversion Meter-Meter Number 17147 | ||||
ObservedProperty | The raw or processed phenomenon (quantitative or qualitative) being measured for the Datastream. Preferably including a link to a definition provided by an established ontology or controlled vocabulary such as the ODM2 Controlled Vocabularies or http://qudt.org/) | Depth to Water Below Ground Surface (BGS) | Analyte (e.g. Coliform (TCR) (3100)) Turbidity | Mtr Amount | ||||
OPTIONAL: FeatureOfInterest | The real-world feature that the Observations are about. This may or many not be different from the Location where the Thing on which the Sensor is mounted. Can include a JSON-formatted point location or a polygon or collections thereof. | Formation (e.g. https://maps.nmt.edu/maps/data/hydrograph/formation_lu) | Public Water System (head office location or service area boundary) (e.g. Albuquerque Water System PWSID NM3510701) | Water Right (set of relevant points of diversion) | ||||
Data | Observation | A single measurement value including the result, time values, and other metadata. Information on the ObservedProperty that was measured by what Sensor is provided by the Datastream these observations are in. Features of Interest are linked for each observation as well. Observations are linked to (collected in) Datastreams | Depth Measurement (observation/Parameter - Missing value code (e.g., if well is dry)) (observation/Parameter - Is well recovering?) | Sample (e.g. 763391) | Meter Reading | |||
Observation/result | The actual measured value, with valid values defined in observationType and units defined in unitsOfMeasurement, both provided by Datastream | Depth (e.g. 337.08) | Sample Result (P (Positive/ Coliform found) A (Negative/ Coliform not found)) | Mtr Amount (e.g. 107.948) | ||||
Observation/phenomenonTime | The date+time (or interval) in ISO 8601 format (YYYY-MM-DDT:HH:MM:SS-Z) when the observation occured | 2019-01-31 00:00:00 | MP (Monitoring Period) (e.g. 01-01-2020 to 01-31-2020) (might need to discuss this further to figure out value for entry; does coincide with entry within Drinking Water Watch) | 1/20/2017-04/05/2017 (Quarterly period for which volume was measured) | ||||
OPTIONAL: Observation/resultTime | The date+time that the result was generated. May be the same as phenomenonTime | Date (e.g 01-06-2020) (refers to sampling time, not time of lab analysis) | 04/05/2017 (date of meter reading) | |||||
OPTIONAL: Observation/validTime | The date+time interval during which the Observation can be used (often used for provisional values that are replaced by QA/QC’d observations) | |||||||
OPTIONAL: Observation/resultQuality | A description of the result Quality. Will vary according to agency practice. Can use ODM2 controlled vocabulary for data quality types as a guide. | Precision (e.g. “within two hundredths of a foot”) |
...
These three options, including how they would add to existing agency workflows, are summarized in Figure 23.
Figure
...
3. Options for Providing Agency Data via SensorThings API
...
Option 1: Agency-hosted SensorThings
...
Set up a SensorThings database and API instance in a preferred environment, configuring its domain name and security as appropriate to the agency.
Write scripts to Extract, Transform, and Load (ETL) data from existing databases or tabular data sources (e.g., csv, excel) and upload them to the SensorThings database using the SensorThings API.
Provide the URL of the agency SensorThings instance to NMWDI for registation registration in the API Management platform and CKAN.
...
An example Python workflow for the above three steps from the NMBGMR groundwater level and quality database is available here: https://github.com/NMBGMR/WDIETL/tree/master/etl Similar workflows can be constructed with any scripting language capable of making HTTP requests, making SQL-type queries from database connections and/or importing and manipulating data from tabular data sources such as csv, tsv, and excel files. Common options include Java, Javascript, PHP, R, Shell script, etc. NMWDI can be consulted with for assistance in setting up these workflows.
Step 3: Providing the
...
URL and any necessary authentication to NMWDI
...
In order for NMWDI (and the public) to use the agency SensorThings instance, it must be available from a publicly accessible URL. This will likely require pointing an agency subdomain over DNS to the IP address of the machine the SensorThings instance runs on. It is also adviseable advisable to reverse-proxy the service over HTTPS.
...
To take advantage of this option, the agency should have a public-facing API that they can fully document and participate in an collaborative process with the API Management Platform administrators to map their API to the SensorThings specification and to give administrators specifications as to the load limitations and security considerations the platform should accomodateaccommodate. Below is a demonstration of an API management platform
...