Mapping a Wikipedia Page in Seconds

Wikipedia has an astounding wealth of information including location data, and much of it is semi-structured due to the use of tables and information cards. It's possible to take advantage of that structure to quickly extract location data and attributes and then use Geosheets to create a map.

The secret sauce here is the IMPORTHTML function in Google Sheets, which populates a spreadsheet in a single call, combined with the GEO_MAP function in Geosheets, which takes a spreadsheet containing location data and creates a map. In this demo, we're going to make a map of all US capital cities, as listed in this Wikipedia article:

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 page's data table into your spreadsheet using the IMPORTHTML function.

Do this in cell C3 to give yourself some room to work. Write this formula:

=IMPORTHTML("", "table", 1).

The first argument is self explanatory, it's the url from which you want to import data. The second argument is one of either "table" or "list" depending on the type of element you're trying to import. The third argument is the position on the page of the desired element, i.e. is this the first table on the page ("1"), the second table on the page ("2"), and so on. For a Wikipedia page with one big table, this value is typically 2 but could be 1 or 3; just try them all starting at one until you get the right result. In a second or two, you should see the table data magically populate in your spreadsheet.

3. Create a column with the city and state name.

Write this formula in cell A4: =JOIN(" ", F4, C4). It populates that cell with the values of cells F4 and C4 joined by a space. Now copy that formula all the way down column A.

This will be the column that Geosheets uses to determine a location to plot on the map. In most cases, if you have a major city name, you can use that column as-is for mapping and Geosheets will figure it out. In the case of capitals, some of the city names have ambiguity, like "Springfield" or "Columbia". So we created a column with a combined city and state like "Springfield Illinois", by simply joining the values in the "Capital" and "State/district" columns that were populated by Wikipedia.

4. Draw a map using this table and the GEO_MAP function.

In cell A1 or somewhere easily accessible to you, write this formula to create the map: =GEO_MAP(A3:L53, "capitals").

The first argument is the entire range of the table, all the data present in the spreadsheet. Geosheets looks at the header cell of each column to determine what that column represents. It assumes that the location names, addresses, or coordinates to be mapped are in the first column that you pass in. If your locations are in a different column, you should pass the header name for that column as a third argument; we've omitted a third argument here because that's not necessary. The second argument is just a label for your map, so it can be any string, but here we've just chosen "capitals". The label is what keeps the resulting map url stable if you change the underlying data in the future.

Wait a few seconds and cell A1 should populate with a url pointing to your new map, which should look like the one below. If you install the Geosheets add-on, you'll see a preview of the map in a sidebar right within your spreadsheet. You can change any data within range you passed to the GEO_MAP function and the map will automatically update.

5. (Optional) Color the markers on the map by adding a column called "Color".

Here you can put in any color name or hex color code, or you can use a helper function to automatically color-code the map. Make column B your Color column. In cell B4 write the formula =GEO_ASSIGN_COLOR_INTENSITIES(K4:K54). This will look at the population values in column K and assign colors based on how big that value is for each row. Cities with a higher population will have brighter colors and cities with smaller populations will have darker ones.

6. Use, share, or embed the map!

The map url generated by the GEO_MAP function is easily shareable with others. If you go to the map page and click the "Share/Embed" icon in the upper left corner, you'll also see an embed code you can use for your site or blog, and options to download the raw map data in another format.

View the full page map:

The finished spreadsheet should look like this:

Leave a Reply

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