The following is a transcription of the Youtube video below. See for visual references.
Today we’re going to talk about the best ways to format your spreadsheets to get the most accurate possible result from our Spreadsheet Uploader tool.
Let’s go ahead and get started!
From our home page, we’ll go ahead and click the upload spreadsheet button. You can also navigate directly to https://geocod.io/upload in your browser.
Our Spreadsheet Uploader is designed to be easy and intuitive to use, but there are many ways to set yourself up for success. We’ll walk through the upload process at the end of this tutorial - and see what kind of results we can get - but first, let’s discuss some formatting tips to keep in mind when creating your spreadsheets.
You can use whatever software makes sense for your project. We accept a number of different formats - including: XLS, XLSX, CSV, TSV, and ZIP files. In this case, I’ve got a CSV open in Google Sheets that I would like to upload and geocode.
There are three major factors to check when formatting your spreadsheet.
First, your spreadsheet must have headers at the top of each column - preferably in the very first row. We use headers to identify and reference the columns we will use to retrieve specific data.
| Address | City | State | Zip Code | Country |
|---|---|---|---|---|
| 660 Pennsylvania Ave SE | Washington | DC | 20003 | US |
| 1718 14th St NW | Washington | DC | 20009 | US |
You can name the header anything you like. For example, if you were geocoding Canadian addresses and wanted to switch column C from “state” to “province”, we would still be able to find that information. During the upload process, you get to choose which columns contain the appropriate address components.
Next, make sure that your formatting is consistent.
There are a number of ways that you can input data into our spreadsheet uploader. You can split the address into multiple columns as shown in my initial sample. You can also place the entire address in a single column.
| Address | City | State | Zip Code | Country |
|---|---|---|---|---|
| 660 Pennsylvania Ave SE | Washington | DC | 20003 | US |
| 1718 14th St NW | Washington | DC | 20009 | US |
| Address |
|---|
| 660 Pennsylvania Ave SE Washington, DC 20003 US |
| 1718 14th St NW Washington, DC 20009 US |
When reverse geocoding, the same is true. You can split the latitude and longitude into separate columns or place them into a single "coordinates" column split by a comma.
| Latitude | Longitude |
|---|---|
| 38.885172 | -76.996565 |
| 38.913274 | -77.032266 |
| Coordinates |
|---|
| 38.885172, -76.996565 |
| 38.913274, -77.032266 |
The important thing to remember is that once you’ve chosen the format you would like to use, it needs to remain consistent throughout the document. If you elect to use a multi-column format, you shouldn’t have rows that contain a full address - and vice versa. This could potentially cause errors and prevent us from getting the correct results.
If your spreadsheet includes addresses or coordinate pairs in multiple different formats, you will need to split them into different lists.
A couple of other considerations:
It’s important to note that we only ever add columns during the geocoding process. We never delete them. So if you have other data stored in your spreadsheet beyond address information, that will be retained in your result.
If we take a look at this spreadsheet I’ve already generated, you can see I included a "business names" column before the "address" column. This was included with my finalized spreadsheet. Geocodio added these columns past the address, but everything else is the same.
| Business Name | Address | Latitude | Longitude | Accuracy Score | Accuracy Type | ... |
|---|---|---|---|---|---|---|
| Peregrine Espresso | 660 Pennsylvania Ave SE Washington DC 20003 | 38.885172 | -76.996565 | 1 | rooftop | ... |
| Mexicue | 1718 14th St NW Washington DC 20009 | 38.913274 | -77.032266 | 1 | rooftop | ... |
| TaKorean | 1309 5th St NE 20002 | 38.908724 | -76.997653 | 0.9 | rooftop | ... |
The last thing to check for before uploading your spreadsheet is only important if you plan on geocoding addresses in Canada.
If this is the case, be sure that you include that information at the end of your address or coordinate pair. For example, if you are using a multiple column format, add a fifth column called “Country” that allows you to define the address as either in the “US” or “Canada”. If you are using a single column format, add the name of the country at the end of the address string.
Quick note: Currently, we only offer forward geocoding of addresses in the US and Canada. No other countries will provide a result. If you are reverse geocoding coordinate pairs, we can return Mexican addresses as well.
If no country data is provided, we will always default to the US.
Now that we know the basics of how to format our spreadsheet, let’s talk about a few more things that might help you to receive more accurate results.
Your addresses do not need to be complete. They just need to be formatted the same way.
You can see in my sample spreadsheet that there is some incomplete data. In these rows up top, I don’t have information about the city and state. In these rows below, I don’t have information about the Zip Code. This will not stop us from providing geocoded data.
| Address | City | State | Zip Code | Country |
|---|---|---|---|---|
| 201 F Street NE | 20002 | US | ||
| 1001 2nd St SE | 20003 | US | ||
| 3064 Mount Pleasant St NW | Washington | DC | US | |
| 1052 Thomas Jefferson Street NW | Washington | DC | US |
Still, the more information you provide, the more accurate the results you will receive.
If you want rooftop-level accurate responses, we do require a street address in order to find the specific locations.
We also require either a combination of the city and state - or - the zip code. You can leave off one or the either, but if all we receive is the street address, you will likely receive an error.
If you provide a City/State combination or a Zip Code without a street address, we will return the centroid coordinates for that region. This is the center most point when taking all of the boundaries of the city or the zip code into consideration. Here is an example of a spreadsheet where I only input Zip Codes. You can see that they all return place-level accuracy instead of rooftop.
| ZIP Code | Latitude | Longitude | Accuracy Score | Accuracy Type |
|---|---|---|---|---|
| 20910 | 39.003109 | -77.029648 | 1 | place |
| 20008 | 38.93521 | -77.060399 | 1 | place |
| 20003 | 38.881904 | -76.990935 | 1 | place |
When reverse geocoding a spreadsheet, be sure to provide both coordinates - with latitude first and longitude second. They must be in that order.
Now that we’ve got our spreadsheet in order, let’s get to uploading!
Back to our Spreadsheet Uploader tool, we’ll start by uploading the file.
Once it has been processed, we can verify that the input columns match each part of the address we require. You can adjust which header is associated with which data type by using the dropdown menus.
Before moving forward, be sure to check the boxes at the bottom of this section to see if the format looks correct. Then, hit “Preview”.
In this section, you’ll see a sample map with plots pointed representing a few of the rows in your spreadsheet. This should match the regions you would expect to see data points. If you don’t see any points at all you may want to go back to the first step to make sure the data is being imported correctly.
In this case, everything looks good.
In the third step, you can elect to add different field appends at the expense of additional lookups. This could increase the cost of your upload. We’re going to skip this step for now, because we just want a basic geocode.
Finally, we agree to Geocodio’s terms of use and hit the start button. We will upload your spreadsheet and add the requested fields to your spreadsheet.
Let’s open it up to see what we’ve got.
It looks like everything was a success! We’ve geocoded each of the addresses, returning coordinates that match their location. We’ve also parsed out the addresses and provided an accuracy score to determine how accurate the result is versus your initial input.
You can see one of our addresses could not be geocoded. This is because we did not provide information about its City, State or Zip Code.
| Address | ... | Number |
|---|---|---|
| 475 H St NW | ... | Could not geocode address. Postal code or city required. |
The vast majority of our results return with high accuracy scores at rooftop-level. That’s what we were hoping for!
However, there are a smattering of results with slightly lower accuracy scores. This can happen for a myriad of reasons:
We regularly update our database with new addresses and if there’s something you can’t find, you are welcome to report it to: geocod.io/report and we will do our best to research why it is not in our database.
Regardless, by following these tips, you should ensure that you get the best results possible.
I hope this has been a helpful guide. If you have any additional questions that I didn’t cover, feel free to reach out to support@geocod.io.
Until next time, happy geocoding!