I’ve developed several database tools at the AP. In this project, I built a tool to aid reporters in geolocating the hometowns of US military personal killed in action in Iraq and Afghanistan.

AP’s research department, NIRC, tracks US casualties compiled from Department of Defense press releases; unfortunately, the DOD data on military personnel’s hometowns is not always complete or accurate.

In order to create a national map showing hometown locations, it was necessary to link up the NIRC database with a gazetteer that could provide mappable coordinates and other location demographics. Three GIS databases were used and contained nearly 2 million US “places”.

Most hometown locations were found automatically, but a high percentage of locations had incomplete or conflicting matches in the gazetteer. A tool was created on top of the database to allow staff to find problematic entries and then find the closest match. Most entries were repaired by fixing common misspellings or standardizing name conventions, such as changing “St. James” to “Saint James,” or by eliminating multiple matches by looking for other unique criteria in the DOD provided data, such as a home county.

The database tool saved these remappings separately from the original NIRC database. This allowed a complete audit trail to be created and made it easy to update when more DOD data came in.

The final data set of geolocated hometowns was plotted in ArcGIS and used in this graphic.