ARTICLES

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 Other Sources

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 locate.

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

Route Creation 

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

Business Intelligence

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 Microsoft).

Summary

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!

	MP2Kmag Internet