Implementing a GIS-T can introduce as many high cost issues as ROI benefits, though.Enterprise systems can slow down work flow, tie up IT resources, introduce non-compatible architectures, and ultimately produce applications that are not adopted by end users.One way of reducing this risk is to limit the project scope and develop an application in stages.This creates a buy-off process that greatly increases the chances of success. Furthermore, by using desktop mapping technologies rather than robust GIS software, a project can stay within the bounds of the specific business goals.Choosing an appropriate cost effective technology such as desktop mapping solutions can be an important driver for success.Ultimately, it is the bottom line that drives a GIS-T project and not the seduction of cutting-edge technologies.Keeping this in mind will help any project succeed.
This article describes a GIS-T application that was developed by Integral GIS in Seattle, Washington under the direction of Airborne Express, Inc. The application was created under extremely tight budgetary constraints so cost effective development was required.The software also had to fit within an established operational environment so tool output needed to be consistent with work-flow and field procedures.It was concluded that Microsoft MapPoint was a superior development environment considering the transportation business requirements and the software's compatibility with Office products.The goals of low cost development, implementation and high user adoption rates were met and contributed to Airborne's successful strategy of creating the highest delivery and pick-up productivity to date. The application provided the necessary data to enable efficient planning of routes. These factors drove the ROI success of this project.
Background and Business
Requirements
Many transportation companies
collect and analyze data to reflect a delivery driver's efficiency in order
to track operational productivity. It would be difficult to manage
truck fleets without indicators of how many miles are driven relative to
stops and items delivered.Transportation and logistics businesses have
an interest in monitoring the high cost of labor, the increasing cost of
fuel, truck fleets, and the potential negative impacts on customer service
when appointments are missed.Therefore, it is understandable that Airborne
Express has the same interest in tracking and improving these sorts of
productivity numbers.
Airborne Express engineers measure numbers like "stem time" (drive time from station to first stop), "stem distance," "on area miles," "on area distance," "total on road time," and "total stop distance". These numbers are used to plan routes, assess driver productivity, benchmark stations, regions and areas against each other, and determine bonus levels for personnel.Many of these measures are determined based on data collected from a driver's scanner.The driver keys in "out for delivery" time when departing and time of first scan is recorded which determines stem time.In general, the larger a station's aggregate stem time, the less efficient are the route structures.Structure is defined by route overlap, geographic area covered, natural and man made boundaries and other factors that impact the relationship between miles driven and numbers of stops and packages delivered.
Certain productivity measures are less likely to be recorded accurately and in some cases they cannot be recorded at all. Take on-area distance as an example. Each driver logs on to their scanner and records "start of shift mileage" and at end of day records "end of shift mileage." Management can then calculate total on area distance. However, these numbers are only as accurate as the numbers entered by the driver.Also, the number does not tell the engineer what the distance should have been given the day's route and whether the driver delivered optimally. Questions include whether the driver stayed on route or extended it for non-work related motives.The station must weigh these issues when evaluating scanner driven productivity numbers. Add to this the fact that union cultures often make matters worse by creating inaccurate data, and once manipulative "tricks" become standard culture it becomes a constant challenge to maintain accurate numbers.Furthermore, half the company stations are set up in a way that ensures much of the data is not made available to industrial engineers and corporate office personnel.This is due to "franchising" arrangements with Independent Contractors (IC) independent contractors that operate numerous stations across the corporation.
Airborne Express contracts half their stations to ICs.The goal of this arrangement is to keep unions from driving up labor costs.Airborne-owned stations are unionized while ICs are not.ICs handle all deliveries and pick-ups while using the Airborne brand.A by-product of contracting is that a great deal of productivity data is not available to the contractor or Airborne.Airborne cannot dictate how the IC conducts its own independent business.This creates holes in productivity data collection for many of these IC stations.This lack of data creates significant challenges to overseeing the contracts and maintaining efficiently operated stations.The development project aimed to reduce some of these limitations.Productivity data and maps of routes help to negotiate contract values and oversee the efficient maintenance of those contracts.
Airborne Express and Integral GIS developed the Stop Density Analysis (SDA) tool to enable Airborne to analyze station data in IC and company owned environments.The software output helps determine route requirements for stations because the tool produces stop density data that was not previously available.Integral GIS developed a customized tool that uses Microsoft MapPoint as the geospatial engine for geocoding, calculating street distances, time in transit, and other functions to create route productivity data.This software uses Visual Basic forms and data access features integrated with other Office products. The end results are spreadsheets that present familiar productivity data and corresponding maps that display each route in a station.As an added feature, the custom application is loosely coupled with ESRI's ArcGIS so more advanced spatial functionality can be used.This provides the flexibility of using MapPoint to do most of the work while allowing for more advanced analysis in the ArcGIS environment.
Customizing MapPoint
Integral GIS used a Visual
Basic 6 form-based interface that utilizes Active X controls with Microsoft
MapPoint, Access, and Excel.The data access features of Visual Basic allow
for data to be used between applications.The application interface provides
the user with access to the various programming controls.This interface
limits the user's decisions and simplifies input of proprietary Airborne
data.An administrative function is also available from this interface
for managing underlying look-up files that are used when running the software.
Visual Basic, MapPoint and the Office suite provide an excellent framework around which to design business functionality.MapPoint has built in streets files for running the transportation algorithms.Shortest distance and the optimization of route functionalities are available.With Office compatibility, it is easy to use different file formats as inputs and outputs.The SDA application had to allow inputting of stop files from legacy systems.These files are downloaded from mainframes in text format and have to be inputted into Access (as part of SDA) to make them available for the various business functions.Many of the productivity measures use distance calculations and time in transit that are calculated using the MapPoint functionality. These are sufficiently powerful for a GIS-T application of this type.
SDA interface -- SDA
loads files into Access based on station ID then makes them available for
geospatial processing.
Integrating Source Files
The two categories of data
files used in the SDA application are: 1) proprietary data; and 2) vendor
application data. The first category includes: a) zip codes with
station identifiers; b) station addresses' and c) delivery and pick-up
files.Vendor application data includes MapPoint streets data and address
cleansing data.To start with, the proprietary files had to be integrated
in the application so spatial analysis functions could be performed on
them.Each file is used for a different reason.The zip code files identify
the areas of station responsibility.MapPoint and resulting maps cannot
zoom on an area without this file.Since each route starts and ends at
a station, the station location must be entered into the application on
each run.Finally, a file of pick-ups and deliveries for a given station
is needed to identify the addresses of each sequential stop.The SDA route
productivity tool loads the addresses for all the stops on a route and
geocodes them as required by the business functionality.
The proprietary data files have to be managed in a database so they can be accessed for analysis and processing. This is done in Access using Active X Data Objects (ADOX). Stop files for a station consist of a delivery and a pick-up file.These files start in text format and the structure of these files is different. Airborne collects different elements for deliveries and for pick-ups.With deliveries, the consignee's name is stored and with pick-ups, the sender's name is saved.To use the two different file formats in SDA, they have to be merged into a common format that utilizes all the fields available from each of the delivery and pick-up files.This is accomplished in Access where the two different files are imported and merged becoming accessible to the other Office functionalities used in the SDA app. This method of handling the different data structures works quite well.
SDA data input sequence
-- SDA merges delivery and pick-up stop files into a common data structure.
Route Productivity Files
The application captures
an historical day's worth of stops and recreates each route from station
to first stop, to second stop, etc, and finally back to station.Times
and distances from stop-to-stop are calculated from MapPoint and outputted
to an Excel spreadsheet so the productivity data is displayed.This is
done for each station route and also provides an average for the entire
station.
When a station is analyzed, the application produces two sets of output files.This includes a digital map book of all routes and a spreadsheet of each route with stop and productivity data summarized at the end of those routes.A map book shows the route corresponding with the spreadsheet so visualization can be accomplished while looking through the spreadsheet data.Historical route maps can have an amazing impact on driver productivity.Just the simple act of showing maps to drivers can be very powerful.Drivers realize their routes are being tracked and this can change their behavior.It is tough to replace maps in this sort of field operations exercise.
SDA output sequence --
A route map and stop details with productivity data for a route.
Summary of SDA 1.0 Development
The development of SDA 1.0
helped produce the highest quarterly and yearly productivity numbers to
date.At time of development there were numerous company initiatives that
marked productivity as a high priority.Obviously these sorts of tools
are only as effective as the end users.By no means would this tool have
been successful without top notch engineers and station personnel.However,
the tool was recognized for its significant help in reducing costs related
to stops across the company.Previous to these efforts field personnel
lacked a mapping and data analysis tool to help them effectively manage
route costs.
The SDA tool has been used to model and predict the amount of hours required for delivery and pick-up stops for a given geographic density.This information had allowed Airborne to effectively project required hours and costs for a given station.The SDA has significantly raised the bar on the ability to proactively plan and evaluate routes.This had company wide implications on the bottom line as fewer routes were needed with delivery/ pick-up times decreasing which ultimately resulted in across the board productivity gains.
The success of SDA 1.0 made
it possible to add functionality to the existing architecture by developing
SDA 2.0. Route level analysis using desktop mapping and GIS tools
added a new dimension to operations analysis at Airborne.SDA 2.0 carried
this further by creating much more in depth route planning tools.These
tools were aimed at further enhancing Airborne's ability to effectively
plan routes in a timely manner.Next week, the continuation of this article
will highlight how SDA 2.0 improved on what we had developed so far.