The New California Water Atlas

Making water understandable in California

General

"California State Well Numbering (SWN) system wells are assigned a unique identifier without using a computer." - Yolo County Flood Control & Water Conservation District Manager

This video underscores how backwards some of our state agencies are with their internal workflows. Putting aside making data available to the public, the perpetuation of inefficient and outdated workflows circa 1970 is borderline scandelous in today's digital era.

It is understandable that decades of historical records are expensive to digitize, but there is little excuse when new data is compiled and archived in such ways. We can do better.

In our effort to build a digital public work for water in California, we are sharing open government methodologies, and civic technologies with all agencies we come into contact with. We do this from a place of love and understanding. We are fully aware of strained budgets and staffing, and are here to lend a hand.

Lets make California awesome!

Our work featured in the Associated Press. Find the article at these various media outlets:

San Diego Union Tribune

San Jose Mercury News

Sacramento Bee

SFGate

I had the privilege of attending the Department of Water Resources's Water Plan Plenary this year. The Water Plan is a comprehensive planning framework that invites all stakeholders and interested parties to participate in forming future directives for California water. This year, the plenary was held at the Red Lion Woodlake Conference Center in Sacramento. Amidst the sounds of clinking chips from the in-house casino, and the three story spray of a massive courtyard fountain, there was much discussion about the future of water in our changing state.

See PDFs of slide presentations here.



Water Plan Plenary Budget Poster

While orderly, there was much deliberation and hesitation over language and assumptions. Between multiple sessions, and interim discussions, it became apparent, that a clear description of the often nuanced and complex issues facing water users and our environment was of paramount importance. In our review sessions, we could hardly get past a single slide without a twenty minute discussion. The goal was clearly to get feedback, so spending large amounts of time on a single phrase, map, or diagram was not out of scope for such a meeting. I was impressed by the emphasis on communicating to citizens and stakeholders, especially in light of how little budget is devoted to a “public understanding” of water at all levels of government.

The highlight though was a summary and recommendations by tribal leaders recounting and reflecting on the recent Tribal Water Summit. It was great to hear from tribal leaders about progress that has been made, and the long road ahead reconciling the many issues that tribal communities face. A refreshing and needed interlude was a very basic intro to the concept of tribal ecological knowledge by Ron Goode, Chairman of the North Fork Mono tribe.

Groundwater Session

During a groundwater session, I had the opportunity to make recommendations about DWR's CASGEM groundwater elevation data repository. Armed with guidelines from both the Sunlight Foundation, and the Open Knowledge Foundation, and my own intimate knowledge of CASGEM, I suggested a handful of improvements. Here are some the documents that I drew from:

Open Data Policy Guidelines, Version 2.0

Defining Open Data

As someone focused on making water understandable, I was moved by this focus shared by many at the Water Plan Plenary, but troubled by its execution within DWR. Translating this information is hard work. There are professionals that stake a career in being able to communicate through interactive visualizations, or static graphics complex systems and relationships. After speaking to various DWR employees about workflows and methods, it became clear that not only is there not enough resources to support in-house efforts to communicate these issues, but little resources exist to seek private sector help to convey these priorities.

We held a participatory design workshop at Stamen and invited participants to ask their questions about groundwater. Here is the list.

General

  • Where and who uses groundwater?
  • Where is tracking happening?
  • Which farms use groundwater?
  • How many (un)monitored wells are there?
  • If I report groundwater that is previously unreported, will someone get in trouble?
  • How bad is the groundwater situation?
  • Is groundwater regulated in an environmentally and socially just way?

Geology Underground

  • Where is groundwater?
  • What is the geological structure under me?
  • How does the water flow underground?
  • Do we how how much water is left in the ground?
  • Can I compare counties?
  • How do we know where the groundwater is?
  • Do we, like, dig for it or something?
  • Where is the water table near a well?
  • How much water do the wells pull?
  • Where is groundwater in the East Bay?
  • How does the price I pay for water compare to commercial waters (farmers, manufacturers)

Water Quality

  • What effect does the type of ground have on water?
  • What is the quality of the water
  • Is the groundwater toxic?

Water Supply

  • Which cities drink ground water?
  • Does my drinking water come from ground water?
  • Where does my water come from?

Time

  • Changes over time given constraint
  • Can I see difference levels of rainfall over the years affection water level?
  • Is there enough water to live in California for 25 years?
  • How has the level changed every year?
  • Do wet years give a chance to refill the groundwater?
  • What are the seasonal fluctuations in water levels?

Meta

  • Visual metaphors other that geometry
  • Who is the primary type of person who needs to interact with the data?
  • Will the new CA Water Atlas work on a Really big tablet so it ccan be like the first book?
  • How different will the new CA water atlas be from the old one?

Definitions and Quantities

  • Can we see pictures of land subsidence?
  • Do groundwater and land subsidence correlate?
  • Where does ground water and surface water interact?
  • Is the watertable the same as groundwater?

Define the words

  • Aquifer
  • Groundwater
  • Water table

IMG_3085

What do you want to know or see about groundwater?

