Geocoding Addresses for ArcMap

Using Google Earth, Microsoft Access and What Not

Has anyone ever given you a large list of addresses and asked you to throw them on a map? It seems like I have been doing a lot of this lately. Here's a way to do it quickly using Microsoft Access, Google Earth, and ESRI's ArcMap. You could easily stop at plotting them in Google Earth, however I am taking it a step further to plot them in ArcMap because that's where I needed them.

1. Get your address list in a delimited text file or MS Excel format before importing into the database. I use a simple table with one row listing all the addresses.

2. Create a new Microsoft Access database then add a table called "addressList" with only one field: address. Save the table and exit the design view.

Using the standard menu, click "File > Get External Data > Import". Follow the wizard to complete the import.


3. Create a query in the database called "generateKML". Include the "addressList" table in the query design view. Click and drag the field "address" into the first column of the query design view. Right-click and select "Zoom".


Enter the following code:

Save and run the query.

4. Select all the records and copy them to the clipboard. Open up your favorite text editor and paste in the query results from your Access query. This lists all the placemarks for the address list in KML format.


5. You will need to add the following header and footer to the text file before you open it in Google Earth.

Header

Footer
Save the file as "addressList.kml"

6. Open the KML file you created by either double-clicking on it or by opening Google Earth and browsing to it from the file menu. As you zoom in to the general area of your address list you will notice the addresses begin to appear one by one in the view. Once all the addresses have loaded in the view, you are ready to export the address list again as KML.

This time it will include the coordinates for each address. *NOTE: Google Earth does not like a large list of addresses, I have crashed the application before with larger lists. I don't know the exact cutoff, but it is somewhere between 500 and 1000.

7. Export the new KML file by right-clicking the folder in the layer panel containing the address list. Choose "Save As" from the list and be sure to select KML instead of KMZ.

8. Open MS Excel and browse to the newly created KML file and open it. Excel will recognize its format and bring it in in a tabular form.


9. Delete all redundant columns leaving 2 columns: address and coordinate information. Note that all the coordinate information is in one field and needs to be separted before importation into ArcMap. To do this simply save the file as a Tab Delimited text file. Then close the file and re-open it. The Text Import Wizard will take over. Select "Delimited" from the first step and click "Next". Select the "'" as the Text Qualifier. Check "Other" in the delimiter section and type "," as the character. Click finish and you should see your file with the latitude and longitude separated.

Note you will need to do a Find and Replace for the " character to remove them from the longitude column. Clean up your file and save it.


10. Open ArcMap and click on the Add Data Layer button to browse to you text file containing the address and coordinates. Once added, right-click the file in the Source view and select "Add XY data".

Click on the OK button and you should see your addresses plotted in ArcMap.


Current web templates overview suggests that there are more web templates coming up every hour today, resulting in a larger range to choose from. That means bye bye to old powerpoint templates. Although flash templates may still be used in some cases, b but essentially they have grown to be as outdated as a cv template. You can find more on this in web template faqs online. You do not even need to go though web development courses to make out these concepts.

Comments

Re: Geocoding Addresses for ArcMap

Nice job Brades. I read every word of this tutorial, really brings back the 'good ole days.' I'm finally getting to do some maps up here, but I still haven't bought ArcGIS. I'm doing quite a bit of stuff in AutoCAD and 3Ds Max. I've almost got the whole property mapped.

Re: Geocoding Addresses for ArcMap

The webcam mapping page using google Maps doesn't consistently work. when selecting IN for Indiana, you are shifted to India. Also, selecting ON for Ontario, provides no results. Thanks.

Re: Geocoding Addresses for ArcMap

Hi!
The explanation is very good and in detail. I tried working out on it, but failed to see the address list in the layer panel. I am tryin to get the coordinates of addresses. Can anyone suggest me what to do.

Thanks in advance!

Re: Geocoding Addresses for ArcMap

test

Re: Geocoding Addresses for ArcMap

teste

Re: Geocoding Addresses for ArcMap

A really helpful post, thank you. Unfortunately it doesn't seem to work that well, at least for addresses in the UK, even when given addresses in a perfect format. It either gives multiple matches or goes to completely the wrong place. This is particularly the case with large buildings or companies that do not have street numbers.

For instance, the address of my local travel agent is 93 Commercial Street, Rothwell,
LEEDS. LS26 0QD, uk. Point A is the correct business, but it is 300 feet away from where it really is. If I just put in the postal code on its own (LS26 0QD) it is more accurate (about 200 feet away from actual position and no duplicates).

Re: Geocoding Addresses for ArcMap

Geocoded addresses are, as far as I know, based on addresses at known intersections or other natural end points, and then interpolated in between. It would be unusual for a geocoded address to appear in exactly the right spot.

For fun application, see http://www.naa.org/presstime/PTArtPage.cfm?AID=6701

Re: Geocoding Addresses for ArcMap

thanks a bunch. but i am wondering if someone can give me a tip on the whole projection and coordinate system stuff. I want to integrate with some maps already created for me that are in a NAD 1983 projection system and ArcGIS Street Maps. But when I did the import as stated, the dots are in a disconnected realm and don't overlay. I can "zoom to layer" and look at the old stuff or the new geocoded dots, but they won't overlay. I tried editing the properties of the geocoded layer and setting the projection system to be the same (I think), which is NADS 1983 VA State Plane North FIPS 4501, but it still doesn't work. Any suggestions out there?

Re: Geocoding Addresses for ArcMap

Impressive - and very easy to follow tutorial. I have a question on doing a similar thing-

I have an Access DB, say 1000 rows, each with it's own set of coordinates. From any given record, I'd like to launch Earth, and have it go to that xy coordinate and show an Icon, with various info from that DB record. Is there a way to maybe launch google earth with coordinates in the command line ?

Re: Geocoding Addresses for ArcMap

Very, very useful!
@Kirk: The Projection is WGS84. You can find it in ArcMap in Geographic Coordinate System -> World.

Re: Geocoding Addresses for ArcMap

I tried this with GE Pro and it does not work - the coordinates do not seem to come across. Does anyone have any idea how I force the coordinates into the resultant table?

THANKS!

geocoding photos??

Hi Brady,

Do you know may be how to geocode a photo using arcmap??

I have to do the same as in google earth, where you can assign a photo to a specific location in a map.

Thanks!

None

Post a comment

Name:

Email Address:

URL:
(include http://)
Title:

Comments:

Remember your info. on cookie?

Type the word in the image: