California-Operated Stream Gages data to WaDE

[amabdallah]

Let's start here. Ignore stations that are operated or their data is accessible through USGS https://gispublic.waterboards.ca.gov/portal/home/item.html?id=32dfb85bd2744487affe6e3475190093

The actual time series data is available through Geoconnex here (Check with Kyle) https://sb19.linked-data.internetofwater.dev/

[amabdallah]

http://cdec.water.ca.gov/cgi-progs/staMeta?station_id=YPB Broken link: Solution as suggested by Kyle: For the cdec ones just change cgi-progs to dynamicapp Example: https://cdec.water.ca.gov/dynamicapp/staMeta?station_id=YPB

[amabdallah]

Kyle has organized CA gage location metadata and put it in Geoconnex. https://sb19.linked-data.internetofwater.dev/collections/ca_gages/items?startindex=200

Each site has URL pointing to its page on California's system

This is the historic, QC data for ones DWR tracks https://wdl.water.ca.gov/waterdatalibrary/ContinuousData.aspx?ReloadData=False

[rwjam]

Unnecessary links, don't use:

• Geoconnex link here: https://sb19.linked-data.internetofwater.dev/
• broken link: http://cdec.water.ca.gov/cgi-progs/staMeta?station_id=YPB
• Individual site info for Site# YPB, but nothing to draw upon: https://cdec.water.ca.gov/dynamicapp/staMeta?station_id=YPB
• custom landing page for some CA gage locations that returns 200 sites. However, can't adjust that 200 value accurately and get exact number of sites, use shapefile option instead: https://sb19.linked-data.internetofwater.dev/collections/ca_gages/items?startindex=200
• Not sure how this relates. Some historic info maybe, but only individual sites and single time stamp. Not great for pulling data: https://wdl.water.ca.gov/waterdatalibrary/ContinuousData.aspx?ReloadData=False

Use these:

• Data we want specifically comes from here: https://cdec.water.ca.gov/index.html
• shapefile info here, want siteid field, ignore all sites that have a operator = USGS: https://gispublic.waterboards.ca.gov/portal/home/item.html?id=32dfb85bd2744487affe6e3475190093
• Use something like this to attach timeseries data to sites. Attach to each siteid value. We don't know min & max time stamp, so will have to guess for now. Guessing makes the query take longer, expect to this take some time: https://cdec.water.ca.gov/dynamicapp/req/CSVDataServlet?Stations=CMR&SensorNums=1&dur_code=E&Start=2020-12-28T00%3A00&End=2022-03-15

import requests
import io
url = "https://cdec.water.ca.gov/dynamicapp/req/CSVDataServlet?Stations=CMR&SensorNums=1&dur_code=E&Start=2020-12-28T00%3A00&End=2022-03-15"  # Test here
r = requests.get(url)
r = r.content
rawData = pd.read_csv(io.StringIO(r.decode('utf-8')))
rawData

[rwjam]

May be too many records to retrieve. Resulting file was 35GB in size. Tried both a 1950 and 2000 start date year, with similar results.

Also ran into memory issue with script, not sure how that will affect things.

[rwjam]

Going to use SensorNums=65 and only use sites that have a known timeseries of monthly for now (about 24 sites). Good place to start.

• Example API: https://cdec.water.ca.gov/dynamicapp/req/CSVDataServlet?Stations=ERS&SensorNums=65&dur_code=M&Start=1950-03-22&End=2022-03-22

[rwjam]

Going to also include reservoir level time series data. I was able to literally just copy / paste a site info table from their website. Looks like we can use that same API call too using a a SensorNums = 15 for monthly RESERVOIR STORAGE, AF.

• https://cdec.water.ca.gov/dynamicapp/req/CSVDataServlet?Stations=DRE&SensorNums=15&dur_code=M&Start=1950-03-22&End=2022-03-22%22