KMLGeocode (available here under the Google Geocoder listing) takes an address file in Excel (or XML) format, and creates a Google Earth KML file that plots geocodable addresses in the correct position. Load an address XLS file into the program (like this sample data also available on the download page):
You have to specify the column names that contain the key address fields required for geocoding, as well as the output KML filename. Once done, create the KML output file and open it in Google Earth. Addresses that could be geocoded will be plotted with orange pushpins:
Clicking on pushpin will bring up all the data fields for a record in the spreadsheet, not just the specified ones. Addresses that couldn’t be geocoded are given yellow pushpin designations, and will either show up plotted in a general location, or not plotted at all in Google Earth (though they will be listed in the Place pane under the KML file listing:
To embed the actual geographic coordinates for each of these addresses, right-click on the KML file listing (people.kml in the above example), and save it as another KML file with a different name. You can now open this different KML file in the KML Geocode Report program:
Specify the name of a text output file; this output will be a spreadsheet-readable CSV file that contains the original spreadsheet data with the longitude and latitude of geocodable addresses appended at the end as X and Y coordinates:
ID,Name,Group,Street,Street2,City,State,Zip,Phone,Email,X,Y
11, Suzanne White, Office of Information Resources, 312 8th Ave North, Suite 1600, Nashville, TN, 37243, 615-253-4799, suzanne.white@state.tn.us,-86.784031,36.164133
12, Kurt Snider, US Fish and Wildlife Service, 446 Neal St, , Cookeville, TN, 38501, 931-528-6481, kurt_snider@fws.gov,-85.497903,36.138056
14, David Tirpak, Comptroller of the Treasury, 505 Deaderick St, Suite 1700, Nashville, TN, 37243-0277, 615-401-7820, david.tirpak@state.tn.us,-86.781603,36.165238
The “UnMatched” file is supposed to contain the non-geocodable addresses in KML format (at least, according to the PDF manual for these programs), but it didn’t show up for the sample data when I tried it. When you “Process” the files, in addition to the output file above, you’ll get a pop-up telling you the overall geocoding success percentage:
Note: When installing both programs, the installation directory is listed simply as “C:\Program Files\”; however, unless you specify the new name, it is installed in the directory “C:\Program Files\BRalston”, and “BRalston” is the folder in the Start Menu where you’ll find the program shortcut icons.
Thanks to Dr. Ralston at the University of Tennessee Dept of Geography for these tools!… they came in extremely handy when I needed geocoded addresses for a point pattern ananlysis which is part of my thesis project.
There are a bunch of other free geocoding tools available as well; check the geocoding category on this blog for more info.
Here is a new online map for ya made with batchgeo… http://landsurveyorsunited.com/page/new-lsu-member-map
this map has almost 1000 land surveyors all over the globe based on level of experience
user contributions on LSU are up 93% I am hoping that the software you provided above (thank you) will render a map that lays out a little faster…
WOW this application is just what I was looking for! I’m sending a thank you note to the author right now..
Doesn’t work on W7 even with sample data :(
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.InvalidOperationException: The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at xml2kml.Form1.ProcessXLSFile(String& thefile)
at xml2kml.Form1.btnInput_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
Works on my Windows 7 system without issue; you might try contacting the program author.
OK, I fixed it. It didn’t work because I have x64 CPU and JET is absolute and works only for 32-bit so xls files can’t be imported.
To fix: Use coreflags utility to set the program to work in 32-bit compatiblity.
Download:http://www.apexsql.com/zips/CorFlags.zip
run: corflags kmlgeocode.exe /32bit+