Sometimes, data is available, but not in a way that allows for quick understanding – and we want to understand fast. Interactive maps and charts can make the things easy to understand – they transform complexity so that we can understand fast. UnderstandFast.com showcases different types of experiments with tools to help us understand fast.
Largest Cities in the United States – by Population, by Area, and by Population Density (population per square mile)
For the technically curious:
Background: I was excited to set up an Esri ArcGIS account, which includes ArcGIS Online, ArcGIS Pro, and the legacy ArcGIS Desktop. Although I’ve used these tools for well over a decade in my previous work, I now had my own account, and could learn and experiment without the constraints of competing projects and priorities.
Goal: Start by creating a simple interactive map showing the largest cities in the United States. I wanted to show the cities with a larger size (or different color) based on the population of the city (larger city = larger symbol), and/or the density of the city (more people living closer together = larger symbol). The goal was to explore out-of-the box options for creating the map, without coding. Esri (ArcGIS Online), Google My Maps, and Microsoft Power BI were the primary options that I explored.
Source: Wikipedia has a page that already lists every US city with a population over 100,000 (2019 estimates).
Side note: Esri also offers a similar data source, but for purposes of this experiment, I will continue with the Wikipedia data.
Visualization Option – OpenStreetMap using existing link
The Wikipedia page conveniently includes links to map the largest US cities using OpenStreetMap. This was super-convenient, and had some built-in interactivity (e.g., links to each city’s Wikipedia page). However, the tool itself did not allow for symbolization based on size, so this did not meet my needs.
Visualization Option – Wikipedia’s GPX to ArcGIS Online
The Wikipedia page also conveniently includes a link to export the largest cities in GPX format, which imports easily into ArcGIS Online. However, the data that was included in this format did not include the information that I wanted to map (population size, population density), so this did not meet my needs.
Visualization Option – Wikipedia’s KML to ArcGIS Online
The Wikipedia page also conveniently includes a link to export the largest cities in KML format. This is supposed to import into ArcGIS Online. However, this particular KML would not import into ArcGIS Online, so this did not meet my needs.
What to do? This could be an opportunity for data transformation. Meaning, I could take the data from Wikipedia and “clean it up” so that is would be easy to use.
For example, transform the data for the land area so that it is a number (301.5) instead of a number and text (“301.5 sq mi”). That way, different symbols can be used. For example, a bigger city can have a bigger circle or a different color than a smaller city.
But, this also presented the opportunity to experiment with creating an interactive map in non-Esri platforms – platforms other than ArcGIS Online. Platforms like Google My Maps.
Visualization Option – Wikipedia’s KML to Google Maps
Unfortunately, using the KML link on the Wikipedia page and importing it into Google Maps, I found that this data also is missing the information that I needed. As with the Wikipedia page’s pre-made GPX format, the pre-made KML data from the page was basically just City name and location. It did not include the other columns of data, such as population, area, or population density, so this did not meet my needs.
So, none of the pre-made data formats on the Wikipedia page included the data that I wanted to use.
Visualization Option – Microsoft Excel’s 3D Maps tool
Microsoft Excel could quickly create a map a copy-and-paste of the Wikipedia data, using Excel’s 3D Maps tool (formerly called Power Maps).
This tool is found in the Excel menu: Insert -> Tours -> 3D Maps.
I quickly created the map and symbolized it so that the larger cities were taller. Not exactly what I was looking for, but a good start.
But there doesn’t seem to be a way to publish/share the resulting map, other than sharing it as part of the Excel file. That's not what I'm looking for - I want to share the map on the web. There’s no way to share a map product, for someone to understand fast without using Excel. Odd, and it means that this did not meet my needs.
Visualization / Additional Data Option – Leverage Excel’s Geography “data type” to get more data
I experimented with Excel’s Geography “data type”:
Data -> Data Types -> Geography
By making a field a Geography data type, it allows you to you bring in additional data about the location (Microsoft retrieves it from its Bing service).
Excel had no problem converting the copied-from-Wikipedia field with the city name to a Geography field, and within Excel, there were popups within the cell to show additional information about the city.
However, I was mostly unsuccessful at using this Geography field to create new data fields (e.g., to create a new column which automatically retrieves Bing’s paragraph-long description of the city).
Since I already had the data that I wanted, I did not troubleshoot this further – that’s something to explore another time.
Visualization Option – Google My Maps from data in Microsoft Excel
Unfortunately, Google My Maps could not recognize the “Geography” field type from Excel. So that was a non-starter. But it was easily fixed: I created an additional Excel column with the city name as a General data type and Google could use this when I re-imported the updated file.
Google My Maps was quick to use. It could directly import from the Excel file.
Why Microsoft Excel instead of Google Sheets? Excel’s “format as a table” function is really helpful, and Google Sheets lacks this tool.
With Google My Maps, I could quickly create a map with a set of points, that could be clicked on for more information.
But there were many limitations with Google My Maps: Columns (field headings) cannot be relabeled, map points cannot be different sizes based on population size, and more. Although Google My Maps allows quick map creation, it did not sufficiently meet my needs. So, it looked like Esri’s ArcGIS Online was the answer.
The best visualization option is ArcGIS Online
Unfortunately, ArcGIS Online does not directly import Excel files. So, the choices were to export the Excel file as CSV, or do some work in ArcGIS Pro and then bring that to ArcGIS Online. That’s the approach I planned to use - but it was not necessary.
ArcGIS Maps for Office: Solid visualization tool, and excellent bridge from Excel to ArcGIS Online
Once ArcGIS Maps for Office is downloaded and installed, there is a new menu item in Excel (“ArcGIS Maps”). From there, you must sign in (which Esri made me do twice).
Interestingly, as with Google Maps, Esri’s ArcGIS Maps for Office also does not understand fields that are set as Excel's Geography data type; perhaps the Geography data type is understood by Microsoft but not by others. No matter, I just instead used the field with the city name, where the data type is not set to Geography.
ArcGIS Maps for Office allows:
a quick way to create a map with larger circles for larger cities
the choice of which data to show when someone clicks on a city
the label for this popup data to be different from the column name in Excel
sharing the map to ArcGIS Online
So, ArcGIS Maps for Office provides a lot of easy-to-use tools to create a more refined and powerful map than Google My Map, without GIS knowledge (although it does require an ArcGIS Online license to share the map).
My quick take is that ArcGIS Maps for Office provides a lot of functionality for a little amount of work. The map that I created using it made it much easier to understand fast than the Google My Map. And once the map was shared in ArcGIS Online, I could further improve it.
Improving the map with ArcGIS Online and Esri Story Maps: Powerful interactive maps, with no coding
Once the map was in ArcGIS Online, I could further improve it, to make it easier to understand fast. For example:
Configure the popup
Concatenate data fields to create a better pop-up title
Re-order the data fields
Re-label the data fields
Have multiple symbolization options (population size, area, density)
Choose "breaks" to separate the data in ways that are more intuitive (e.g., a “clean” break at 150,000 people, instead of a break that is more mathematically meaningful but is complicated and difficult conceptually to understand)
From there, Esri's story map templates allowed the maps to be grouped together and shared, without coding.
Another Good Visualization Option, but with a catch: ArcGIS Maps for Power BI
I've been a fan of Microsoft Power BI. It allows creation of interactive dashboards that make it possible to understand fast: With one tap, change a year, or change a category of data, and immediately see changes in charts, lists, and more. In my prior organization, we did not have access to ArcGIS Maps for Power BI, so it would be interesting to explore it as part of Understand Fast.
Lessons learned with ArcGIS Maps for Power BI:
I did not have the correct type of license to access all of the power of ArcGIS Maps for Power BI, but it turns out that it's still powerful without signing into ArcGIS Online.
Noting, as with Google Maps and Esri’s ArcGIS Maps for Office, ArcGIS Maps for Power BI also does not understand fields that are set as Excel's Geography data type - it appears that data type is only usable by Microsoft Excel. Microsoft Power BI is taking Microsoft Excel analytical tools forward several levels; it's a little odd that the Power BI team did not work with a feature that the Excel team created.
ArcGIS Maps for Power BI requires separate fields for latitude and longitude, so I used Power BI to transform the data that had been pasted from Wikipedia (creating two new fields from the one field).
Add Column -> Column From Examples
There were still issues with latitude/longitude, so I just used ArcGIS Maps for Power BI’s location field. This did require transformation, because some city names are used in more than one place throughout the globe; using only the city name in the Location field did not always result in the correct location within the world. But it was easy enough to use Power BI to concatenate city and state, to create a field that ArcGIS Maps for Power BI had no trouble using.
Next up, I wanted to let visitors tap on a city name and understand fast, by immediately seeing the city's size compared to the other 316 large cities, the city's area, compared the other 316 large cities, and the city's density compared to the other large cities. With the #1 city being the largest, the built-in Power BI gauge visual did not facilitate quick understanding. I needed something similar that showed low numbers (#1 in population rank) as the most significant. Luckily, the Power BI community had already though of this, and by using "get more visuals" and choosing the Tachometer visual, I was able to have #1 show the most, and #317 the last.
The "slicers" (filters) that come with Power BI are good, but I ended up using another visual from the community, the Tile Search Slicer. It allows for faster selection of different options of a choice list.
But here's the catch: When you publish from Power BI, you cannot share the map from ArcGIS Maps for Power BI. Which is odd. This, it turns out, is documented on Microsoft’s website. [Update: Esri improved ArcGIS in Power BI in February 2021 (the US Cities work described here was published in December 2020). Check out their blog post on changes that make publishing ArcGIS for Power BI content less restricted.]
The Power BI visualization allows one-tap function to see the relative ranking of a city, across three charts: population size ranking, geographical area ranking, and density ranking. But the maps from ArcGIS Maps for Power BI do not show. So, I ended up using the Power BI page without the map.
Other Options
There are, of course, many other options for interactive maps and dashboards. ArcGIS Insights and ArcGIS Dashboards may also have been options, but were not included as part of this exploration. One or both may have been able to yield results similar to Power BI (and would be able to include an ArcGIS-based map, unlike Power BI). Mapbox and many other options are available.
However, this is where the exploration ends (at least for now).
On to more datasets!