Roll Your Own Commercial Routing System with MapPoint
Matt Fritz shares his experience developing a routing application including discussion of MapPoint's strengths and limitations. The articles includes several screenshots illustrating the various components of the routing application.
Everyone is familiar with vehicle routing from point A to
point B, perhaps applying additional criteria like ‘highway or scenic’, routing
around known construction areas, and perhaps inserting a stop (waypoint) or two
along the way. But suppose you needed to route 100 vehicles to 3000 locations
where the list of locations changed daily, many locations had a ‘time window’
dictating an acceptable arrival time, the amount of time spent at each waypoint
varied, and the total duration of each route needed to be no less than 8 hours
but no more than 8 ½ hours.
Commercial vehicle routing adds a bit more complexity to the
routing problem. Solving this problem through software as been the elusive
brass ring, in part because there is no ‘right’ answer – only a set of ‘best
compromises’. Commercial routing systems usually offer a toolbox rather than a black
box solution, (regardless of marketing claims to the contrary). Commercial routing
is often a partnership of tools and human intervention.
MapPoint 2006 offers many of the core tools needed for this
kind of routing – many but not all. That’s no surprise because MapPoint has
been built and promoted more as a business intelligence tool to render graphic
representations of business data. For example MapPoint 2006 doesn’t support
multiple concurrent route creation from a pool of thousands of waypoints or
routing around streets and highways designated ‘no commercial traffic’. (You
can solve these issues on your own (kind of) but they’re not supported out of
the box. Let’s see what the next version brings).
When charged with writing a commercial routing system for a
wholesale distributor I employed the MapPoint 2006 map control and extensive
API into a VB.NET application. This enables the application to deliver core
routing functionality, including:
* Geocoding – automatic address matching and manual locating
* Calculating route/stop distance and duration – estimated arrival/departure times
* Performing ‘perimeter searches’
* Employing custom pushpin icons to indicate customer delivery criteria at-a-glance
* Employing pushpin balloons to display order information at-a-glance
* Providing a good map experience at low cost
Note for this application the routes are not created by
MapPoint but rather using a collection of drag/n drop tools. MapPoint is then
employed to calculate data about the route and display routed and un-routed
customers on a map.
Batch Geocoding Customer Addresses with MapPoint and
The foundation of any routing system is accurate geocoding. The
quality of customer address data can be anything from ‘OK’ to dismal (rarely is
it good) and address matching itself is part science – part fuzzy logic. When
attempting to geocode many thousands of customers at one time (like for an
initial data load) the results can be disappointing and even misleading.
MapPoint returns a ‘result quality’ value to rate the result of an address
match but even a high confidence result doesn’t mean you’ll like every location
it chooses. Manually confirming the locations chosen by MapPoint for 10,000
locations would be an arduous and error-prone task.
For our initial batch geocoding of all customer
addresses we opted to use an on-line geocoding service from Tele Atlas called EZ-Locate.
It also returns a ‘results quality’ for each match plus the lat/long. We
accepted the lat/long into our database for all results with the highest result
quality value. (We experienced a 90% percent good match rate – your mileage
may vary). All others where flagged as ‘not geocoded’ for the end-users to
manually locate using our routing application with embedded MapPoint tools. The
manual process took less time than anticipated and served as a good learning
exercise for the end-users.
Geocoding New Customers with MapPoint
New customers are initially flagged as ‘not geocoded’ which
tells the routing software to geocode them using the MapPoint API. Again the
application automatically accepts high quality results and leaves remaining
customers flagged as not geocoded for the end-users. The difference here is the
small number of new accounts each day which makes it easy to verify or manually
Manually Locating / Relocating and Updating DatabaseLat/Long
Geocoded Customer Records with Lat/Long and GeocodingSource
A Proximity Search Function (Who’s Nearby) Helps with Manual Geocoding
The routes are manually created using drag/n drop tools. Once
created the route locations (route start, route end, and waypoints), and the
waypoint duration times are passed to MapPoint – not to change the sequence of
waypoints but rather to generate data about the route passed to it, including
arrival times, mileage, and total route time, including to and from the warehouse
depot (the real first and last stop).
We couldn’t let MapPoint create the actual route for several
reasons including lack of support for roads designated ‘no commercial traffic’.
However this turns out to be a non-issue because the distance and elapsed time
between two locations is approximately the same regardless of which streets are
used. Considering other unknowns like traffic and weather makes the whole issue
moot. Arrival times are approximate.
MapPoint Waypoint Arrival Times and Mileage Calculations
Commercial routing and related data like customers and sales
can generate tons of historical data and MapPoint is a great BI tool to render
that data on a map. My application didn’t take advantage of MapPoint’s BI abilities
but yours can, including combining your business data with demographic data
from MapPoint and third party sources. An image can be worth a thousand reports
to logistics, sales, and other interested parties. (The image below property of
Commercial vehicle routing is an interesting and complex
task that is usually supported by commercially-available packages but it’s possible
to write your own system customized to your needs. MapPoint can provide
invaluable functional support and professional polish at a very reasonable cost.
There’s nothing to it but to do it!