Add latitude and longitude to your dataset using addresses, cities and countries

by Pablo Sáenz de Tejada

I am currently working in a new visualization for the Music Viz Contest, to try to win the Big Price: a trip to Las Vegas to compete in the Iron Viz competition at Tableau Conference 2015. I know the probabilities of winning are not very high… but after my first Viz of the Day of last week I feel full of confidence!

I don’t want to tell the idea for my Viz before finish it, but I found a difficulty that almost maked me change completely my initial idea, and I would like to share how I solve it, maybe somebody else has had this same problem.
I am working with 2,000 locations in a big number of countries from Latin America, North America, Europe, Africa, Asia and Oceania, and for each location I have a venue name, city name and country name. But when I tried to map each location in Tableau I get a lot of ambiguous and unknown results and couldn’t map the 10% of them. It seems that with a very big dispersion of locations, if you don’t have additional info like state, region or similar, Tableau has difficulties to map all the points even adding the country and city dimensions to the detail shelf.

My first idea was to look for all the regions / states of all the database hoping that with that additional information will be enough (City, State and Country), so I found this information for the 2,000 locations and although the number of ambiguous and unknowns decreased to 80, it was still bigger than what I needed. The only final solution that I could think of was to add the latitude and longitude of at least those 80 ambiguous or unknown locations, and because of my little knowledge of Google’s API for geocoding, I had many doubts that I have time to finish my viz in time if I had to search for each location in Google Maps, copy the latitude and longitude and paste them in my database.

Instead, after some time searching through the web I finally found a very useful page that I’m sure it will be very useful for a lot of Tableau users: EasyMapMaker. With this web you just need an Excel file with your addresses, the name of the city and the country and it will add the latitude and longitude information. for example, let’s try to geolocalize these locations (4 football stadiums, the address of my previous flat in Madrid and the Data School address, with no region or postcode). For general venues with no address is better to also include in the venue name the city and the country for gaining more accuracy, but as you can see, in the stadiums there’s no address, just the name of the stadium and in the addresses no region, state or postal code info:

 

Venue City Country
Estadio Santiago Bernabeu Madrid Spain Madrid Spain
Nou Camp Barcelona Spain Barcelona Spain
Emirates Stadium London United Kingdom London United Kingdom
Anfield Stadium Liverpool United Kingdom Liverpool United Kingdom
Calle Mancebos, 6 Madrid Spain Madrid Spain
33 Cannon Street London United Kingdom London United Kingdom
To add the latitude and longitude, you only have to follow these simple steps:
1) Copy that Excel info and paste it in the webpage.
EasyMapMaker 01
2) Click on “Set Options” and select Venue for the Address field (City and Country are automatically detected in this case).
3) Click on “Make Map” and copy again the information you pasted previously in the page, but now with the latitude and longitude added and paste it again in your Excel file.
Venue City Country emm_lat emm_lng
Estadio Santiago Bernabeu Madrid Spain Madrid Spain 40.453054 -3.688344
Nou Camp Barcelona Spain Barcelona Spain 41.380896 2.12282
Emirates Stadium London United Kingdom London United Kingdom 51.557834 -0.098675
Anfield Stadium Liverpool United Kingdom Liverpool United Kingdom 53.430829 -2.96083
Calle Mancebos, 6 Madrid Spain Madrid Spain 40.411986 -3.712138
33 Cannon Street London United Kingdom London United Kingdom 51.512611 -0.09484
If you look for each location in Google Maps searching by the latitude and longitude you will see how accurate it is. In this case only Anfield Stadium is a bit less accurate, but very close to the exact location:
Locations lat and long
You can gain additional accuracy adding region / state info or postal codes if you have them. And even if the data Easymapmaker is not always 100% exact, it will help you solving those problems with ambiguous or unknown locations, specially if you don’t have the knowledge to use Google’s Geocoder API. In my case, with this tool I found almost all the lat and long for my 2,000 venues using the venue name, city, state and country, and I only needed to look manually for 4 or 5 that the web couldn’t find, much easier than my initial 80 and I gain some hours to create my viz. I hope I can publish it in the next days.
Avatar

Pablo Sáenz de Tejada

New features in future Tableau versions

1 mins read

Fri 23 Oct 2015

Two ways to add labels to the right inside in bar charts

2 mins read

Sat 03 Oct 2015

Graphic Presentation by Willard Cope Brinton: Data visualization in the late 30s

1 mins read

Sat 26 Sep 2015

Filtering Charts Based on Running Total Comparisons

1 mins read

Sun 20 Sep 2015