This post, replicated in a ReadMe file, introduces a system of two related script files - GeocodeFromPalmFamily.vbs and MakeKML.vbs.
The overall objective is to read files extracted from the church MLS system, geocode families for a ward or other unit, and produce several KML output files rendering Folders and Placemarks for loading into Google Earth. The basic file shows all families in a given ward or unit. Optional files also show home-teaching and visiting teaching companionships and assignments.
These KML files, like their underlying source data, are intended for church use only. They should be protected from unauthorized use, and not stored on third-party servers. The content of the basic families map contains no substantive data that is not already downloadable by authorized ward members from the Local Unit Web Sites on LDS.org, so in my own opinion it is suitable for dissemination to ward members. The home- and visiting-teaching files are intended as a tool for leaders in the bishopric, quorums and Relief Society, as well as clerks. If in doubt, follow the guidance of your bishop or other priesthood leader.
The functionality is split into two scripts for good reason: It is better to separate the geocoding step from the KML-rendering step because geocoding is not a simple process that can just be run blind, and the address data is imperfect. Almost certainly there will be ambiguities, errors and warnings to deal with between the initial geocoding process and the use of those lat/lon coordinates to produce accurate maps. Garbage In, Garbage Out. Most often, address errors are best dealt with by correcting them upstream in MLS, then exporting the data again.
In addition, very likely there will be exception addresses that fail to be geocoded satisfactorily by the primary geocoding provider, but which might be geocoded by another provider.
This editing functionality differentiates this system from some batch-geocoding scripts that ignore warnings and geocode some records inaccurately (such as at a zip-code centroid), and exclude other records without warning.
Overall, the process works like this:
1) Extract the file(s) from MLS. This requires administrator access to the MLS system, and obviously should be authorized by the bishop. To produce only a simple map of all families in the unit, only one extract file is required: PalmFamily.csv. To produce the optional files for home and visiting teaching, three other files are required: HomeTeaching.csv, VisitingTeaching.csv, and Membership.csv. All those files must be extracted at the same time.
2) Store the extract file(s) in a dedicated working folder, which will also contain its output KML files and several intermediate files and temp files in csv format, all with column headers and quote-marks to delimit strings. Logically, all these .csv files comprise a relational "database," and really ought to be stored in a proper SQL back end. Since I have not provided one, the scripts juggle the .csv files directly. Yuck.
3) Run the GeocodeFromPalmFamily.vbs script, obviously with Internet access to hit Yahoo.
The script produces a fresh copy of main output file called GeoDB_Households.csv. This file should not be edited, but may be carefully browsed in a spreadsheet such as Excel. (In a future version I might make it read-only.) In addition to the geocoding results from Yahoo -- lat/lon coordinates and standardized addresses -- the script captures three fields that describe the success, failure and uncertainty of each geocoding transaction.
4. The script also produces three intermediate files: GeoDB_GeocodeExceptions.csv, GeoDB_GeocodeWarnings.csv and GeoDB_MissingAddresses.csv. Often the best solution is to correct the addresses upstream in MLS, then export again.
4(a) The exceptions file contains records which Yahoo failed to geocode, or which failed to meet the quality threshhold I have set, despite your best efforts to fix the addresses in MLS. Included in this file are addresses where the street address or street could not be located at all, or which Yahoo only geocoded as the centroid of a zip code, city or state.
I strongly recommend that the user stop and try geocoding these exceptions interactively from a secondary source such as Google Earth, paste the lat/lon into the appropriate fields using a spreadsheet, and save the file again in .csv format. The script is designed to preserve and maintain these exceptions for future use in this database file, so you should not have to repeat this work again for the same addresses the next time you do an update.
4(b) The GeoDB_MissingAddresses.csv file is a report of records that have missing data in one or more of three principal fields -- street address, city or state. (Sometimes the content is present but is in the wrong fields, in which case Yahoo might recognize it anyway and geocode it.) In most cases, of course, these egregious errors are not geocoded at all.
4(c) The GeoDB_GeocodeWarnings.csv is a report of records that have other problems in the address fields that Yahoo had to guess at -- typically misspelled streets, missing directional indicators (N, S, E, W), etc. In my testing, I found that Yahoo usually guesses right. So unless you take steps to the contrary, the scripts will map these addresses as Yahoo suggests.
But sometimes, Yahoo guesses wrong. and substitutes the wrong address . So the addresses get geocoded at the wrong coordinates. Unless we fix such errros upstream in MLS, they will occur and recur. So the right solution is to fix the errors upstream, which should happen anyway because MLS should not contain erroneous addresses, geocoding or no geocoding. Clerks need to get the data right, and this process is a useful tool for finding potential errors in member addresses.
5. After dealing with errors and exceptions, the user is ready to produce the KML files. Run MakeKML.vbs.
The output files will be named according to the unit name entered interactively by the user. There will be a general <UnitName>Families.KML. If the requisite input .csv files exported from MLS are present in the database folder, there also will be a <UnitName>HomeTeaching.KML file and a <UnitName>VisitingTeaching.KML file. All these folders are date-stamped internally according to the date the underlying data extracted from MLS, and these dates are visible within the Google Earth folders.
5(a) At runtime of MakeKMLvbs script, the user has the option to include on the map those records that could not be geocoded, by choosing a default location for these ungeocoded orphans. This is especially helpful in the home- and visiting teaching files, where it may not be useful to leave an assigned family off the map just because it has a bad address. (Or the user may choose to leave them off the map entirely.)
The user is encoouraged to select a meaningful default site such as their meetinghouse or a local landmark, and store its coordinates in GeoDB_DefaultLocation.csv. If the user has not stored such a location but would like to map the orphans anyway, the script will estimate and generate a default site slightly apart from the cluster of geocoded members.
6. The icons for placemarks in the KML files are color-coded: Families in the general file are green. In the home-teaching file, High Priests and their assigned famlies are green, Elders and their families are blue, and unassigned families are orange. Visiting teachers and their assigned sisters are pink, and unassigned sisters are orange. The shapes of the icons also varies: Families and sisters assigned for teaching are pushpins, home teachers are man-shaped figures and visiting teachers are woman-shaped. If a family or sister is mapped at the default location, that icon is an inverted teardrop "paddle" shape.
Within Google Earth, the placemarks also can be navigated within folders displayed in the left pane. The home- and visiting teaching folders are organized as hierarchies -- quorum or auxiliary, then district, then companionship, then individuals. They are often best browsed by checking/unchecking folders to allow companionships to be isolated for display. It is even possible for the user to cut-and-paste interactively from one folder to another in what-if fashion to adjust companionships by geography.
7. Lather, rinse, repeat as your ward roster changes. In my huge ward, with many apartment-dwellers and a lot of turnover, I plan weekly updates. YMMV.
Sorry this is so complicated. It is complicated world. I don't think anyone will be happy for long, once the novelty wears off, with batch-geocoding solutions that don't confront the problems of getting the geocoding right upstream and maintaining this data over time. Producing maps on-the-fly directly from address data just does not work as well.
The scripted user interface is pretty crude -- serialized prompt-response dialog popups. I know that all this complexity really belongs in some application, with a decent GUI and a bulletproof SQL back end. Maybe someday I'll write one. But maybe the church developers will render that unnecessary. Ideally, this geocoding functionality will be integrated into MLS someday soon, driven by commercial-grade geocoding and maintained by ward clerks within the MLS database, and the officially correct coordinates will be available for export.
Meanwhile, here is v 1.0.