Calculating Distances between locations

The Distance Calculation action allows you to calculate road or straight line distances between two geographical locations and store the results in a new or existing field. You can:

• Use a filter to define the records to which the transform applies
• Choose the calculation method by selecting the provider
• Optionally select a lookup table for the latitude and longitude, and map the relationship
• Optionally include toll data when PTV is selected as provider
• Apply a circuity factor to calculations
• Optionally, return transit time as well as distance

On the Configuration tab:

1. Define a filter to limit calculated distances.

2. Drag field from Table Fields to define latitude and longitude for origin and destination.

3. Select distance calculation options.

4. Select or add a column for distance and optional columns for additional information such as transit hours.

The most common application is to generate distances based on the latitude and longitude of origin-destination pairs.

Calculate distances

1. Drag the Distance Calculation icon onto the design surface.
2. On the Connections tab , enter a Name and a Description to identify the action, then specify the target and output information:

   Target -

   • For Database Connection, select the database that contains the latitude and longitude data, or choose New Database Connection to establish a new connection to another database.
   • Target Table Name – Select the table containing the latitude and longitude values.
   • If using a lookup table, select Use Lookup Table, and then select the table to be used.
   • Geodata Connection – Visible when the Provider selection on the Configuration tab is Services or Calculation with Service Secondary, this choice allows you to select the connection that contains the key to a valid geodata provider account. You can also select New Geodata Connection here to establish a new connection.
   • Mapset - Visible when PTV has been selected as the geodata connection, this choice allows you to choose the PTV data center from which to receive data. Regional selections include -
     • World City Map
     • Europe City Map Premium
     • North/South America City Map
     • Australia City Map Premium

     The default is World City Map.

   Output -

   This section is optional and enabled for PTV only.

   Create Toll Data Output Table - Use this choice to create a table containing the toll data of your selected mapset region. Select the checkbox, and enter the name of the table to be created. By default, the table is named based on the Target Table name and appended with _tolls.

   You can also:

   • Add a new _autokey field to the table, with incremented values that make each table row unique. This is required by some database types.
   • Indicate that the table should be cleared after the macro or workflow has executed.
3. On the Configuration tab :

   Optionally, use the Filter area to include an expression that more narrowly defines the records for which distances are calculated. Expressions can be created directly in the Filter area, or you can use the Expression Editor.

   Input Fields -

   • If you are not using a lookup table, drag each field to be used for origin-destination distance calculations from Table Fields into the appropriate latitude or longitude box in the Input Fields area. Each field you use must be unique.
   • If you are using a lookup table, drag the origin and destination key fields from Table Fields to the Origin ID and Destination ID fields, respectively. Each field you use must be unique.

     To configure the lookup table -

     • Drag the lookup key field from Lookup Fields to the Lookup ID field.
     • Drag the lookup field for latitude from Lookup Fields to the Lookup Latitude field.
     • Drag the lookup field for longitude from Lookup Fields to the Lookup Longitude field.

   Options -

   • For Provider, choose the method by which the calculations will be made -
     • Calculation - Calculate distances based on a straight line between origin and destination, combined with a circuity factor.
     • Calculation with Service Secondary - When selected, this choice enables the Threshold field, in which a value must be entered. Straightline distances are first calculated between origin-destination pairs, and then examined to determine whether they are below the specified Threshold value. If Bing has been selected as the provider, and the distance between an origin and a destination is less than the threshold, Bing provides the more accurate road distance.
     • Service - Calculate road distances based on data from the provider.
   • Circuity Factor - Enter a value by which straight-line values will be multiplied to obtain a more realistic road distance. The default value is 1.17, meaning that the straightline value is multiplied by 1.17 to determine the distance.
   • Choose a Unit of Measure, either Miles or Kilometers.

   Output Fields -

   • Drag the field that will receive the distance calculations from Table Fields to the Distance box. Alternatively, you can drag the New Field icon into the Distance box, and then either accept the default name of Distance or edit the name for that field.
   • Optionally, specify an output field to indicate the Unit of Measure for the distances calculated. You can drag an existing field the Table Fields, or you can drag the New Field icon and edit the default name of Unit of Measure.
   • Optionally, specify an output field to indicate the Transit Hours, if Service or Calculation with Service Secondary has been selected as the Provider. You can drag an existing field the Table Fields, or you can drag the New Field icon and edit the default name of Transit Hours.

     If the Provider is Calculation with Service Secondary, transit time data is not returned for those records on which Calculation was applied.

   • Optionally, specify an output field to indicate the Provider, or method, by which the distances were calculated. You can drag an existing field the Table Fields, or you can drag the New Field icon and edit the default name of Provider.

Distance Calculation data example

In the following example, distances have been calculated between a manufacturer in Ann Arbor, Michigan and several distribution centers: