Video: Google Fusion Tables Tutorial of California's Water Data

Video: Google Fusion Tables Tutorial of California’s Water Data

Watch this tutorial to learn how to use Circle of Blue’s Google Fusion Tables maps that show changes in California’s water use over the last two decades and how these trends relate to population demographics and water contamination from arsenic.

By Aubrey Ann Parker
Circle of Blue
May 6, 2014

Choke Point: Index is an on-the-ground report that finds fresh water in U.S. farming regions — just as in other major food-producing nations — is in precarious condition.

J. Carl Ganter: Hi, this this J. Carl Ganter, managing director of Circle of Blue. For almost a century, water providers and food producers in California have taken on one of history’s most ambitious projects. China was built by hydrologists, and Roman aqueducts watered an empire. They focused on one thing – moving water where it won’t go on its own.

Read the Story: California’s Lingering Drought and Pollution Defy Solutions

Fusion Tables: Central Valley Arsenic Map

Fusion Tables: California Water Use Per County

Video: Watch the Qlikview Tutorial for California Reservoirs

Qlikview: Live and Historical Water Reservoir Volumes in California

California’s 80-year-old system of canals – the State Water Project and the federally financed Central Valley Water Project — stitch together an audacious hydrological network: 1,200 miles of aqueducts, canals, and pipelines. Together, both systems can deliver about 11 million acre feet – 3.6 trillion gallons – of water for agricultural, municipal, and industrial use. This water all comes from the snowfields and streams of the Sierra Nevada Mountains. But snowpack levels are among the lowest they’ve been since record keeping began. And the state is in the midst of its worst drought in history. A new report from Utah State University says that climate change is shifting California’s water supplies. This is amplifying older, persistent challenges like groundwater pollution.

The need to better understand the state’s intersecting trends in water use have never been more urgent. With Choke Point: Index, our teams at Circle of Blue set out to advance this critical story using the best data, images, and on-the-ground reporting available. Our photographers captured the faces of the people most affected. Our data miners pulled mountains of data from federal, state, and local non-profit sources. And our writers put these trends into critical context.

Since 2009, Circle of Blue has worked with Google Research, Qlikview, and others to find new ways to assemble and present key data. Aubrey Ann Parker, our lead data analyst, will take you on a tour of California’s water use data from the past 20 years. You’ll see how it relates to population demographics and water contamination in the eight counties of the Central Valley. It’s one of the most productive — and at risk — agricultural regions in the nation.

Aubrey Ann Parker: I’m Aubrey Ann Parker, chief data analyst for Circle of Blue. Our researchers pulled water withdrawal data from the U.S. Geological Survey. They publish data every five years. They have data going back to the 1950s, but they only started collecting it at a county level in 1985. This is the 1985 total withdrawal map for the state of California, down to the county level. We can click through the toggles at the top and it will show us how that intensity changes every five years, up to 2005. That is the last year that is available through the USGS. The 2010 data won’t be out until later this fall. These eight counties that are outlined in light blue rely heavily on supplies from the San Joaquin Delta. This region is called the Central Valley, and it’s home to half of the state’s irrigated land. These graphs over here on the right show statistics for the whole state. This bar chart in the middle shows how total water withdrawals have changed from 1985 to 2005; it’s broken up into three major sections — agriculture is in dark blue; industrial use is in this medium blue; and domestic supplies are in light blue. If you click on it, it opens in a separate tab, and you can see more detail. We’ll go back to the map.

Note that in 2005, more than 92 percent of Californians got their water from a public supply, compared with 86 percent nationally. But as we can see here in the bar chart, water delivered to the home made up less than 10 percent of the state’s total water withdrawals. So yes, it’s important to take short showers, but most water supplies go toward farming, as is the case globally. Agriculture is California’s biggest water user, using half of the state’s total water withdrawals and three-quarters of its freshwater withdrawals.

When you look at huge data sets, you start to see glaring holes. In 2000, the USGS didn’t collect numbers on domestic public supplies, or water that was coming to people’s homes from a public utility. That’s the green, which shows the difference between the total water withdrawals and agricultural withdrawals, the two fields that they did collect numbers on. Despite that green hole, we still see the general trend. Total water use has gone down — ag withdrawals especially have dropped by about 20 percent, while domestic have actually gone up by 30 percent. Still, total withdrawals have gone down by about 4 billion gallons per day over the past two decades.

About one-third of the total withdrawals occurred in these eight counties over here in the Central Valley, which we can see in this pie chart here. This 30 percent in light blue corresponds to the water withdrawals in these eight counties. Only 10 percent of the state’s population live in the Central Valley though, which we can see in this pie chart up here of population — again the light blue is for the Central Valley, 10 percent.

If we go back to the map and we click on a county, we get a pop up that shows us slightly more in depth analysis of what’s going on in that county. We still have the bar chart breakdown of total water withdrawals into agriculture, industry, and domestic.

This tutorial was created by J. Carl Ganter and Aubrey Ann Parker, Circle of Blue’s chief data analyst. Photo images provided by Matt Black for Circle of Blue (California’s Central Valley in black & white) and Brent Stirton/Getty Images Reportage for Circle of Blue (aerial color images). The map was created by Aubrey Ann Parker and Jordan B. Bates, Circle of Blue’s web producer. Contributors included Holly Jo Sparks and Brett Walton of Circle of Blue, with assistance from Sreeram Balakrishnan of Google Fusion Tables; Sheng Long and Alysha Chan, graduate students with the Columbia Water Center in New York City; and Andrew Girrell, a freelance data analyst who is based in Traverse City, Michigan. Columbia interns were overseen by Upmanu Lall and Margo Weiss. Reach Circle of Blue’s data team at jordan@99.198.125.162/~circl731 and aubrey@99.198.125.162/~circl731.

Choke Point: Index is produced in collaboration with Google Research, Qlikview, and the Columbia Water Center, with financial support from the Rockefeller Foundation.