Division of Drinking Water Lead Service Lines App

[ZachBeck]

Benefit

The ability to view service line locations with dashboard elements for service line inventory submitted through DDW LCRR portal.

Acceptance Criteria

• ETL data from DDW service line inventory database
• Create web maps and dashboards to visualize service line data.

Notes

DPR-1081
Project SOW

Risks

No response

Issue Reference

refs #

[ZachBeck]

Waiting to start.

[jacobdadams]

25.1.3

Met with Atie Amirgol (DEQ) and Jeff Mobey (DTS) on 8 Aug. Jeff will be creating a database of the records and will provide a view that will be the source of truth for the map data. This view will always have lat/longs (either provided by the submission or geocoded through the API).

Jeff hopes to get the db up by the end of Q1. Once it's up, we'll have a consistent schema through the view so we can create a feature service and start work on the map/app.

After the DB is up, I will work with Jeff to set up a cloud function that can be triggered by webhook so that whenever a record gets added to the DB it calls the webhook on the function to add the new data to the feature service (probably just by doing an on-demand truncate and load operation). This will be started end of Q1/beginning of Q2.

[jacobdadams]

25.1.6

Met with Atie and Jeff again on 11 Sept to go over the data needed for the map. Jeff is finalizing the data and preparing a view for us to connect to.

Atie wants a map where you can click on a point and get a couple pieces of information for that line:

• System name
• PWSID
• address
• material classification
• material for both system- and customer-owned segments
• basis of material classification for both system- and customer-owned segments
• field verification method

We will need to modify field display names and do some arcade to collapse some of the categories in the material fields into groups.

She would also like a way to search/pan/zoom to a specific system.

We also discussed adding DNR's CulinaryWaterServiceAreas layer and pulling in a field from a google sheet to indicate which systems have a "no-lead" status completely. This would be done with a scheduled palletjack update from the sheet. We'll need more discussion on the symbology of this layer and which systems to show/not show.

Atie would like a map in place by the 16th of October. I told her we could probably get a web map up with static data by then but won't be able to get the auto updates wired in.

Next Steps:

• Reach out to Jeff to get access to the database view he's creating for us.
• Reach out to Atie to get access to google sheet for overall system status.

[jacobdadams]

25.1.6 pt2

Met with Jeff and Atie on 27 Sept to discuss next steps.

• Jeff will provide us with a csv dump of the data so we can get working on the initial map
• Jeff will create an API for us to hit to access the data (because the skid will run in GCP, providing direct, IP address-based access through the firewall will be tricky, and the API will be easier).
• Atie will share the sheet with the project area-specific data with Zach and Jake

[steveoh]

• (because the skid will run in GCP, providing direct, IP address-based access through the firewall will be tricky, and the API will be easier)

This might be a good use case for apigee if you want to discuss it with Ben to see if it can simplify things for everyone.

[jacobdadams]

I think he might be using it already. Jeff mentioned there was a push to get things on apigee, but the context of the discussion almost made it sound like "but I don't want to use it for this."

[jacobdadams]

25.2.1

Still waiting on API from Jeff. Got updated data for map that Zach is working on.

[ZachBeck]

First stab at the app
https://ddwlead-hub.maps.arcgis.com/apps/dashboards/690020443e57445783a050c410affd78

[ZachBeck]

Updated water service polygons added and indicator box values are now static rather than returning values of extent as per Atie's request 11/4.

[jacobdadams]

25.2.3

Trying to merge their system review spreadsheet to DWRe's Culinary Water Service areas. There are PWS IDs on the spreadsheet that aren't in the service areas layer. I'm guessing most of these are smaller system, but the Municipal Water District of Salt Lake and Sandy is not represented in the areas; instead, both cities have their own separate layers. If we want to get this working, we'll need to work out these discrepancies and figure out a way to crosswalk the larger systems that are supersets of other systems.

[jacobdadams]

25.2.4

Got access to dev DB api via GraphQL, working on way to connect through python and dataframes.

Met with Atie and figured out that the missing PWS polygons are not needed, so we can continue to use their sheet matched up with DWRe's polygons to show the system boundaries.

[jacobdadams]

25.2.5

Got a rough draft of GraphQL loader and started work on GSheet loader for pulling data from these two sources.

[jacobdadams]

25.2.6

Nearly finalized skid, just waiting on DTS to figure out why we're getting random amounts of NULL values in certain columns. We're close.

[ZachBeck]

App sent off for another review
https://ddwlead-hub.maps.arcgis.com/apps/dashboards/690020443e57445783a050c410affd78