NWS API Data Pull

[BobLfoot 301]

I have done my fair share of Excel VBA coding and even some SQL DB manipulations, but these newer API, Json, XML style queries still puzzle me quite a bit.  I'd like to take the data which comes back from this National Weather Service API Call - {https://nwis.waterservices.usgs.gov/nwis/iv/?format=json&indent=on&sites=01197000&parameterCd=00060&startDT=2020-12-08&endDT=2020-12-08} and stuff the values and DateTime stamps into an excel sheet or SQL DB.  Eventually I'll use the data to adjust the gains on my Pond Level PID since this data tells me what my inflow to my pond was.  Any simple suggestions or solution ideas are welcome.

[BobLfoot 301]

Well some success, found out if I change from json to waterml format I get an XML file.   {https://nwis.waterservices.usgs.gov/nwis/iv/?format=waterml&indent=on&sites=01197000&parameterCd=00060&startDT=2020-12-08&endDT=2020-12-08} and the waterml / xml format can be imported into Excel and manipulated by macros.  Might need to leave a vb.net app running to translate from Website to PLC, but looks very doable now.

[Steve Bailey 118]

Where can I find what data is available from that NWS location? One of my customers gets alternative energy credits for using process steam for feedwater heating. They don't have a functioning temperature sensor on the incoming water line so they've been using a conservative constant value. If the NWS database includes water temperature along with flow they might be able to use that. There shouldn't be too much of a temperature difference between Housatonic river water at Coltsville and the Pittsfield city water main under Dalton Avenue coming from the Cleveland Reservoir.

[BobLfoot 301]

@Steve Bailey If you go to https://nwis.waterservices.usgs.gov/ you'll see the top level page and all the information about how data is published and what might be available.  I don't know if temp is an available parameter or not.  We're concerned with flow rates as it effects our potential production.  You might also play around with the Test Tool - https://nwis.waterservices.usgs.gov/rest/IV-Test-Tool.html.

[Steve Bailey 118]

Yeah, I checked. All they have available is flow. The NWS is primarily concerned with flow so they can forecast when a river might reach or exceed flood stage.

[BobLfoot 301]

I did find some temps on other water sources listed on the test page.  I'd keep poking around you might find something useful.

[Steve Bailey 118]

I found some as well, but nothing from the East branch of the Housatonic. The closest I found was the Cold River in Savoy and Florida.