Making a Map From Open Data Using a Spreadsheet

There's been an explosion of open data made available by all levels government, but much of it is still in a pretty raw form and can be hard to manipulate and visualize. However, with the data import functions in Google Sheets and the mapping functions in Geosheets it's possible to quickly produce insightful visualizations from raw open data feeds.

We're going to map all of the polling places in New York City, as made available on NYC's Open Data portal here.

1. Create a new spreadsheet with Geosheets enabled.

Either install the add-on or make a copy of the demo spreadsheet which has Geosheets pre-installed in that sheet.

2. Import the open data file using the IMPORTDATA function.

In cell E3 so that you have a little room to work, write this formula:

=IMPORTDATA("https://data.cityofnewyork.us/api/views/utqd-4534/rows.csv?accessType=DOWNLOAD")

This is a built-in Google Sheets function that takes the url of a CSV or TSV file and imports that data into your sheet. We got this url by clicking on the Export button on the open data page mentioned above, finding the "CSV" link, and copying the link url.

opendata-csv-screenshot-export

You should see a large table of data populate in a second or two. One of the columns is called "LOCATION" which contains the latitude and longitude of each polling place, which we'll use to plot them on the map.

3. (Optional) Add columns for the size and appearance of the map markers.

In cells A3 and B3, add column headers called "Type" and "Radius". In cell A4 type the value "circle" and then paste it all the way down column A. Similarly, in cell B4 type the value "3" and paste it all the way down column B. This will tell Geosheets to render each point as a circle of radius 3 pixels, rather than the default teardrop marker. Because we have a large number of points, this will make the resulting map easier to understand.

In cell C3 add a column header called "Color". We're going to assign a color to each borough, where a unique id for each borough can be found in the column labeled "BOROUGH", which should be in column P. In cell C4 write the formula =GEO_ASSIGN_COLORS(P4:P1203). This will assign a unique color for each value found in column P, so in one step this color-codes every point.

In cell D3 add a column header called "Label". Any text in this column will show up when your mouse hovers over a placemark on the map. In cell D4 write the formula =E4, then copy cell D4 and paste it all the way down column D. In other words, we're going to copy the values from the "SITE_NAME" column into a new column called "Label", since this feels like the appropriate text to show when you hover over a polling place on the map. If you'd like, you can also just rename the "SITE_NAME" column instead.

4. Draw the map using the GEO_MAP function.

In cell A1, write the formula =GEO_MAP(A3:P1203, "nyc-polling-places", "LOCATION").

Here we're calling Geosheets to draw a map from the data found in the range A3:P1203, which should be all the data in your spreadsheet. We need to give the map a name in the second argument, so we've chosen "nyc-polling-places". Finally, since our latitude and longitude values are not in the first column, we need to tell Geosheets where to find them, so we pass "LOCATION" as the third argument, since that's the header for the column containing our coordinates.

In a moment cell A1 should populate with the url of your newly-created map. If you install the Geosheets add-on, you'll see a preview of the map in a sidebar. In the meantime, if you visit the map page you should see a gear icon that lets you change settings about the map, including its name, description, and the base map layer.

The finished map should look something like this, if you change the base layer:

The full-page map can be found here: https://www.geosheets.com/map/s:DlAEBdJn/nyc-polling-places

The finished spreadsheet should look like this:

Leave a Reply

Your email address will not be published. Required fields are marked *