This is a mapping project, which maps the 170 Cisterns of San Francisco.

This is my first mapping project and I thought I'd share my discoveries, techniques and open source tools for all you map-geeks out there.

This Instructable covers:
• Data-gathering on the web
• Writing Python code to convert the data to a GeoJSON format
• Using Stamen map tiles and Leaflet — a Javascript library to build a webpage with a map

The final result is this online map of the San Francisco Cisterns.

The map is still an early version and I'll be fixing up some of the data points and adding it to a website, but it more or less works!

This Instructable is one of many short investigations, all part of a larger project, called Water Works, which is a data-visualization and mapping of the San Francisco water infrastructure: the pipes, hydrants and treatment plants that keep this city alive.

This work is from a Creative Code Fellowship, supported by Gray Area, Stamen Design and Autodesk.

Step 1: A Brief History Lesson

I wrote about this history in my previous Instructable about 3D modeling the Cisterns of San Francisco, so for those of you who read that Instructable, this is a repeat lesson.

After your read about them, you will see these brick circles everywhere (in San Francisco). You can even find them on Street View.

It turns out that underneath each circle is an underground cistern. There are 170 or so of them spread throughout the city. They’re part of the AWSS (Auxiliary Water Supply System) of San Francisco, a water system that exists entirely for emergency use and is separate from the potable drinking water supply and the sewer system.

In the 1850s, after a series of Great Fires in San Francisco tore through the city, 23 cisterns* were built. These smaller cisterns were all in the city proper, at that time between Telegraph Hill and Rincon Hill. They weren't connected to any other pipes and the fire department intended to use them in case the water mains were broken, as a backup water supply.

They languished for decades. Many people thought they should be removed, especially after incidents like the 1868 Cistern Gas Explosion.

However, after the 1906 Earthquake, fires once again decimated the city. Many water mains broke and the neglected cisterns helped save portions of the city. Afterward, the city passed a $5,200,000 bond and begin building the AWSS in 1908. This included the construction of many new cisterns and the rehabilitation of other, neglected ones. Most of the new cisterns could hold 75,000 gallons of water. The largest one is underneath the Civic Center and has a capacity of 243,000 gallons.

The original ones, presumably rebuilt, hold much less, anywhere from 15,000 to 50,000 gallons.

* from the various reports I've read, this number varies.

Step 2: Track Down the Data

I spent a long time searching through the web for the locations of all of the cisterns. There were a few people that had written about the cisterns such as Atlas Obscura, Untapped Cities and Burrito Justice. However, none the blog posts had their locations.

I got lost in web-hell searching through various San Francisco water agencies. I got frustrated. Eventually I poured myself some bourbon. As my brain unwound, I honed in on a phrase on some site somewhere that said that the San Francisco Fire Department was the agency that filled up the cisterns.

I renewed my search efforts looking for SFFD and cisterns and eventually found this fire supplies manual. At the back of the PDF was a listing of all of the intersections in San Francisco (169 of them) where the cisterns are located.

Bingo!

Step 3: Copy and Paste Into a Spreadsheet

Love it or hate it, data-wrangling often involves CSV files — a text file of comma-separate values. For small datasets like this a the spreadsheet application of your choice — Excel, Numbers, OpenOffice, Googles, etc. will be where you enter the values.

I copied the intersections and the volume of each cistern.

Copy. Paste. Copy. Paste. Copy. Paste. Copy. Paste.

Tedious.

Eventually I finished and then exported the file as a .csv, which other programs can read.

Step 4: Get Lat/long From Google Maps API

Using the Google Maps API, from an intersection, you can get the exact latitude and longitude.

For example, check out this link through the Google Maps API.

It looks like a bunch of gobbledy-gook. We'll get to this later.

The first step was to figure out how take my intersection name such as "16th & Bryant" turn into "16th+and+Bryant,+san+francisco"

The second step involves parsing out the Google API results.

I wrote a python script which takes this information and converts it to a CSV.

Also tedious, but I have source code to share! Here is the GitHub repo:

Note: You'll also have to get your own Google API Keys for repeated queries.

(note the last column is elevation, which I use in a 3D-data visualization, stay tuned...)

Step 5: Add Headers

What we're going to do is to convert our CSV into a GeoJSON format, which is what mapping applications such as Leaflet require.

In the spreadsheet application of your choice make the 1st row the line for your headers. This will describe your data in each column.

The rest of the columns are custom: I added Description (the intersection name), Volume and Elevation. These will appear in our online map later on.

The important ones are the lat and lng names. Keep these exactly how you see them.

Export this as a CSV.

Step 6: Convert CSV to GeoJSON Using Ogr2Ogr

We need to use the GeoJSON format — this is a mapping format used by Leaflet and other types of applications.

The Ogr2Ogr web interface is the easiest way to do these, at least for small datasets.

Choose your CSV file.

Press the Convert To GeoJSON button and you'll see a whole bunch of text, which we will cover in the next step.

Step 7: Save the GeoJSON Text Into a File

This mess of text is GeoJSON-formatted and is a specific type of JSON (JavaScript Object Notation) format. JSON is a key-value data format common for web applications.

Click in the text box, select all and save to a text file using a format-free text editor such as TextWrangler, Sublime or Atom.

The text file should end in .json. For the purpose of this Instructable, we'll call this cisterns.json.

To summarize: what we just wrangled was unformatted web data into a GeoJSON-formatted text file, which mapping applications using Javascript love.

Step 8: Design Icon for Leaflet

Based on my previous 3D Modeling Cisterns Instructable, where I made a 3D model of a San Francisco Cistern, I worked with Heather Grates at Stamen Design, who came up with this icon for the map.

Previous versions were rust-colored, which we thought looked like a mud hut and also had a birds-eye view, which looked like a knob.

Step 9: Make a Basic Leaflet Map

I hammered out some Javascript for this. I fess up that the code is bad, but hey, it's my first foray into any significant web-programming. Here is the GitHub repo.

I won't go too much into the how-tos of Leaflet, which is covered in this Quick Start guide.

There are a couple of things that might be helpful.

(1) I used Stamen Map tiles, which give you some very pretty options and it's all open source!

(2) Remember when we added the Description and Volume properties, well now we can have this information in the popup menus.

Step 10: Done!

This was a fun project. Normally I like to build physical things, but having a web presence that accompanies your physical sculptures is helpful for reaching a larger audience.

And there will be a physical sculpture in the near future.

I hope this was helpful!
Scott Kildall
For more on the Water Works project, you can find me here:
@kildall or www.kildall.com/blog

p.s. I'm aware there are some incorrect data points on the map. If you notice anything specific, please contact me privately and I'll fix them up!