<jupyter_start><jupyter_text>River Mouth Surface Water Quality DataCollection of river mouth surface water quality data from Copernicus Marine Service products, starting with utilization of CMEMS WMS for data retrieval.  _Author: <NAME>_  _Created: 9 Jul 2021_  _Environment: jupyterlab_   Ocean Watch seeks to feature several biogeochemical parameters from the [Global Ocean Biogeochemistry Analysis and Forecast (GLOBAL_ANALYSIS_FORECAST_BIO_001_028) product](https://resources.marine.copernicus.eu/?option=com_csw&view=details&product_id=GLOBAL_ANALYSIS_FORECAST_BIO_001_028) of the [Copernicus Marine Service](https://marine.copernicus.eu/). The data will be mapped via Resource Watch. OW will also show time series of the parameters at various locations (representing outlets of rivers to the ocean).    Source Presenting that time series data would typically require harvesting the information from the historical product rasters. However, the CMEMS [Pretty View](https://view-cmems.mercator-ocean.fr/GLOBAL_ANALYSIS_FORECAST_BIO_001_028) WebGIS tool displays just such time series (via the `Tools`). Furthermore, these are generated using the public-facing [WMS](https://www.ogc.org/standards/wms). The Copernicus Marine User Support Expert indicated [the relevant documentation](https://docs.geoserver.org/master/en/user/services/wms/reference.htmlgetfeatureinfo). Constructing a properly formatted `GetFeatureInfo` request from scratch without examples was unsuccessful. However, spying on the Pretty View tool while making a time series request revealed [just such an example](https://nrt.cmems-du.eu/thredds/wms/global-analysis-forecast-bio-001-028-daily?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&QUERY_LAYERS=chl&BBOX=53.26,14.26,53.2600001,14.2600001&HEIGHT=1&WIDTH=1&INFO_FORMAT=text/xml&SRS=EPSG:4326&X=0&Y=0&elevation=-0.49402499198913574&time=2020-07-16T12:00:00.000Z/2021-07-16T12:00:00.000Z):```htmlhttps://nrt.cmems-du.eu/thredds/wms/global-analysis-forecast-bio-001-028-daily?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&QUERY_LAYERS=chl&BBOX=53.26,14.26,53.2600001,14.2600001&HEIGHT=1&WIDTH=1&INFO_FORMAT=text/xml&SRS=EPSG:4326&X=0&Y=0&elevation=-0.49402499198913574&time=2020-07-16T12:00:00.000Z/2021-07-16T12:00:00.000Z```This query returns daily readings of `mass_concentration_of_chlorophyll_a_in_sea_water` at a depth of about half a meter for approximately the preceding year, at a location between Socotra and Yemen.  A similar example closer to OW's needs can also be generated. [The query](https://nrt.cmems-du.eu/thredds/wms/global-analysis-forecast-bio-001-028-monthly?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&QUERY_LAYERS=o2&BBOX=31.01,31.74,31.010000100000003,31.7400001&HEIGHT=1&WIDTH=1&INFO_FORMAT=text/xml&SRS=EPSG:4326&X=0&Y=0&elevation=-0.49402499198913574&time=2019-01-16T12:00:00.000Z/2021-05-16T12:00:00.000Z) for the full times series of monthly `mole_concentration_of_dissolved_molecular_oxygen_in_sea_water` values at a half-meter of depth at a pixel near the mouth of the Nile:```htmlhttps://nrt.cmems-du.eu/thredds/wms/global-analysis-forecast-bio-001-028-monthly?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&QUERY_LAYERS=o2&BBOX=31.01,31.74,31.010000100000003,31.7400001&HEIGHT=1&WIDTH=1&INFO_FORMAT=text/xml&SRS=EPSG:4326&X=0&Y=0&elevation=-0.49402499198913574&time=2019-01-16T12:00:00.000Z/2021-05-16T12:00:00.000Z``` This template can be modified to accommodate OW needs. Capabilities of service requested via:```htmlhttps://nrt.cmems-du.eu/thredds/wms/global-analysis-forecast-bio-001-028-monthly?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetCapabilities``` Key excerpt from response:```xml                            image/png                text/xml                                                                                                                                                                ```Data can only be returned in `text/xml` format, not the preferred JSON (`application/json`).  Parameters  Variable- _mole_concentration_of_dissolved_molecular_oxygen_in_sea_water (mmol m-3)_    - `QUERY_LAYERS=o2`  - _mole_concentration_of_phosphate_in_sea_water (mmol m-3)_    - `QUERY_LAYERS=no3`  - _mole_concentration_of_nitrate_in_sea_water (mmol m-3)_    - `QUERY_LAYERS=po4`    Note: providing a comma-separated list of valid layers (eg `QUERY_lAYERS=o2,no3`) returns an exception (`400` response)  Depth- `elevation=-0.49402499198913574`- `elevation=-1.5413750410079956`- `elevation=-2.6456689834594727`- `elevation=-3.8194949626922607`- `elevation=-5.078224182128906`  Bounding Box`xmin`,`ymin` are just the point of selection; `xmax`, `ymax` are just minimum value plus 0.0000001  Example Request Oxygen```htmlhttps://nrt.cmems-du.eu/thredds/wms/global-analysis-forecast-bio-001-028-monthly?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&QUERY_LAYERS=o2&BBOX=31.01,31.74,31.010000100000003,31.7400001&HEIGHT=1&WIDTH=1&INFO_FORMAT=info_format=text/xml&SRS=EPSG:4326&X=0&Y=0&elevation=-0.49402499198913574&time=2019-01-16T12:00:00.000Z/2021-05-16T12:00:00.000Z```  WorkflowUtilizing the CMEMS data within an OW widget entailed several steps. And prerequisite to all of those was a phase in which a river mouth dataset was constructed. Compile River Mouths - Identify river mouth locations     - Script: [river-mouths_identify-points.js](/datasets/ocn_020_alt_chemical_concentrations/river-mouths_identify-points.js)         - [On Google Earth Engine](https://code.earthengine.google.com/736a3ca06cf73e1d4a2894f4dc42b5f0)     - Key source: [MERIT Hydro](https://doi.org/10.1029/2019WR024873)         - Google Earth Engine asset `MERIT/Hydro/v1_0_1`     - Set of river mouths stored as points         - Google Earth Engine asset `projects/resource-watch-gee/ocean-watch/river-mouths` - Affiliate river mouths with actual rivers     - Script: [river-mouths_match-mouths-with-rivers.js](./river-mouths_match-mouths-with-rivers.js)         - [On Google Earth Engine](https://code.earthengine.google.com/fd7e922bb8a4e55770dc6f3f9159df9d)     - Key source: [HydroRIVERS](https://www.hydrosheds.org/page/hydrorivers)         - River network uploaded to Google Earth Engine as asset `projects/resource-watch-gee/ocean-watch/river-network`     - Every MERIT Hydro river mouth associated with a HydroRIVERS river         - Output manually uploaded to [Carto table](https://resourcewatch.carto.com/u/wri-rw/dataset/ocn_calcs_010_target_river_mouths) Label Rivers - Harvest river names     - Script: [river-mouths_harvest-names.py](./river-mouths_harvest-names.py)         - Result written to shapefile locally     - Key source: [OpenStreetMap](https://www.openstreetmap.org/) - Collect names for missing areas     - Notebook: [river-mouths_harvest-missing.ipynb](river-mouths_harvest-missing.ipynb)         - Retrieved data merged with that from previous step and saved as shapefile locally     - Key source: Again [OpenStreetMap](https://www.openstreetmap.org/) - Assign names to river mouths     - Notebook: [river-mouths_assign-names.ipynb](river-mouths_assign-names.ipynb)         - Describes manual execution in ArcMap, the output of which is passed to the Carto table containing the completed river mouth point dataset         - Not all rivers identified in the MERIT Hydro/HydroRIVERS dataset are named in OSM data Collect Water Quality Data - Identify coordinates for each river mouth that overlap with the ocean water quality data products     - Script: [river-mouths_adjust-coordinates.py](./river-mouths_adjust-coordinates.py)     - This only needs to be executed once. The results are stored persistently on Carto.       - Valid coordinates could not be algorithmically identified for every river mouth, leaving about 500 outstanding river mouths. From these, valid coordinates were manually identified for the dozens of rivers with `ORD_FLOW` rank higher than 5, leaving over 400 rank 5 rivers without coordinates. These are to be addressed at a later date. - Test adjusted coordinates for validity (ie valid WMS response)     - Script: [river-mouths_test-coordinates.py](./river-mouths_test-coordinates.py)     - This identifies river mouths where the "valid" coordinates still do not return a proper response. These must then be manually corrected within the river mouths table.     - Note that the WMS is not perfectly consistent, so coordinates may be successfully tested but still fail during data harvesting. This seems to occur when the adjusted coordinates are very near the edge of the land mask. - Add basin information to river mouth data     - Script: [river-mouths_join-basins.py](./river-mouths_join-basins.py)     - Joining `PFAF_ID` and `HYBAS_ID` from HydroBASINS dataset to the river mouths dataset, which is largely based on the HydroRIVERS dataset, links each river mouth to the containing basin at any desired levels (here 3 and 6). - For validated river mouth locations, use WMS calls to harvest and store all relevant time series data     - Script: [river-mouths_data-collection.py](./river-mouths_data-collection.py)     - These actual river mouth quality data are stored in [a separate table](https://resourcewatch.carto.com/u/wri-rw/dataset/ocn_020alt_chemical_concentrations).  Performance EnhancementIndices added to river basins table for faster service of data to parameterized chart widgets ```sqlCREATE INDEX ON wat_068_rw0_watersheds_edit(	geostore_prod  )WHERE level = 5 AND coast = 1``` ```sqlCREATE INDEX ON wat_068_rw0_watersheds_edit(	hybas_id  )WHERE level = 5 AND coast = 1```  Results  River Mouth LocationsOriginal locations and effective (ie valid for WMS request) coordinates are stored in [the same table](https://resourcewatch.carto.com/u/wri-rw/dataset/ocn_calcs_010_target_river_mouths), with the initially calculated location represented by the geometry, and the validated coordinates stored in `x_valid` and `y_valid`.<jupyter_code>import os
from dotenv import dotenv_values
env_vars = dotenv_values('/home/pkerins/code/.env')
from cartoframes.auth import set_default_credentials
from cartoframes import read_carto, to_carto
import pandas as pd
CARTO_USER = env_vars['CARTO_WRI_RW_USER']
CARTO_KEY = env_vars['CARTO_WRI_RW_KEY']
set_default_credentials(username=CARTO_USER,
                        base_url="https://{user}.carto.com/".format(user=CARTO_USER),
                        api_key=CARTO_KEY)
read_carto('ocn_calcs_010_target_river_mouths').tail()<jupyter_output><empty_output><jupyter_text>Chemical Concentrations at River MouthsCorrected locations are used to retrieve longitudinal data for the concentration of various chemicals at a range of depths. The results for all locations, chemicals, and depths are stored in [a single table](https://resourcewatch.carto.com/u/wri-rw/dataset/ocn_calcs_011_river_mouth_chemical_concentrations).<jupyter_code>read_carto('ocn_020alt_chemical_concentrations').tail()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09476584022038567, 'fraction_numerical': 0.06767676767676768, 'mean_word_length': 4.880669546436285, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 10, 'https://': 22, 'lorem ipsum': 0, 'www.': 4, 'xml': 8}, 'pii_count': 0, 'substrings_counts': 3, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '194434', 'license': 'MIT', 'n_tokens_mistral': 3949, 'n_tokens_neox': 3386, 'n_words': 875, 'path': 'datasets/ocn_020_alt_chemical_concentrations/river-mouths_process-overview.ipynb', 'repo_name': 'resource-watch/ocean-watch-data', 'size': '10896'}