googleearth

Mapping Phone Data in Four Easy Steps

Have you run into this problem: you have a list of phone numbers and associated values which would be best shown geographically to see patterns, but there isn’t a clear way to put the data on a map. Maybe you’d like to see a map of customer service calls by call duration or inbound sales by average order size.

I wanted to share how to MacGyver a solution with a piece of twine, bubble gum, Excel, and a free online map tool. To me, this is a nice testament to the simple but powerful data visualizations that can be accomplished without programming skills or expensive applications.

1. Pull out area codes

First I pulled the area codes from my list of phone numbers using the formula below. This simply checks if the phone number starts with 1, then grabs the appropriate three digits for the area code.

=VALUE(IF(LEFT(E7,1)="1",MID(E7,2,3),MID(E7,1,3)))

 

2. Convert area codes into states

For my purposes, mapping the phone numbers by state was sufficient. Ideally, we would map the phone numbers to precise latitude and longitude coordinates by doing a reverse lookup of addresses then using the Excel geocoding tool.

First I needed a lookup table that could link my list of area codes to states. I wasn’t able to track down a good data table, so I grabbed the data from All Area Codes and cleaned it up. Here is a lookup table of area codes by state.

An aside: I have a pet peeve with people who sell data that feels like it should be publicly available. You’ll run across these businesses when looking for basic information about ZIP codes, MSAs, or area codes. Here is an example of one of these parasitic businesses.

Zip code product

3. Create your summary data set

I used a pivot table to summarize metrics by state.

4. Create colorized map of the US

Our friend Ducky Sherwood has generously put together a online tool called Mapeteria that will generate a colorized overlay of US states. In Ducky’s words: "Want to make a choropleth thematic map (i.e. coloured based on your data) for Canadian provinces, U.S. states, or French départements?" This overlay can be viewed in either Google Maps or Google Earth.

Here’s where it gets a little tricky. You will need to provide Mapeteria with a URL to a properly structured CSV file. Posting a CSV file to a web server isn’t trivial if you aren’t running your own web site. I found one free service called FileDEN that did the job (other suggestions?). Beware all the advertising—and in all likelihood they immediately sold my e-mail address at registration. Nevertheless, you can upload a file here and it will give you a URL which can be used to create your map.

Here’s an example of the results:

State Map

GIS for the rest of us… or Another Excel-based Mapping Tool

A client recently asked us if we could help him find the five locations that have the most customers nearby. I dug into the annals of our blog to find two tools that together ("wonder twin powers activate... shape of mini-GIS solution") could solve this problem:

1. Juice geo-coding tool lets you get the precise latitude and longitude for a list of addresses and plot these locations in Google Earth.

2.ZIP code finder lets you enter a US address and returns zip codes within a certain number of miles of that address.

Led by our resident Excel guru David, we combined these features into an Excel tool that lets you answer common location-based questions such as:

  • How many libraries are within 10 miles of Worchester, MA?
  • Which cities have the most libraries within 10 miles? 20 miles?
  • Could I see the library locations on a map, please?

Here’s how it works:

1. Drop your location information into the "Geocode data" tab. We are using the Yahoo geocoding service.

2. Go to the results tab and fill in the table with your selected location addresses and distances. Press "Calculate."

Download the Excel file here: Juice Distance Tool

We had a couple other features in mind, but thought it would be better to get some reader reaction before we loaded it up.

This tool is released under the MIT license.

Google Earth/Google Maps Mashups

Yesterday, Google rolled out new mapping features for Google Earth and Google Maps. Many of these features are behind the scenes in the APIs, but there are great new capabilities that you will start to see. One thing I’m excited about is that KML—Google Earth’s format for building sophisticated map overlays—has come to Google Maps.

Google demoed this at their Geo Developer day yesterday using one of our Google Earth overlays that shows US census bureau data by county mapped as a heatmap. It looks like this.

Counties are displayed in a list on the left. When you click on a county, you get a nice popup showing statistics for that county.

There are a few limitations. Large KML files don’t load in Google Maps, medium-sized files load very slowly—it seems Google is parsing the KML using Javascript.

The mapping toolkits provided by Google Maps, Google Earth, and Yahoo Maps beta are well on their way to becoming important business tools once developers figure out how to wire in your enterprise data.

Without further ado, here are some US census data maps for you to explore in Google Maps.