Chach's New California Water Atlas manifesto is published in the Fall 2013 issue of Boom: A Journal of California

Read it

We presented the New California Water Atlas at the UC Berkeley Geospatial Innovation Facility to a group about about 30 mappers.

We talked about using open data and open government strategies to improve citizen’s understanding of our natural resources. Was a great group of people and they asked hard questions about water pricing and obfuscated data.

Afterwards, we spoke with Kevin Koy about Cal-Adapt and discussed water API's for California water datasets. Really exciting stuff - especially the idea that we might be able to access future temperature projection data and the like. In the next few months should start to have in our hands more data that we can work with.`

We were invited to present a project at the TechRaking #3: Mining the News conference at the GooglePlex on August 8, 2013, organized by the Center for Investigative Reporting.

We have been working on making it easier to work with groundwater data this summer. We have been working on aquifer boundaries, aquifer metadata, and well level data. We knew that it would be important to have this basic information in order for journalists to be able to report on groundwater more effectively. One issue that is very relevant in California are the various types of ways in which oil and gas are captured in proximity to groundwater.

Many journalists from many news organizations attended our group. We brainstormed sets of data that are necessary to report on fracking, and designed a document that can be passed around among those interested to organize and collect the data necessary to report effectively on this important issue.

Check out the document here. This is a google doc that is currently in the process of being passed around to various journalists. It is a very simple project, but could help with getting information about fracking out there. The document explains the rest of what you would need to know and how you can help.
FrackTracker: Fracking Data Document

FrackTracker presentation

Map of Fracking wells with Vulnerable Aquifers

This is part 2 in a series of posts that chronicle our work with groundwater elevation data. Using California's Department of Water Resources's CASGEM system, we were able to retrieve 30 years worth of well readings for the entire state, but not without headache.

Since our last post Water Level Data from DWR, we were able to expose an unpublished ArcGIS REST endpoint. While this is not suitable for our bulk data download needs (only returns 1000 records max), it will come in handy when we need to retrieve updated entries from the CASGEM system.

Using Charles, a HTTP proxy, we managed to discover this resource:

A sample query for San Bernardino county

http://casgemgis.water.ca.gov/ArcGIS/rest/services/CASGEM_DWR_BASE_LAYERS/MapServer/0/query?spatialRel=esriSpatialRelIntersects&returnGeometry=false&where=%20UPPER%28COUNTY%29%20%3D%20%27SAN%20BERNARDINO%27%20&f=json&outFields=%2A&outSR=102100

Or, to query the API using a GUI:

CASGEM Walkthrough

As mentioned in our last post, unfortunately, CASGEM's bulk data download option does not provide latitude/longitude columns. Fortunately, the unique CASGEM_ID column is composed of the lat long coordinates mashed together. We will use a data transformation tool to generate separate lat long columns from the CASGEM_ID column. Data cleanup operations are not an unusual task when dealing with large datasets. We will use OpenRefine to make transformations and to structure our data for mapping and querying purposes.

OpenRefine (previously GoogleRefine), is a fast, powerful, and free data cleanup tool. There are many merits of OpenRefine, but I specifically enjoy the undo cue and being able to export your transformations to JSON. And did I say it's fast.

With your .CSV file open, and after you cleanup the CASGEM_ID column name (mine imported with symbols in the column header), navigate to Edit cells > Transform...

CASGEM Walkthrough

Using GREL (Google Refine Expression Language), or OREL by the time your read this, we are going to split cells in the "CASGEM ID" column (while preserving the ID column) by targeting the "N" character as follows:

value.split("N")

CASGEM Walkthrough

Now that we have two separate columns which are essentially the latitude and longitude derived from CASGEM ID, we need to format them appropriately. Clearly, "latitude" is missing a decimal point, and longitude is missing a "-" sign and its decimal point. Furthermore, we need to get rid of the "W001" at the end of the ID number. Click the dropdown in the newly created "lat" column and select Edit Cells>Transform. Furthermore, add try this GREL snippet as follows:

CASGEM Walkthrough

value.slice(0,2) + "." + value.slice(2,6)

CASGEM Walkthrough

Next, we need to format the longitude column "long" appropriately. A decimal point and minus sign are added:

"-" + value.slice(0,3) + "." + value.slice(3,7)

CASGEM Walkthrough

Ok, now we have our lat and long in separate columns. Our time series data is ready to map. OpenRefine is very handy when needing to transform data and perform cleaning over large datasets. Here, we did some very simple transformations. For other applications and work-flows check out this OpenRefine tutorial series:

Google Refine 2.0 - Introduction (1 of 3) (video version 2)

As mentioned above, DWR rate-limits its servers to 1000 records per query. If you are here to get data and spare yourself the grief, we will be publishing a bulk download option and data API shortly. Check back here, or keep an eye on announcements via our twitter: @CAWaterAtlas, NCWA blog, or newsletter.

Otherwise, for those that are looking to make this data useful, and need to customize the way the data is presented, I hope this post is helpful and clear. Please submit your questions and comments below. Look forward to your feedback.

Pages

Subscribe to RSS - General