Improving data flow for NGWMN services and Working towards WaterML standards

[inkenbrandt]

We provide a service to the USGS NGWMN of water levels for select wells in Snake Valley. They require that the data are aggregated to daily averages, and that additional informative fields are provided to meet their data standards. New iterations of our database should try to follow WaterML (https://www.ogc.org/standards/waterml) standards.

I would like to modify the view query or the reading table so that data generated in the reading_summary view can be consumed as a service by the USGS NGWMN. This would streamline the workflow for providing those data. This may just result in a modified sql query that we can use to generate the service.

Here is a link to the current service that the USGS NGWMN consumes: https://webmaps.geology.utah.gov/arcgis/rest/services/Groundwater/NGWMN_USGS_V2/MapServer/0

Here are the fields in the service with an explanation of the source of each:

• objectid ( type: esriFieldTypeOID, alias: objectid )
• usgsid ( type: esriFieldTypeDouble, alias: usgsid ): locationid field in the ugs_ngwmn_monitoring_locations table; not to be confused with altlocationid, which is the field that joins to the locationid field in our reading table
• readingdate ( type: esriFieldTypeDate, alias: readingdate, length: 8 )
• locationid ( type: esriFieldTypeDouble, alias: locationid ): locationid field in our reading table
• timezone ( type: esriFieldTypeInteger, alias: timezone ): CONSTANT; -7
• uom ( type: esriFieldTypeString, alias: uom, length: 8000 ): CONSTANT; feet
• depth_to_water ( type: esriFieldTypeDouble, alias: depth_to_water ): measureddtw
• wlmethod ( type: esriFieldTypeString, alias: wlmethod, length: 8000 ): CONSTANT; transducer
• quality ( type: esriFieldTypeDouble, alias: quality ): driftcorrection field in reading table
• latitude ( type: esriFieldTypeDouble, alias: latitude ): latitude of site in the ugs_ngwmn_monitoring_locations table;
• longitude ( type: esriFieldTypeDouble, alias: longitude ): longitude of site in the ugs_ngwmn_monitoring_locations table;
• shape ( type: esriFieldTypeGeometry, alias: shape )

[marthajensen]

@inkenbrandt charge code for this work is UNG6

[marthajensen]

@inkenbrandt I think I've got this working. Attached is the output CSV from the SQL query (shown below)- please review it, then let's chat about next steps. Make sure you change Column B to a number to get it to display properly. The Shape field matches the Shape field in the monitoring locations table - so even though it looks weird, I think it's right

reading_summary_ngwmn.csv

DROP VIEW IF EXISTS sde.reading_summary_ngwmn; CREATE OR REPLACE VIEW sde.reading_summary_ngwmn AS

SELECT (row_number() OVER ())::integer AS objectid, a.locationid as usgsid, date(r.readingdate) as readingdate, r.locationid as locationid, '-7' as timezone, 'feet' as uom, r.measureddtw as depth_to_water, 'transducer' as wlmethod, r.driftcorrection as quality, a.latitude as latitude, a.longitude as longitude, a.shape as shape

FROM sde.ugs_ngwmn_monitoring_locations as a INNER JOIN reading r

ON a.altlocationid=r.locationid;