Population Density

Lighter is higher population density (white is 800+ people per square mile), Dark is lower population density (black is 2 or fewer people per square mile)

AlabamaAlaskaArizonaArkansasCaliforniaColoradoConnecticutDelawareDistrict of ColumbiaFloridaGeorgiaHawaiiIdahoIllinoisIndianaIowaKansasKentuckyLouisianaMaineMarylandMassachusettsMichiganMinnesotaMississippiMissouriMontanaNebraskaNevadaNew HampshireNew JerseyNew MexicoNew YorkNorth CarolinaNorth DakotaOhioOklahomaOregonPennsylvaniaRhode IslandSouth CarolinaSouth DakotaTennesseeTexasUtahVermontVirginiaWashingtonWest VirginiaWisconsinWyoming

Median Age

Lighter is older median age (white is 46.0 years median age), Dark is younger median age (black is 29.0 years median age)

AlabamaAlaskaArizonaArkansasCaliforniaColoradoConnecticutDelawareDistrict of ColumbiaFloridaGeorgiaHawaiiIdahoIllinoisIndianaIowaKansasKentuckyLouisianaMaineMarylandMassachusettsMichiganMinnesotaMississippiMissouriMontanaNebraskaNevadaNew HampshireNew JerseyNew MexicoNew YorkNorth CarolinaNorth DakotaOhioOklahomaOregonPennsylvaniaRhode IslandSouth CarolinaSouth DakotaTennesseeTexasUtahVermontVirginiaWashingtonWest VirginiaWisconsinWyoming

Male/Female Ratio

Lighter means more men than women (white is 55% men), Dark means more women than men (black is 45% men)

AlabamaAlaskaArizonaArkansasCaliforniaColoradoConnecticutDelawareDistrict of ColumbiaFloridaGeorgiaHawaiiIdahoIllinoisIndianaIowaKansasKentuckyLouisianaMaineMarylandMassachusettsMichiganMinnesotaMississippiMissouriMontanaNebraskaNevadaNew HampshireNew JerseyNew MexicoNew YorkNorth CarolinaNorth DakotaOhioOklahomaOregonPennsylvaniaRhode IslandSouth CarolinaSouth DakotaTennesseeTexasUtahVermontVirginiaWashingtonWest VirginiaWisconsinWyoming

Google Earth Enhancements: What the birdie brought

A little birdie told me that the Juice Analytics census data heatmaps were used at Google’s Developer Day to show how Google Maps can now load Google Earth KML files. Very cool.

Google Earth KML files now have two important user interface features that I’m excited to try out. First up is progressive display of data. This means a KML file can show high level summary info when when a user is high above the earth and seamlessly show more detail as the user zooms in. This was only possible through network links in the current version of Google Earth and this will feel a lot more polished to users. The other important UI feature is folders can now support radio buttons (where only one thing can be selected at a time). The big deal here is it allows a user to explore points organized into multiple dimensions where you can only view a single dimension at a time. For instance, you might want to view your customers grouped by sales volume, types of products purchased, or industry. Choose which of these groupings you want to see and the others will be hidden.

Finally, viewing KML files in Google Maps is a potential home run. This increases the sophistication of what Google Maps can display and simplifies rollout of geographic information to an organization. Bravo, GE folks.

Restructuring data in Excel

It’s always good to see how other people do things. In this screencast, you can look over my shoulder as I work on a common data problem in Excel. In this case, I have a pile of addresses that are stacked vertically. I need to extract the address, city, state, and zip from each of these addresses into a contiguous block.

One the data is transformed, I geocode it and map it, tres easy with our Excel Geocoding Tool.

Unlike most previous screencasts, this is on a separate page as I needed more elbow room to show the transformation process.

Note: Audio quality is bad, that is to say, my keystrokes sound like random gunfire. We’ve never experienced this before, but will get it fixed in the future.

Google Earth Hacks: Floating the Navigation Panel

The Google Earth User Interface is controlled by a mysterious cabal directed by the Vatican. Whoops, check that - Da Vinci Code marketing cross-talk. The Google Earth User Interface is really controlled by a simple, easy-to-read, easy-to-modify XML file. This means it’s easy to show, hide, or modify elements of the user interface. Here’s an example.

Google Earth with floating navigation panel

