Introduction
The conversion of Coordinates to Addresses is an issue that arouses interest from many areas, not just Telecom.
And one reason is that this mapping is increasingly present in our day-to-day, for example when we receive a list of Addresses, and we need to make such a coverage analysis at each point.
There are several ways to obtain this conversion from a manual to fully automated type. But when we don’t have a standard procedure to obtain these data, we ended up wasting a lot of time.
Thus, we’ll now know a little more on this subject.
Goal
From a list of Addresses, get a list of corresponding Coordinates, for example to show coverage level at each point.
File Structure
You are already familiar with the Hunter structure.
In today’s tutorial, we add the directories as shown below.
Remember that the sample files always refer to a structure based on 'C:' directory folder followed by the ‘Hunter’ directory, and other subdirectories. If you copy the sample files to another location (directory), you need to make appropriate adjustments for it to function properly.
A very simple way to obtain the standard structure is simply:
- Detach the ‘ZIP’ file received in the root folder 'C:'.
- Click the mouse right button and choose ‘Extract Here …’, answering yes to any possible existing files.
Thus, with the structure set up, just access the script folder, and run the application.
Ways to get the Coordinates from Addresses
There are several ways to obtain the Coordinates (Latitude and Longitude) from a valid Address.
Manually
The simplest way, of course, is to obtain manually but depending on the amount of records (Addresses) often becomes infeasible. Or at least is very time consuming.
One of the advantages of manually and individually to obtain Coordinates is that at least we can have a critical analysis, ie, the results are quite accurate, because you’ll be correcting any inconsistencies as they arise.
A common way to do this manually is access Google Maps or Google Earth, enter the Address, and then check the desired location.
Through Services and Websites
Another possible way is to use the services that some websites offer.
Basically you send a file as an Excel spreadsheet with your data and get back a file with the outcome, including the records that could be identified, as well as those who could not be located.
However, this solution has some details that may eventually become a problem.
The first is the limitation. Many of these sites allow only a certain amount of queries per day. The reason for this is simple: they use the APIs from providers such as Google or Yahoo!, and these providers also limit the number of daily requests, so these sites need to do the same with the users, ie you.
The second is that many sites do not offer a good service, and often require manipulation of data - putting them in their format. In addition to charging for the service!
The third and perhaps most importantly, your data will be sent to an external server, ie, your data can be accessed by other people!
Through Customized Programs
The third way is to geocode Addresses through the use of local programs, like one you can create with Excel.
In such cases, the program also requires the user to obtain an API Key some geocoding service provider, such as the aforementioned Google or Yahoo!
Hunter Solution
Our solution currently does not use yet none of these options mentioned.
Let’s use a bit of logical reasoning: If you type an Address at geocoder, what happens?
If the Address is correct, it is showed! If it’s not correct, geocoder asks you to fix it and try again, sometimes offering some suggestions or options.
So let’s use the client program Google Earth, and do a manual geocoding.
Assume we have a list of valid Addresses, and one of them is ‘Eiffel Tower, Paris’.
Type this in the search box of Google Earth, and press Enter.
We then have point ‘A’, our Address - obviously with their corresponding Coordinates! (To see the Coordinates, click on the Properties and choose the mouse right button).
What did we do? We entered an Address on Google Earth, and it returned a point.
And you know what the best part of it? We can enter this info via a flat file in the Google Earth ‘KML’ format. Suffice it to put the Address between the ‘address’ tags!
Note: Remember that this is a text file, and can be created with Windows Notepad.
By opening this file, the result is the same: there is a point in the desired Address.
And now you should be asking, but how can I see all Coordinates? Do I need to click on every point (all I have in my list)?
The answer is no. Simply right-click the file you opened (which is in ‘Temporary Places’) and ‘save’ the file again - remembering to save with the extension ‘KML’ (You can even overwrite the original file).
Next, locate where you saved that file, click the right mouse button and select Open with Microsoft Excel.
Excel will realize that this is not one of its standard extensions, and will ask a question. Click Yes to continue.
Keep clicking ‘OK’ to open as a table ‘XML’.
Okay, here we have our data: Address and Coordinates together! (The notation is Longitude, Latitude, Altitude).
And of course: we did it with a single point, but we can do for any points we wish.
Okay, but we do not want to repeat these steps manually to a list (although, as we said, this is possible). I then created a simple application in Access or Excel - as we are used to create - to generate the file for us.
So, creatively, we only used Google Earth and saw how to get our expected result in a practical, fast and efficient method!
The Application
If you are a user Hunter you already received the sample application - with codes - in your e-mail. It is very simple, so let’s see it in action, a more practical example.
Running the script, we have access to a very simple interface where you can select (1) where the initial file (2) with Address list is. Clicking on Google Earth (3), this data is then processed and we get a file ‘KML’.
We also have the option to directly edit this file in Excel (4), and open the folder that contains it (5).
The file format is quite simple, and the only required field is, of course, the ‘Address’ field (1).
The other two fields: ‘Name’ (2) and ‘Description’ (3) serves only as an aid - can be left blank.
Anyway, it is interesting to use one of them, for example the ‘Description’. If your original file has several other columns, you can use it to make a correlation with the original file and the file with the final Coordinates - using this field as a reference for the VLOOKUP in Excel for example. But you get to decide how you want to do.
Geocoding a list of Addresses
In order to demonstrate, we need a list of Addresses. We use the list of public Addresses of schools in the city of Vitoria, Brazil.
In your case, the source can be any, such as the list of bank where you want to check the service operator coverage, for example.
Thus, by visiting the Town Website, we downloaded the data.
Copying data and pasting in Excel, we have what we need.
We add some columns, for our input format in the application. The data of our sample Addresses are separated by column, then use Excel formulas to concatenate the format ‘Name (of Street, Avenue …), Number City’.
Note: This notation is used in Brazil. Depending on your region, get the data according to their nomenclature. More on that later.
Okay, now we open the Hunter application, and clicking the Edit button (the little pencil next to the name of the file to be parsed).
With this file open, we can then paste data from columns in Green added that the original file, to our original Addresses listing.
That done, just click the button to rotate. Then open the generated file, and follow the procedures already seen before to get the Addresses and Coordinates in Excel!
Not so Perfect as it seems…
Unfortunately, not everything is as good as it sounds. The result of geocoding heavily depends on the input data accuracy, ie the Addresses. If they are ‘bad’, we will not get returned Coordinates.
But this issue is not a new problem - and we do not have much to do. For example, if you type an Address that does not exist on Google Maps, what will it returns?
Nothing, most of the time. And when the Address has errors (typos), the best geocoder can do is to offer you some suggestions - never decide for you. (Thank goodness, because this decision could be to a wrong conversion).
Although these cases are really difficult to parse - as there are numerous possibilities for Address formats and errors - at least there are some actions we can take to try to minimize the faults.
And these actions represent the ‘error handling’, or try to pass an Address to the geocoder that is as close as possible to the format that it can recognize.
But now an important observation: if the Address is not recognized by the method used above, it’s is very likely that it won’t be not recognized using any other automatic method, for example on websites or services that do the automatic geocoding.
But We can help!
Yes, we can help in these cases, putting data in an appropriate format. But to put data into an appropriate format, we need to know what geocoder expects. What’s it that formats?
Although there are no rules to follow, we can start learning with it.
For example, let’s take an example (output) of our geocoding.
Entering an Address that we use in our example in the search box, the geocoder response is more complete.
We entered:
- ‘RUA PROFESSORA CLARA LIMA, 63 VITÓRIA’
And the geocoder returned:
- ‘R. Profa. Clara Lima, 63 - Antônio Honório, Vitória - ES, Brazil’
Important: Note that, luckily, the geocoder is very ‘smart’, and quite able to identify variations. For example, only with our input data, it correctly identified the real Address and Coordinates.
But if we can help it, why don’t do it? After all, if we make things easier for the geocoder, it can identify more Addresses, and the big benefit will come to us, don’t you agree?
From what we saw above, is good practice to then convert the words like ‘street’ or ‘avenue’ for their expected standards ‘R’ or ‘Av’.
In our example, the original file has the information of the ‘neighborhood’, but we prefer to omit it. This is because that information sometimes gets in the way - the neighborhood may be typed incorrectly, or worse, may not match the street (actually the Address can be in another neighborhood).
Basically, if we have the information: ‘Name of the via’, ‘Number’ and ‘City’ it is enough to properly evaluate point location (Coordinates).
Note: Other types of information also bring positive results, such as zip code, but this will depend on how the Address is most common in your region or country, that is, our approach is quite reasonable, and other information should be used only when basic ones is missing.
Other information: The geocoder does not understand punctuation. A good practice is to replace all these types of characters such as ‘-’ for space ’ '. A single exception is the case of the apostrophe - it should simply be removed: ‘Joe’s’ becomes ‘Joe’s’.
The Address must contain only single spaces - that is, remove double spaces, triples spaces, etc…
In short, we seek to obtain an Address in a more standardized possible form.
But there will always be cases where the parsing - for example through a VBA function - is almost impossible.
For example, when the data are of very poor quality! This happens for example when we have Addresses like ‘Street X, Corner With Street Y’, or ‘Bamboo Moutain’. In these cases, no need to say it is impossible any attempt at treatment.
Addresses written wrongly, Addresses that had officially changed, new cities that arise … All this also needs to be corrected manually.
Anyway, as mentioned above, this is not a problem of methodology demonstrated - the Address would not be in any other identifier also automatically.
The way out then is to go changing the data until it find a favorable outcome.
In our case, a good tip is to geocode the original list.
Then, in the Excel final file, only correct Addresses that fell in some cases above (no geocoding), and need correction.
And run again only for new records with fixed data.
Conclusion
We have seen how to use creativity to get unexpected solutions.
Using only the local Client ‘Google Earth’, we’re able to obtain the Coordinates for a list of Addresses! Although the procedure can be done manually, we also use the help of a simple VBA application to speed up the process of generating the ‘KML’ file.
Thus, we avoid the service of others, which moreover, makes our data vulnerable.
That’s it for today, we wait you in the next tutorial.
Oh, if you like, could you share? It is very easy using the links right below…
Download
Download Source Code: Blog_035_Hunter_Converter_-Address_to_KML(Google_Earth_Coordinates).zip (375.9 KB)