Tableau has geocodes in the maps database for cities with population greater than 15,000. If you are working with rural or small-town USA data, your points will not be mapped. I had some fun(?!) with custom geocoding. When you look at community support to find out lot of threads with no successful conclusions, desperate pleas for help, or even the worst no replies on that thread, your heart would just sink down to stomach. Don’t fret. Custom geocoding seems to work fine if you just follow the instructions to the “T” and take some extra effort not to add your own mess to it. Here are some tips for custom geocoding small-town USA.
- Do not have null values in “City” or “State” column.
- Correct all the misspelt cities and states. You’ll really save some time – NYC is not a valid city
- If you expand the state codes to full name, for example “IL” to “Illinois” that brought from 356 unknowns to 157 unknowns for me.
- Of course, the inevitable – you got to extend the geocodes for Tableau. Here is where the fun begins
- I needed city and state. Maintain the hierarchy and the correct names of the columns Country (Name), State, State/Province, City, Latitude, Longitude. Any changes to this order or even an extra space between the “/” in State/Province will get you this error
“Warnings occurred while importing the custom geocoding data. Do you want to keep the imported geocoding? The file <fileName.csv> could not be used because it was missing the following levels: [Country]. Level names are case sensitive and must match exactly”
- Do not have duplicates in your rows. Duplicates will make Tableau ignore the city altogether and throw this warning.
“Ignored <number> invalid rows in the file <filename>.csv”
- You got 157 unknowns and looking at the forums, you try to outsmart Tableau and custom geocode every inch of United States, even some cities very known metropolis like “Boston, MA” will vanish from your map. My guess is Tableau ignores duplicates. I would suggest sticking ONLY to your “unknowns” and not the entire United States.
- Tableau says schema.ini is optional. Creating schema file helps to reduce the time to import the geocodes as Tableau can understand your file better.
- Make sure you got a “CSV” file instead of “Excel”. With “Excel” file importing the geocodes was not throwing any errors but your map will have the same number of “unknowns”.
- You can try latlong.net to find the latitude and longitude of your small-town USA.
If you want a quick sample, my files are here. Have fun geocoding!