Observe the floating navigation panel with more room for the Earth in Google Earth. The navigation panel can also be hidden, or dragged to a different screen. Convenient.

This change is simple to make by editing the Google Earth "kvw" (Keyhole View?) file. On my system, this is found at C:\Program Files\Google\Google Earth\kvw\default_lt.kvw. This is a simple text file. Open the file in a text editor and find the Navigation Panel windowStack. Change the location attribute to "float" as you see below.

Google Earth XML Configuration

Make a backup before you start making changes. More extreme changes are not kosher, resulting in Google Earth failing to start. Restoring the kvw file from backup will fix the problem.

[Added]OgleEarth provides directions on using this hack on Mac OS X.

For the Mac, right-click on the Google Earth application (when it is not running), select "Show package contents", then navigate on over to:

/Applications/Google Earth.app/Contents/MacOS/kvw/default_lt.kvw

Open it in a text editor and apply Juice Analytics’ hack. 

Incidentally, the Google Earth map you see above is graphing hurricane paths of the last 5 years with color coded intensities using a Python KML library. Just trying to keep up with the Joneses (the Joneses, in this case, being the Timoney group with their excellent start to Google Earth based analytics).

Hurricane Analysis with Google Earth

Brian Timoney of The Timoney Group just launched a great web site that gives a feel for the types of analysis that can be done with Google Earth. The site, entitled Energy Impact of Rita and Katrina, lays out a multitude of geographic views (available as .kml files in Google Earth) that show the hurricanes’ devastation in the Gulf region. For example, here’s a look at the Gas Production in 2004 vs. 2005:

2004/2005 Gas Production

That’s just the eye candy. Even better, Brian has developed a series of Google Earth tools that let you analyze the actual geographic data. Below is the description of the a tool that lets you choose a buffer distance around a path.

Timoney Buffer Tool

Excel Geocoding Tool v2

Update (March 2012): Max Rice has generously updated the Excel Geocoding Tool. It is shared on Github here. I’m happy to announce a few small revisions to our Excel geocoding tool. The tool takes a list of addresses and will look up the latitude and longitude of those addresses. The addresses can then be exported as a Google Earth map.

A user pointed out that the tool wasn’t looking up zip+4 codes properly in Yahoo and this problem is fixed along. Also, I’m sorry to report that Yahoo has lowered their limit of free geocodes to 5,000 per day from 50,000 per day.

Geocoding Tool v3.1.xls

Public Data for the Public

We’ve blogged recently about the rich data available from the census bureau and the ability to visualize it with low cost GIS tools like Google Earth. Our friend Ducky recently released her mash-up of Google maps and census data that shows the potential for dynamic and informative maps that are easy and free to use.

Webfoot map

This data is available to everyone, but compare the data you find at the census bureau to some of the maps at Webfoot. The census bureau presents a bunch of numbers and Webfoot made them come alive. At Juice, we call this "democratizing data" and it shows the true potential of the next generation of web tools to empower your organization and put the data at the hands of the people that need it. Check out Absolutely Google Earth for a source of tools and resources to do the same.

Census Data in Google Earth

We love Google Earth because it puts the power to explore data in the hands of average folks. We've been exploring uses for census data and wanted to share some of this data with the world. What you're seeing here is a map of the counties in the United States colorized by median age. Lighter colors are older.

Median age in United States by county (lighter is older)
Median age around Detroit, Michigan by census block group (lighter is older)

Census data is also available at the block group level which is much, much more detailed.

Without further ado, what follows are three sets of links for each state which allow you to explore population density, median age, and male/female ratio in each state at two levels of detail. Google Earth is required. We did have some ftp issues when uploading these files, so if you have any problems, let me know and I"ll re-upload the file.

Population Density

Lighter is higher population density (white is 800+ people per square mile), Dark is lower population density (black is 2 or fewer people per square mile)

by County (overview)

by Census Block Group (fine detail)

Alabama

Alaska

Arizona

Arkansas

California

Colorado

Connecticut

Delaware

District of Columbia

Florida

Georgia

Hawaii

Idaho

Illinois

Indiana

Iowa

Kansas

Kentucky

Louisiana

Maine

Maryland

Massachusetts

Michigan

Minnesota

Mississippi

Missouri

Montana

Nebraska

Nevada

New Hampshire

New Jersey

New Mexico

New York

North Carolina

