This example shows you how to use Ultra Geocoder to find coordinates for street addresses in a Microsoft Access database. Most of your location data will be specified by complete or partial street addresses (e.g. zipcodes, or cities), but Ultra Mileage requires coordinates. Use Ultra Geocoder to 'geocode' (ie. convert) these addresses to geographic (longitude, latitude) coordinates.
This example uses the Customers table in the geocoder_sample.accdb database that can be found in the Ultra Mileage examples file:
This table is based on the USA customers in the Microsoft Northwind sample database. The design view looks like this:
In addition to the original Northwind data columns, two double precision number columns (Longitude, Latitude) and a short text column (Status) have been added. Longitude and Latitude will store the geocoded coordinates. Status reports the success or otherwise of the geocoding process. This reports success, errors, ambiguous results, etc.
This is what the data actually looks like:
Setting the Ultra Geocoder Parameters
Start Ultra Geocoder. The parameters may be different, but you will be presented with a dialog box that looks like this:
The Geocoding Service box describes the selected geocoder. Select the required geocoding service and press Settings to set the geocoder's settings. Here we have selected LocationIQ and this is what the LocationIQ settings dialog box looks like:
This is currently the most complicated settings dialog box. The Maptitude geocoder does not require any settings. All of the online geocoders will require one or possibly two license keys. Some, such as LocationIQ, distinguish between State and County, whilst Ultra Geocoder and some geocoding services only recognise "Region". Select how Ultra Geocoder's Region data should be mapped. The example data's Region field stores the US State, so we select the State radio button.
Back on the main panel press the Change button in the Database box to set the database parameters. These work in virtually the same way as Ultra One2OneMileage's Database Parameters. Set the dialog box to look like this:
Select 'Access 2007' for the Data Source Type. For the Data Source, select the geocoder_sample.accdb file from the examples zip. The database only contains one table, Customers. Select this if it is not already selected. Set the individual input columns. Note that most of these can be set to '<none>' if you are not using this data. For example, if you were geocoding zipcodes, you would set Postcode to the zipcode column and all of the other columns to '<none>'. You must set a country. You can set this to an input column, or you can override the country to a fixed value for all of the input data. For the latter option, the country is displayed using a 2 character ISO code (e.g. USA is 'US', South Africa is 'ZA', etc).
You must set all three output columns. You must specify columns for the coordinates (Latitude, Longitude), and a string column for the status.
Leave the Row Selection option set to the default of 'all rows'. This works in the same was as One2OneMileage's row selection, and can be used to recalculate failed rows. For example, you could try different geocoders on the same data.
Press OK to return to the main panel.
Press Start to start processing. This is a very short database and should not take long to compute. The resulting table should look something like this:
The exact results may vary depending on the geocoding service used. Note that results were found for all of the addresses. Some of them were unique results (Okay) but many were ambiguous. For example "PO Box 555" is ambiguous because most geocoding services are unable to geocode a PO Box.