Zones: It's the Geography, Stupid!

Transportation is all about geography and the challenge with Truckmate is to structure the Zone table so that you can easily and accurately track the physical location of all equipment, people and freight. At the same time, this table must also respond to the search requirements for dispatch, sales and data analysis using a single hierarchical structure.

Canadian Postal Codes are excessively granular and would flood the zone table with over a million records nationwide. We compressed them to only define every combination of postal area (first three characters) and city/town. On the other hand, U.S. 5-digit zip codes are often not granular enough - especially out west - to provide geographic precision, so adding extra zones for larger cities within a zip code helps. Truckmate's zone validation utility will identify which points can actually be mapped and used for mileage calculations. Our final count to cover the U.S and Canada was about 60,000 valid, geo-coded rows.

The hierarchy we built on top of the geo-located zones for searching and grouping has the following levels in the U.S. and Canada;

1. Country
2. Region
3. Province/State
4. Areas
5. Larger city (having more than one postal area)
6. Postal area (first 3 characters of the postal/zip code)
7. Smaller city (within one postal area but having more than one postal/zip code)
8. Geo-coded postal/zip codes with extra city entries for large U.S. Zip codes

To implement these changes we executed the following steps;

•  Import and clean postal/zip codes to level 8 from the most recent data available on the web (see https://www.dropbox.com/sh/yl0ufaelxh7rce5/E_WFuhXXi3/Postal) using IBM-DB2 data import utilities.
• Generate the summary zones for the hierarchical levels above.
• Validate and locate (geo-code) all zones against the mile server via Truckmate utilities.
• Delete any level 8 zones that weren't valid
• Remove any validations and geo-coding generated for levels 1 through 7
• Add triggers to prevent use of non-geocoded zones (i.e. Level 1 through 7) in Order Entry and Dispatch

It was a difficult undertaking which required about 6 weeks of concerted effort to implement, but now our mobile zones are precise, search results are reliable and pay rules are applied more consistently.