Finding and Mapping Distances Between Cities

San Francisco recently saw direct service begin to Singapore on United and soon on Singapore Airlines, bringing back service from North America to Singapore and creating one of the world's longest flights. We wanted to see how this route measures up to others from SFO in terms of distance, so we pulled the list of SFO destinations, imported them into Google Sheets and calculated the distances, then mapped them. Here's how to do it for yourself:

1. Grab a list of destinations and paste them into a spreadsheet.

You can find the list of international SFO destinations from the flysfo.com site, or simply click below to see them inline. Paste them into the first column of a Google Spreadsheet with Geosheets enabled. You can install the add-on or click here to make a copy of our demo template. Column A should have a list of cities, and you should add a header row at the top.

See SFO destinations ▼

2. Compute the distance in a new column.

Paste this formula in the the second column, in cell B2 (presuming you have a header row):

=GEO_DISTANCE({"San Francisco", A2}, "miles")

Then copy the formula to all cells in column B.

The GEO_DISTANCE formula in Geosheets calculates the straight-line distance between two or more distances, in this case between San Francisco and the city in each referenced cell. We gave it an optional second argument to compute the distance in miles instead of the default unit of meters.

3. Assign colors to each destination based on the distance.

Add a new column to your sheet (column C) called "Color". In cell C2 paste in this formula:

=GEO_ASSIGN_COLOR_INTENSITIES(B2:B47)

This function looks at the distances you computed in column B and assigns a color to each row. The longest distances will have the brightest colors and the shortest ones will be darkest.

4. Map all the destinations using the GEO_MAP function.

In an empty cell like D1, add this function call to create a map:

=GEO_MAP(A1:C47, "sfo-destinations")

This will create a map from your 3 columns of data. The function assumes the locations you want to map are in the first column, unless you tell it otherwise. It colors each marker on the map according to the column called "Color". And the "Distance" data in column B is simply rendered when you click on each marker, since it's a custom attribute. We passed the value "sfo-destinations" to the GEO_MAP function to serve as a label for the map, so that we can later change the map data but keep the same map url.

Your map formula cell will populate in just a moment with the url of your finished map.

5. (Optional) Get fancy and add lines, labels, and other features to your map.

Airport route maps look best when there are geodesic (great circle) lines between each city. That's easy with Geosheets. First you just need to define a line feature. To render a line, you just need a cell with two or more location names separated by a "|" symbol, like "San Francisco | Tokyo". Then you add a new column called "Type" and populate each cell in that column with the value "line" for straight lines or "geodesic" for great circle lines. You can write a formula like =JOIN(" | ", "San Francisco", A2) that populates a cell with the value needed for a line between San Francisco and another city, and paste it down column A for all cities already listed. Then update your GEO_MAP function call to include your extra rows and you'll have lines on the map.

Similarly, you can add an extra column called "Label" and put some text in it. This text will be rendered when you hover over that feature on the map.

Putting it all together, you should get a map like below:

Our finished spreadsheet for this map is below. You can view it and make a copy here.

 

Leave a Reply

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