Volunteer recruitment for GISCorps projects typically starts with a formal request from an agency in need of GIS volunteer assistance, and the project with the Craig Bellamy Foundation was no exception. In early 2012, the foundation asked for a volunteer with expertise in geocoding and publishing GIS datasets on the Web and the recruitment resulted in the deployment of Michael Knapp, a GIS specialist from Anchorage, Alaska. He recently completed all the tasks and launched the application in early April.
Sierra Leone, in West Africa, is a developing country that is still in the process of recovering from a devastating civil war that lasted 10 years and ended in 2002. The Craig Bellamy Foundation offers children in Sierra Leone the chance to learn and grow through sporting opportunities that the children would not otherwise have. Through its work across the country, the Craig Bellamy Foundation realized that it would be beneficial to all non-government organizations (NGOs) to have access to an interactive map showing the locations of offices and programs for the over 260 international and national NGOs in Sierra Leone, as well as the over 200 UNICEF program locations. Until now, all this information had only been available in spreadsheets that were useful, but did not provide any spatial context.
Step 1: Geocoding
At the onset of the project we received two spreadsheets. The first contained a list of 265 NGOs operating across Sierra Leone. The second spreadsheet contained 229 UNICEF programs in the country. The success of this project depended on the ability to adequately geocode locations from the information provided in these source spreadsheets. The location information contained in the source spreadsheets fell in to one of the following categories:
- Street address
- Community name
- Chiefdom name
- Name of NGO
- Name of facility
Figure 1: UNICEF program data were provided in spreadsheet format. In this example, the “Chiefdom” column was used to link the UNICEF programs to a GIS dataset of chiefdoms. (Click for larger image)
Figure 2: Non-government organization (NGO) data were provided as spreadsheets. Using Google Earth maps as a reference, “latitude” and “longitude” fields were added. (Click for larger image)
The geocoding process for these locations within Sierra Leone was limited because only the largest cities (e.g., Freetown, Bo) have street data, and none of the street data had associated address ranges. As a result, each NGO / UNICEF program with a street name that could be identified in Google Earth was placed as a point somewhere along the street segment, but could not be placed at the correct address number location along the segment.
If no street names were available in Google Earth, the NGO points were placed at the location of the community name marker. In some cases, only an NGO name or facility name was provided in the source spreadsheet. In this event, an Internet search was performed to see if a community name or street name could be identified for these sites to assist with geocoding.
Many of the UNICEF programs were linked to a location through chiefdom name. To assist with geocoding this information, a shapefile of Sierra Leone chiefdoms was obtained via http://www.geocomm.com. The spreadsheet of UNICEF programs was then joined to this shapefile via an attribute field in the shapefile containing chiefdom names (after some adjustments were made for spelling inconsistencies).
Using this process, approximately 485 of the 494 NGO and UNICEF program locations were geocoded to a street name, community name or chiefdom name. The geocoding was, by far, the most time-consuming part of the project.
Step 2: Creating the Fusion Tables
With the NGO and UNICEF program data spreadsheets now containing “latitude” and “longitude” fields, or a chiefdom name that could be linked to a shapefile, the next step in the process was to create the spatial data that would be accessed via the Google Map API.
For this process, we used Google Fusion Tables, which are essentially data tables that utilize custom display tools through Google Docs, which allows relatively simple creation of charts, timelines and maps that are easily publishable online. In terms of Web mapping, Fusion Tables are advantageous because they can be easily translated (using the latitude and longitude fields added to the spreadsheets) into spatial data. They also have the advantage of being immediately available for structured query language (SQL)-type queries without additional coding (as opposed to KML files, which require either server-side or client-side parsing in order to be queried). And finally, Fusion Table spatial data can be “layered” for drawing order (as with the layers in an ArcGIS data frame), whereas KML files cannot be rendered in a controllable drawing order.
For the UNICEF program data (which had chiefdom names), the spreadsheet was joined to the chiefdom shapefile in Esri ArcGIS, and then exported as a KML file. The KML file was then uploaded to Google and converted directly into a Fusion Table.
Figure 3: Each spreadsheet of point locations and a KML file of chiefdom polygons were uploaded into Google Docs and converted in to Fusion Tables.
The Fusion Table interface also allows for custom HTML coding of the Google Map API balloon-window, which opens whenever a user clicks on a feature on the map.
Figure 4: Custom balloon windows were created using HTML and references to data fields within each Fusion Table. Balloon windows open whenever a user clicks on a feature in the final map. (Click for larger image)
Step 3: Creating the Interactive Map
Figure 5: The final interactive map displays each of the Fusion Table layers and allows for searches by NGO name and filtering by UNICEF program types. (Click for larger image)
Checkboxes were used to toggle layers for display. Custom queries are possible through the interface using a search box, as well as radio buttons, which execute a custom SQL query.
The final page is viewable at: www.blueskiessolutions.net/CBF/CBF_GoogleMap.html