Summary

These notes were based on a recorded ‘Deep Dive’ with Julie to expand on the details of the steps she goes through to compile her Water Use by Categories report. The video, whose timecode related to notes, is referenced here, can be found at following URL:

Share recording with viewers:

https://us02web.zoom.us/rec/share/yPcuMr3661NJU9LDr3-cWv4qJI2ieaa80yRL_foFnxtpGYmyvRI2EUkPKAyztsRs Password: 1v#2A774 

1.1

how do you get access to Waters DB?

request report from David Anderson. i.e. want all “20XX meter data for the entire state”

Julie asks for specific criteria: OSC file (permit number) , name of entity, county, meter data (surface and ground water withdrawals), x/y coordinates to locate point of diversion, they give you POD? , use code, owner first name, owner last name

Use code has different categories, not just public supply

public water suppliers are determined based on their use code

• MDWCA (community type)

• MOB (mobile home park)

• MUN (municipal)

• SAN (sanitary use)

• SUB (subdivision)

• UTL (public utility)

• PMH (well permitted for multiple households)

master sheet (which Emily shared) is what is received in report from Waters DB via David Anderson

Julie may ask for ‘comments’ as an output field.

The Water application was converted from original paper format to digital format.

comments help when certain permits went into place (was case for oil & gas but not necessarily public), helps determine what stage the water permit application was in.

Julie said she doesn’t really need comments to determine records for inclusion though. Use code will suffice

note: sometimes ‘commercial’ use code are redesigned as public by Julie per her own discretion.

Use codes are rarely changed once assigned

1.2 (17:45)

Julie gets PWS list from NMED because of their relationships w/ EPA they have a list of all public water suppliers in the state so she can have most current list of PWS . Many PWS are regionalizing, and consolidate and NMED is first to know. Sometimes new PWS are created (and don’t exist in Waters db). NMED updates PWS every 7 years. Julie does not know where they are in the update cycle however.

PWS have to report to NMED every 3 years (or so) their water quality data so this is an opportunity to update their information.

Next step is to make sure that all meter data is accounted for in Waters DB master list that is in NMED PWS list. She doesn’t entirely rely on NMED list but may fill gaps in certain cases where discrepancies exist.

Julie indicated sometimes NMED has different meter data than OSE does.

if WatersDB and NMED have a discrepancy for a given field, Julie makes a judgement call as to what the final value will be. She annotates her decisions on the discrepancy.

Also if NMED has a PWS as an ‘inactive’ she will remove that PWS from her master list. For conflicting values between Waters and NMED, Julie will assess. For instance, if a PWS says they are receive all their water now from ABQ/Bernalillo then she will remove because failure to do so would result in double counting.

Emily indicated that if a PWS has less than 15 customers they become inactive.

To reiterate, Waters DB is largely readonly from Julie’s point of view . She only omits PWS entries from her master spreadsheet (she generally does not have privilege to change records).

NOTE: Amy Lewis mapped out all service boundaries for (all?) PWS. (verify with Julie). She may have included SDWIS number for each PWS.

1.3 (41:55)

Julie indicates that she joins/correlates by ‘System Name’ (and not some ID) between a given PWS in Waters DB and NMED/SDWIS .

A copy of the spreadsheet that comes from Daniel Ramirez (NMED/SDWIS) as a point of comparison with master spreadsheet was provided by Julie ( KenOSE.xlsx )

Julie estimated that around 30-40% don’t match by a strict comparison between system names (WatersDB vs NMED/SDWIS)

Stacy indicated that Daniel Ramirez is attempting populate OSE ID into SDWIS. Not clear if that is completed and perhaps could be useful in matching records in future between Waters DB and NMED/SDWIS by OSE ID.

Andrew asked about an existing column name mapping between NMED/SDWIS spreadsheet (KenOSE.xlsx) and what comes from Waters. Julie said mapping does not exist and implicitly indicated that column names will likely not be the same (although a semantic match could be possible?) .

System name, and population are evaluated against master list. It’s mostly useful to know how many systems there are.

Master list is all lumped together by system (and not by wells) .

KenOSE.xlsx fields of interest are System name, facility name, water type, active/inactive Q(facility active) /R (well active) (decide to include on list or not), population, city (to double check location),

2.1 (55:58)

Copy of survey was provided by Julie (Survey.xls).

The survey is one way to get data to include into master spreadsheet. If there are multiple sources for a given data point , one will be chosen based on experience and a note as to rationale will be given as well

A given survey result from a PWS is compared manually against what has already been compiled between Waters DB and NMED/SDWIS. Personal judgement is used to reconcile any differences noted between the 3 sources to form the final snapshot of the PWS in thee master list.

All surveys get manually entered into an excel spreadsheet (Survey.xls) . If perceived out of range values are observed during evaluation of answers, Julie and/or colleague will call PWS to verify.

In cases where PWS do not respond with discrete values (often numeric), Julie will interpret the free text response and decide how to translate into the survey answer. For instance, Taos may indicate a given amount of water was used for snowmaking and the rest for public consumption. In this instance, Julie only recorded the public use consumption as snow making does not qualify for public use .

Of all the survey answers (15), only the withdrawal and population (served) are mapped to master sheet.

Again it was noted that if a supplier gets it’s water from another supplier, then their numbers are excluded. For instance of PWS x & y get their water from ABQ/Bernalillo, then their numbers are excluded (failure to do so would result in a double count).

3.1 (01:10:25)

Use Case described (interpreting free text survey answers):

An entity named ‘Evergreen’ in Dona Ana County , who reported 4.9acre/ft for outdoor irrigation. However, they are importing 11 acre/ft from Dona Ana County mutual domestic. However, their meter data was 16 acre/ft. Julie includes all of the above in a comment by the withdrawal. The information from survey ended up being a comment and she decided to use the value 27.3 acre/ft (which probably came from meter data).

The purpose of requesting meter data from Waters DB is to fill in blanks from survey that Julie did not originally receive from David Anderson.

3.2 (01:13:05)

3.3 (01:13:19)

This means put information (meter readings or reported water use) into master spreadsheet.

3.4 (01:14:05)

Any discrepancy will be explained in a comment in master list

Stacy asked if the water systems (PWS) have different time intervals to report meter data. Julie indicated yes. She said this can be an issue if she compiles meter data to soon because one or more PWS has not yet submitted their meter data. Julie tries to start in March. Julie performs this process every 5 years.

(01:18:01) Stacy asked if every PWS is required to report meter readings as a requirement to OSE. Julie responded ‘No’. There are PWS who are required to report meter readings by conditions of permit, however. Typically older permits have no such requirement. For those that are required to report meter readings, typically the frequency is quarterly, sometimes monthly. As permit requests come to OSE to permit any new wells, then the reporting requirement is put into effect for the new permits. Julie indicated that this is a primary reason that they have to draw from multiple sources to obtain meter readings from PWS to attempt to fill the gaps due to lack of meter reading requirement submissions across the board for all PWS.

Julie noted that in cases where they can get meter readings from other sources (not OSE) they estimate based on GPCD.

(01:21:46) Julie also looks at population number (current vs any previous but typically last 5 year population value) to see any indication of an outlier value.