Continuing Education: Build a POI File with Batch Geocoding

OK - you want to try your hand at creating a POI file. Maybe your company delivers and you want a file that can be used to save time in getting to your customers. Maybe you are just interested in the process.

You need to start with a list of "names" and "addresses". Let's say that you are wanting a "customer" list, so the "names" might be something like XYZ supply or ABC store. They do not have to be unique (a customer might have several locations so the addresses would be different).

Since I recently agreed to maintain the Jo-Ann Fabric and Craft Store file, I used the www.joann.com "Store Locator" facility to acquire all of the store addresses. I used several tools to get the addresses into the general format of
Name (with store number)
Street Address
City
State abbreviation
Zipcode

Now you need a way to get the coordinates (longitude and latitude) for each address. A fairly good batch geocoding tool can be found at
http://www.mapdevelopers.com/batch_geocode_tool.php

It will allow you to input up to 100 addresses at a time (although I have not tested this limit).

It does not specify what the input must be like but I will give an example of what does work. I hope that you will follow along with me by actually repeating the steps below.

I copied and pasted the following into mapdeveloper's "Input Window".

When I clicked the "Find Addresses" button below the "Input Window", mapdevelopers.com returned

I copied and pasted the output file into Notepad and saved it as "mapdevelopers.txt" (although any name.txt would do).

Then, I fired up Excel and clicked "File" > "Open". I went to the bottom of the "Open" dialog and changed the "Files of type:" box so that it read "Text files (*.prn, *.txt, *.csv)". Then I navigated to the folder where I had saved "mapdevelopers.txt"; highlighted "mapdevelopers.txt"; and clicked "Open".

Excel gave me the "Text Import Wizard" dialog. I changed the "Choose the file type that best describes your data" to "Delimited" and clicked "Next". I checked "Tab" and "Comma" as the delimiters and clicked "Finish".

I did a "File" > "Save As" and went to the bottom of the dialog and changed the "Save as type:" box to read "CSV (Comma Delimited) (*.csv)" and saved the file as "mapdevelopers.csv".

I now had a file that was now close to what I wanted - but obviously not in the format that POI Loader would accept.

So, I fired up EPE (Extra POI Editor) which can be downloaded from
http://turboccc.wikispaces.com/Extra_POI_Editor#toc5

I did "Options" > "M Column CSV fields" and chose one of the "profiles". I typed "mapdevelopers" in the "Change selected profile name" box and clicked the "OK" button that appeared at the end of the field when I started typing. I changed the fields to be
POI Name
Address
City
State
PostCode
Latitude
Longitude

Then I clicked "Close" at the bottom of this dialog.

Now - in EPE - I did a "File" > "Open" and navigated to "mapdevelopers.csv" and clicked "Open". EPE knew what the file format was and inputted it.

Now I had a file I could manipulate as I wanted. I could save it back as a basic .csv file by using "1 - User Default" of the "4-Column CSV fields" to describe how to arrange the various fields to conform to the POI Loader expected format of:

Note that EPE's "1 - User Default" - will output a .csv file that has the following fields
Col 1 <Longitude>
Col 2 <Latitude>
Col 3 <POI Name>
Col 4 <Address><br><City><br><State> <Post Code><br>Phone:<Phone><br><br><Description>

Also note that some of these fields can be blank - as there was no "Phone" or "Description" data in my example.

Or you could just save the file as a .gpx file which I find significantly more useful.