North Dakota

Ohio

Oklahoma

Oregon

Pennsylvania

Rhode Island

South Carolina

South Dakota

Tennessee

Texas

Utah

Vermont

Virginia

Washington

West Virginia

Wisconsin

Wyoming

Alabama

Alaska

Arizona

Arkansas

California

Colorado

Connecticut

Delaware

District of Columbia

Florida

Georgia

Hawaii

Idaho

Illinois

Indiana

Iowa

Kansas

Kentucky

Louisiana

Maine

Maryland

Massachusetts

Michigan

Minnesota

Mississippi

Missouri

Montana

Nebraska

Nevada

New Hampshire

New Jersey

New Mexico

New York

North Carolina

North Dakota

Ohio

Oklahoma

Oregon

Pennsylvania

Rhode Island

South Carolina

South Dakota

Tennessee

Texas

Utah

Vermont

Virginia

Washington

West Virginia

Wisconsin

Wyoming

Median Age

Lighter is older median age (white is 46.0 years median age), Dark is younger median age (black is 29.0 years median age)

by County (overview)

by Census Block Group (fine detail)

Alabama

Alaska

Arizona

Arkansas

California

Colorado

Connecticut

Delaware

District of Columbia

Florida

Georgia

Hawaii

Idaho

Illinois

Indiana

Iowa

Kansas

Kentucky

Louisiana

Maine

Maryland

Massachusetts

Michigan

Minnesota

Mississippi

Missouri

Montana

Nebraska

Nevada

New Hampshire

New Jersey

New Mexico

New York

North Carolina

North Dakota

Ohio

Oklahoma

Oregon

Pennsylvania

Rhode Island

South Carolina

South Dakota

Tennessee

Texas

Utah

Vermont

Virginia

Washington

West Virginia

Wisconsin

Wyoming

Alabama

Alaska

Arizona

Arkansas

California

Colorado

Connecticut

Delaware

District of Columbia

Florida

Georgia

Hawaii

Idaho

Illinois

Indiana

Iowa

Kansas

Kentucky

Louisiana

Maine

Maryland

Massachusetts

Michigan

Minnesota

Mississippi

Missouri

Montana

Nebraska

Nevada

New Hampshire

New Jersey

New Mexico

New York

North Carolina

North Dakota

Ohio

Oklahoma

Oregon

Pennsylvania

Rhode Island

South Carolina

South Dakota

Tennessee

Texas

Utah

Vermont

Virginia

Washington

West Virginia

Wisconsin

Wyoming

Male/Female Ratio

Lighter means more men than women (white is 55% men), Dark means more women than men (black is 45% men)

by County (overview)

by Census Block Group (fine detail)

Alabama

Alaska

Arizona

Arkansas

California

Colorado

Connecticut

Delaware

District of Columbia

Florida

Georgia

Hawaii

Idaho

Illinois

Indiana

Iowa

Kansas

Kentucky

Louisiana

Maine

Maryland

Massachusetts

Michigan

Minnesota

Mississippi

Missouri

Montana

Nebraska

Nevada

New Hampshire

New Jersey

New Mexico

New York

North Carolina

North Dakota

Ohio

Oklahoma

Oregon

Pennsylvania

Rhode Island

South Carolina

South Dakota

Tennessee

Texas

Utah

Vermont

Virginia

Washington

West Virginia

Wisconsin

Wyoming

Alabama

Alaska

Arizona

Arkansas

California

Colorado

Connecticut

Delaware

District of Columbia

Florida

Georgia

Hawaii

Idaho

Illinois

Indiana

Iowa

Kansas

Kentucky

Louisiana

Maine

Maryland

Massachusetts

Michigan

Minnesota

Mississippi

Missouri

Montana

Nebraska

Nevada

New Hampshire

New Jersey

New Mexico

New York

North Carolina

North Dakota

Ohio

Oklahoma

Oregon

Pennsylvania

Rhode Island

South Carolina

South Dakota

Tennessee

Texas

Utah

Vermont

Virginia

Washington

West Virginia

Wisconsin

Wyoming

If you want to know more about Google Earth, check out our Absolutely Google Earth a collection of tools and resources to get you started.

We're working on a project to make this and other simple mapping applications more widely available. If you're a python guru who is interested in building great mapping applications like Chicago Crime give me a jingle.