3/11/06

Summer Food and CACFP Sites on a Google Map

Two websites -- Batch Geocoder and Mapbuilder -- provide quick ways to put Summer Food and Child and Adult Care Program sites on a publicly accessible web map. There is no charge for these services.

1. Batch Geocoder  geocodes thousands of addresses and maps up to 100 sites at a time on the new style Yahoo maps. The map can then be saved for instant online viewing, hosted by Batch Geocoder. Click on a marker's info window and you retrieve driving directions on a Google map. The geocoder also automatically generates a .KML file for importing into Google Earth. Unfortunately, there is no way to edit erroneous geocodes on the saved Batch Geocoder map, other than modifying the original addresses and doing a rerun, e.g. change to 7th and Main instead of  727 S. Main.

Here is a Batch Geocoder map  using 2004 Summer Food Program sites for Multnomah County, Oregon .(all sites geocoded, only one site displays on the map where there are duplicates). The geocoding and mapping process took less than a minute.

* * *

2007 Update:

You can view a Google Earth KML/KMZ file created from Batch Geocoder in Google Maps by entering the URL for your KML file. Click here for a map of 240 sites in Washington DC linked to a .kmz file.

The URL is created by pasting http://fairplan.u31.infinology.net/SFS/DC_2004_sites.kmz  into the search box at Google Maps.

This map (or another one that you create) can be saved to Google My Maps and then displayed as a small map on your website.

You can also create a KML file directly from a Google template for Google spreadsheets. See Google's spreadsheet mapper tutorial.

For most purposes, the Google speadsheet method is a better approach than using the xml file technique described below. However, be sure to take note of  the geocoding accuracy paragraph in the next section.

* * *

2.  Mapbuilder.net  geocodes thousands of addresses and maps 300 or more sites at a time on  Google maps -- but there is a learning curve. The map can be hosted on the Mapbuilder site or on your own.  You must get a free account and then you can access this page,  http://www.mapbuilder.net/import/, which explains how to upload and geocode a list of addresses starting with an Excel .csv file. You can add and delete sites using map tools. 

Here is a Mapbuilder map for Multnomah County's 2004 sites (all sites geocoded, only one site displays on the map where there are duplicates, imprecise addresses are not displayed).  Here is the same map hosted on the Mapbuilder site.

Geocoding Accuracy

Both Mapbuilder and Batch Geocoder use Yahoo's geocoding service. In some instances, an address match cannot be found and the site is assigned to the center point of the  zipcode or city. These imprecise  geocodes can be identified by geocoding your list a second time based on city and zip only. Coordinates in the initial set of geocodes by address are not precisely geocoded by address if they match coordinates returned for the city/zip geocodes (generated at step 6 on the Batch Geocoder form) A quick way to spot the imprecise addresses is to copy this second list of coordinates into the spreadsheet and subtract them from the first set. City-only or zipcode-only matches will equal 0.00000000..... You can make edits directly to the Mapbuilder map using the add/delete tools -- or use the spreadsheet technique described below.

Interfacing with the FairData Map

The steps outlined below describe how to create a Google map showing Summer Food sites (hosted on your website) with a menu link at the top to the FairData census boundary map. Here is an SFS interface map (SFS_base.html) for Multnomah County. Click on a marker and you get site info and can obtain driving directions. Click on the menu item "FairData" and a new map window will open that shows the census theme. The clickable side bar in this instance is sorted by city and site. Here is a basic Google SFS site map (SFS_map.html) without the FairData link.

(1) Copy and paste columns A thru G of this Excel spreadsheet  into the Batch Geocoder form at step 2.

(2) Copy and paste the geocoded results back into the Excel spreadsheet from the Batch Geocoder form at step 6. Note that the latitude and longitude coordinates are in columns H and I.  Coordinates geocoded to the city or zip level will need to be modified to show the correct site location (see below).

(3) Copy and paste column K in red (a formula)  into a text-based .xml file.  (Be sure to copy the formula to rows beyond 105 if you have additional sites.) You can use a simple text editor like Windows Notepad to create and save the .xml file. The first line of the .xml file must read <markers> and the last line must read </markers>. Click on the .xml file with your browser to determine if it is formatted correctly -- if not, see additional details below.

(4) Replace the key number in the SFS_base.html file with your API key. A free Google API license key can be obtained here.

(5) Copy the SFS.xml  file (the geocoded addresses) and the SFS_base.html file (the map) into the same web directory. The .html file should produce the interface map (with your sites displayed).

(6) Select "Save View" (above the map) to create a new URL centered on your area.

Additional Details on  the .xml File

1. The .xml file will not load if ampersands -- & -- or slashes are in addresses or names.

2. The .xml file will not load if a cell  in the Excel spreadsheet is left blank, because that creates an error in a formula cell in Column K on the spreadsheet (containing the data for the .xml file.) Just insert an apostraphe -- ' -- in the blank cells.

3. If you need to modify the Column K formula, note that line breaks (for the info window display on the map) are represented by  "&lt;br&gt;"

4. The first line of the xml file,  must read
<markers>
and the last line must read
</markers>

See also Mike Williams' excellent Google Maps API Tutorial.

 Fixing Erroneous Geocodes

The Google maps in these examples have not been edited to fix erroneous geocodes. To make corrections using the FairData map:

(1) Double click on the true location for a site that did not geocode properly.  The point will automatically shift to the center of the map. ( If you prefer a visual marker, the menu item "Center Point" shows a balloon marker centered in a new map window.)

(2) Copy and paste the center coordinates  for the site (shown above the map) into Columns H and I in the Excel spreadsheet. 

(3) If two or more sites share the same address, you'll need to slightly change one of the coordinates so that they display as shown here, where the latitude for one site was changed from 45.7116 to 45.7146.

(4) Create a new SFS.xml file from the Excel spreadsheet and upload this corrected version to your web map directory.

Load Time and Display Speed

Google maps load too slowly in Internet Explorer 6.0 once you go beyond about 100 sites. There are programming techniques to work around this problem, but the easiest solution is to create separate maps for different areas -- preferably under 50 sites per map. Create different web directories for each map and you can continue using  the file names SFS.xml and  SFS_base.html.

For a speed check, this map displays 245 sites for Washington DC  (2004 data) -- a half dozen or so did not geocode and there are some duplicate coordinates. With a broadband connection, the map loads very fast with Firefox and not so fast with Internet Explorer 6.0.  This statewide map showing over 400 SFS sites in Oregon works fine using Firefox and cable Internet (2004 data, sorted by city in the sidebar).

CACFP Templates

Here are maps that work for CACFP sites (SFS.xml is still used, so Summer Food sites are shown)

Google CACFP site map

Google CACFP interface map