Two weeks after a freak storm dropped more than 15 inches of rain on San Antonio, more than 100 residents from a flood-ravaged neighborhood gathered in an elementary school gym. They wanted to know why the government, at any level, had not told them they were at risk of flooding. Across town, journalists at the San Antonio Express-News already were working on the same deceptively simple questions. Before the swollen creeks had receded, we were asking: "Where did the flood water go?
Was there any pattern to the damage? Did people who needed flood insurance have it? Why not?" Some moderately sophisticated CAR techniques were needed to answer those simple questions. On Nov. 29, five weeks after the flood, we printed the results of the work of a computer-savvy reporter and news researcher. We found: • The path of the destruction closely followed the 100-year flood plains published in 1996.
In fact, 75 percent of the 175 residences destroyed by the flood were in those flood plains. • Citywide, only about 10 percent of the damaged buildings were insured by the National Flood Insurance Program, which meant 90 percent of the people in 470 damaged buildings were trying to rebuild on slim federal grants or disaster loans they could not afford to repay. • Many more houses along creeks on the poor side of town were damaged than buildings along creeks in wealthier areas.
The story demonstrated that you don't need a full-time data analyst or CAR guru to get high-impact stories into the newspaper. From the very beginning, we knew that we wanted to print a map showing the locations of hundreds of properties damaged by the flooding. We quickly decided the way to do that was building, then geocoding, a database of damaged addresses. Geocoding is the geek word for assigning latitude and longitude coordinates to (in our case) street addresses.
We used the software at hand – Microsoft Access for building the database and MapInfo for generating the maps and analyzing the relationship between the flood plains and the locations of the actual damage. The American Red Cross gave us hard copies of their damage assessments, which included addresses, an inspector's opinion of the extent of damage and the estimated depth of the water. We built the database using a form in Access.
Although most of the fields were standard, we devised a clever trick that we recommend for anyone who needs to build and then geocode a database. We copied the streets file out of our MapInfo software and imported it as a table into Access. We cut out all the columns except the street names. Then we deleted duplicate names, which whittled the table down from 87,000 to 20,000 rows. Within our data-entry form, we linked the street address field via a combo box to our new table of street names.
The computer would recommend the proper spelling of a street name after we typed its first two or three letters, which saved time and ensured that the street names in the database would be exactly as they appeared in the MapInfo file. Having street names in a database that don't match those on the streets file of the GIS software is frustrating. The computer can't tell the difference between "Villa Main" and "Villamain." When we were done, we could generate a map of red dots for the 470 buildings damaged in the flood.
With MapInfo's query ability, we could sort out homes, businesses, apartments and mobile homes. The missing piece was the flood-plain data. That was easy. We bought it. The MapInfo Web page listed a company, On Target Mapping, that sold digital maps from the Federal Emergency Management Agency for about $200. From there, we printed a large map of the city and several smaller, blow-up maps of the damaged areas for the art department.
We had difficulty outputting maps into a format our art department could import on their Macs. So, the artists scanned the images into their graphics software, then retouched them. Once the data wrangling was finished, the story became a matter of traditional reporting. We interviewed dozens of people, such as the mother who lost everything in the flood except five dresses and two school uniforms for her son and the city public works director who claimed he told several unheeding neighborhood groups about the dangers of flooding.
After the story appeared, several members of the San Antonio City Council pledged to force the city to notify by mail everyone who lived in a flood plain about their risks of flooding. The council also embarked on a multi-million-dollar program of buying out properties in the flood plain that were damaged by the flood.