How Do I Process My Database for Shortest Path?

How do I geocode my database?

Step 1) Upload your database

To begin using the free online batch database shortest path calculator, you must first upload a database of co-ordinate data.

This is done by clicking on the manage databases button which will bring you to the portion of the site where you can upload, validate, share, and download your personal database. Details on uploading and validating your databases are available here. Sample input data files are available here.

This step is shown in the following image:

Supported database formats

Database type File extension

Microsoft Access 20003 and Microsoft Access 97* *.mdb

Microsoft Access 2007 *.accdb

Comma seperated values *.csv

Text delimited *.txt

* Access 97 does not support the decimal datatype, so you will run into problems if/when you click on the auto generate button to create output fields in your database. Access 97 users will need to create the output fields before uploading databases for use in the batch processing.

Database fields

The database you upload should include the following fields that contain information about the source and destination locations you want to find the shortest path for:

Required input fields

Field name* Field type Field description

UniqueID Number The unique identifier for the record

FromLat Double Source location Latitude

FromLong Double Source location Longitude

ToLat Double Destination location Latitude

ToLong Double Destination location Longitude

* These fields can have any name, you will identify them in the third step

In addition to the input fields, your database can also contain existing fields for the shortest path ouptput that will be produced after you work with the service. These fields are optional because they can be created for you automatically in the third step.

Optional output fields

Field name* Field type Field description

ShortestDistance Double Travel distace of the shortest path (miles)

ShortestTime Double Travel time of the fastest path (hours)

TravelDistance Double Travel distace of the fastest path (miles)

TravelTime Double Travel time of the shortest path (hours)

KMLTime Memo KML path generated from fastest path

KMLDistance Memo KML path generated from shortest path

NearestNodeDistance Double Walking distance from the locations to nearest road segments (miles)

TimeTaken Double Process time in seconds

* These fields can have any name, you will identify them in the third step

Step 2) Choose your database and table: After you have uploaded your database you must select the table you want to work on. This is shown in the following image:

Step 3) Identify your database fields: After you have uploaded your database and selected the table you want to work on, you have to identify the requried input fields in your database. Each of the fields you select from these drop down lists must be unique, meaning that you should only select a column in your database in just one drop down. If you select the same database column in more than one drop down, you will get an error when you try to run your database that says Duplicate output destination 'columnName'.

If your database contains the output fields, you can identify them as well. If not, you can click the 'Auto Generate' button to have the fields created and assigned for you*. This is shown in the following image:

* Access 97 does not support the decimal datatype, so you will run into problems if/when you click on the auto generate button to create output fields in your database. Access 97 users will need to create the output fields before uploading databases for use in the batch processing.

Step 4) Choose your processing options

After you have uploaded your database, selected the table you want to work, and identified and/or created the fields, you finally select the appropriate shortest path options you want to use and start the process on your database. This is shown in the following image:

Process Options	Description
Calculate Shortest Distance	Perform calculation for shortest distance
Calculate Fastest Path	Perform calculation for fastest path
Output Routes as KML	Generate KML routes from source to destination
Update Mode	Perform calculation only for empty or errored database records
Full Mode	Perform calculation for all the database from scratch
Maximum distance for nearest Nodes	Maximum allowed walking distance to nearest road segment (miles)
Maximum traveled distance	Maximum allowed distance from source to distination in shortest path (miles)
Maximum traveled time	Maximum allowed travel time from source to destination in fastest path (hours)

Step 5) Review your processes

After you select the options and click 'Start', your process is started and added to the list on next tab as in the figure.

Processes in this list will be in one of four states. Processes which are running can be cancelled, paused, and resumed.

Process States

State Description

Running The process is running on your database

Completed The process has finished with your database

Cancelled The process has been cancelled by the user

Aborted The process has aborted because an error occured

Step 6) Download your processed database

Once your process has been finished, you can click on the database name in the processes table to view the results. This brings you to the page shown in the next image from which you can click the download button to save your processed database back to your hard drive.

Output Data Dictionary

The two main output fields are: 'ShortestDistance' and 'ShortestTime'. After the data file is finished processing, these fields will hold the values representing distance and time. In the case that an error ocurrs, the system will output negative numbers instead of times and distances.

The following table provides a brief discription of error codes along with possible reasons and solutions to resolve them.

Error Code	Name	Description
-1	NoRoute	No route found or maximum travel distance reached
-2	NoNearNode	No near road segment could be found
-4	LogicError	There was a logic error on webservice while processing
-8	MAX	Server overloading
-16	Undetermind	Due to some other errors the results are not determined
-32	NullInput	Webservice inputs are either zero or empty
-64	TimeOut	Webservice time out while processing a record. Usually happens on long queries or when the server is low on resources.