Geocoding Locations of NGOs in Sierra Leone
Volunteer recruitments for GISCorps projects typically start with a formal request from an agency in need of GIS volunteer assistance and the project with Craig Bellamy Foundation wasn't an exception. In early 2012, they asked for a volunteer with expertise in geocoding and publishing GIS datasets on the web and the recruitment resulted in deploying 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 their 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 that shows the locations of offices and programs for the over 260 international and national NGOs in Sierra Leone as well as 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 2 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:
Name of NGO
Name of facility
Figure 1: UNICEF program data was provided in spreadsheet format. In this example, the “Chiefdom” column was used to link the UNICEF programs to a GIS dataset of Chiefdoms.
Figure2 : Non-Government Organization (NGO) data was provided as a spreadsheet. Using Google Earth maps as a reference, “Latitude” and “Longitude” fields were added.
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.
or this process, we used Google Fusion Tables, which are essentially data tables that utilize custom display tools through Google Docs that allow 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 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 was 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.
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.
Checkboxes are 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: http://www.blueskiessolutions.net/CBF/CBF_GoogleMap.htmlAuthor:
Michael R Knapp, GISCorps Volunteer
GISP, CTT+, Owner, Blue Skies Solutions LLC, Anchorage, Alaska.