I accomplished this task very manually via the following method:
- Downloaded membership data from MLS
- Imported membership data into Google earth
- Manually subdivided the ward by eyeballing clusters of members on the map. Used custom markers to assign members to different regions.
- Exported the KML file from Google Earth into Excel
- Performed filtering to break out each district
- For each district, batch geocoded the addresses of all the members
- Ran TSP Solver to produce a least-distance route
- Finally, sent each district leader a sheet with a list of families and a map (see attached for example)
This process took about 6-8 hours the first time I did it. I've been re-performing this process every 3 months to incorporate move-ins and move-outs. This maintenance takes about 3hrs per.
I think there is an opportunity here to build and awesome tool that would streamline this process and which could be used by wards all over the World. Here is my vision of what this tool would look like:
Ideally, the tool would be built into MLS so that it could pull membership data automatically. More likely, someone would need to do a dump each week and upload into the tool.
The tool would show a map of the ward with all members' home plotted. The tool would then have markers labeled 1-X that could be assigned to each member representing district 1-X. There would also be markers 1.1-X.X representing district leaders. When all members and district leaders have been assigned a marker, the tool automatically generates the district lists. There should be an option to print these off or email them to the district leaders.
When updated membership data is provided to the tool (either auto or via manual upload) the tool should determine move-ins and move-outs. Move-outs should automatically disappear from the map. New families should be plotted, but left unassigned to a district. Someone will manually need to make the assignment using a marker. The tool will then show which district have been changed and provide and option for printing or emailing.
Originally, I was trying to come up with a fancy algorithm to automatically assign families to districts as a function of distance, load balancing, etc. But this became overwhelmingly complex. So whatever the solution ends up being, there will probably need to be a manual component where someone makes the actual assignment. This tool would just make the process a whole lot easier.
On a side note, it would be nice to incorporate into this tool a DB that tracks other information that could be pertinent during an emergency (e.g. special needs, inventory of equipment, tools, etc).
Someday I hope to have the time to code this myself. Until that time, I guess I'll stick with our manual process.