We have often been asked if CDXZipStream can apportion a large number of destinations into individual routes, then optimize the order of stops along each of these routes. Unfortunately the route optimization function of CDXZipStream, which relies on the use of Microsoft MapPoint, is not sophisticated enough to do this kind of calculation. The vast number of permutations that could occur here requires a lot of computational muscle, and if there are rules (such as geographic constraints) that must be followed in apportioning the destinations, these must be programmed into the selection process as well.
But this is also a case where the human eye and a little brain power, with some help from Microsoft MapPoint, can compete pretty well with even the more sophisticated (and expensive) software. The trick is to be able to quickly and easily visualize and manage the data, which is right up MapPoint’s alley. So let’s get started.
Let’s say we have a list of 100 destinations, which we need to divide into 10 routes (of about 10 destinations each), followed by optimization of each route. First we import all the destinations into MapPoint using the data import wizard available from the “Data” menu on the MapPoint toolbar:
From here you can then select the Excel file that contains the destination list; you can also select other file types here such as text or Access database files. After selecting the desired file, you will also be asked to select the appropriate worksheet (assuming the file was an Excel workbook). Then click “Next”, and you’ll see a MapPoint dialog like this:
You can also choose to import data beyond just address information, which may be useful if this data is part of the decision-making process in apportioning routes. (You can display all information associated with a destination by right-clicking on it later on the MapPoint map.) Click on “Finish”, then select the pushpin map type in the next dialog:
Once the data is displayed as pushpins in MapPoint, you can use MapPoint drawing tools to apportion the data into routes. The freeform tool was used to draw the area below:
You may also want to try the radius tool so you can specify the radius distance of the area. Once an area has been drawn, right-click on the area boundary line and select “Import to Excel”. You’ll immediately see all the points within the area in an Excel spreadsheet, ready to optimize with the CDXZipStream function CDXRouteMP. You can also cut and paste these points into our Route Optimization template, available for free download from our links page.
This technique may not be appropriate in cases where there are many criteria for apportioning routes. In the case above, we used logical boundaries to define the route area, such major highways and the river to the northwest. (This is very easy for us humans to do, but can be pretty complicated for a computer.) As mentioned previously, you can also import other data into the spreadsheet which can be used as criteria in apportioning. This data can be displayed by right-clicking on a destination on the map, or even better, can be used to define the appearance of the destinations when selecting the map type, by selecting options such as color-coded pushpins or sized or shaded areas.
Keep in mind that before optimizing each defined route, you may need to modify the beginning and end point destinations. CDXZipStream assumes these points are fixed and only optimizes the order of the intermediate points. In the majority of cases this assumption works, but you may also want to add one or more addresses that represent the true beginning and end points if they fall outside the area you just defined in MapPoint.
For more information on using CDXStreamer for route optimization, please watch the following tutorials:
Route Optimization in Excel
and Route Optimization with One Click