Housing prices on rise
By Matthew Waite St. Petersburg Times
Imagine buying a house for $60,000. Over three years you fix a bathroom, paint a few walls and put in some shrubs. And then you decide to sell it. Maybe you've heard that other homes on the block were selling well, so what the heck. And, on a whim, you make more in profit than you spent on the house in the first place.
Luck? An isolated case? Hardly, a St. Petersburg Times analysis of home sales found. The Tampa Bay housing market is in the midst of a transformation that has affected many bigger cities: the price of a home is skyrocketing, changing neighborhoods house by house and, in our case, greatly eroding the area's reputation for cheap housing. In 1998, the median price of a house around Tampa Bay was the same as Wichita, Kan. Now, it's the same as Dallas, a larger city with more wealth.
To get at this change on a neighborhood level, we largely relied on geographic information systems (GIS), in this case, ESRI's ArcView 3.2 and ArcGIS 8.3. We obtained databases of home sales from the property appraiser's offices in our five-county circulation area, ending up with more than 260,000 home sale records. We also got parcel map files from each county's appraiser. In most cases, the appraisers - elected officials - were happy to provide the data or maps free of charge. Others had standard charges, and just who had them seemed hit or miss. One county gave us maps for free, but charged us for data. Another county gave us data for free, but charged us for maps. The most expensive was $85, from one of our rural counties.
With the sales data and parcel data, we then turned to the problem of analyzing the data at the neighborhood level. Some cities have GIS departments, and gladly turned over GIS boundary maps of neighborhood associations. But our area includes large, heavily populated unincorporated areas, with no defined neighborhood associations, other than developer-created subdivisions that were too small for meaningful analysis. So we created our own, using ArcGIS. We started with a Census Designated Place map - which provides reasonable boundaries for cities and areas and went from there. With help from editors, reporters and maps, we drew more than 100 neighborhoods onto our main map. In the end, we had more than 300 neighborhoods, towns, subdivisions and unincorporated areas in our analysis and maps.
That allowed us to dig much deeper than we could have with available reports from the Realtors' associations. We could compare cities, towns and neighborhoods from one to another. We could look at how one hot neighborhood spills over into the ones around it. We could get very, very local for our readers.
The GIS portion of this, while key to the whole project, wasn't particularly difficult. I used ArcGIS to perform spatial joins, which assigned our neighborhood names to parcels based upon where the parcel was located. Then, with the names joined to the parcels, I could import the parcel table into Microsoft Access and join the parcel table to the sales table. Then, with a neighborhood name given to a sale, I could group the sales by the neighborhood name and then calculate median sale prices by years using SPSS, a statistics program. I then cleaned up the median output SPSS created in Excel and calculated percent changes. By the end, I used almost every software tool in my bag.
Since the Times is heavily zoned, the idea from the start was to give each zoned edition a story specific to its area. In the end, we published about 30 stories over two days, 25 of them on one Sunday. For anyone wanting to attempt this kind of story, some advice:
Brace yourself (part 1): In the beginning, this didn't sound like an enormous undertaking. It is. I promise you. The folder where this project resides on my hard drive comes in just under 8 gigabytes, which does not lend itself to easy management.
Brace yourself (part 2): Be prepared for what you're going to get from the property appraiser offices. Some are using Access and ArcView, making everything easy. Some use arcane data management software and unheard-of mapping applications. I was able to get maps and data from them, but it makes a great case for having both Arc 8 and Arc 3 on a machine. Arc 8 relies on a map layer having a projection assigned to it. Arc 3 does not. Data from one of our counties came from a computer-aided drawing system. The parcel file wasn't a collection of polygons - it was a giant collection of hundreds of thousands of lines that seemed to connect, but didn't. I could see what the parcel map was supposed to look like, but it was worthless to me as an analysis tool. The data, however, contained an X/Y coordinate that corresponded to the center of the parcel it was related to. To assign a neighborhood name to a parcel, I did a spatial join. The problem is, none of my layers - from the points to the parcels to the custom-made neighborhood file - had a projection attached to them. Arc 8 won't do a join without a projection, but Arc 3 will.
Check everything twice: Each appraiser's office stored data differently, and we could get only the barest details about a sale from all five counties. And we were after a very specific slice of the market: single family residential sales of houses (no vacant lots, no condos, no townhouses) that were qualified - an arm's length transaction between a buyer and a seller who didn't know each other and weren't giving the other guy a break that would unduly influence the price.
Some officials understood this perfectly, and knew exactly what we wanted. Others didn't get it, and gave us bad data. To check our data, we took more than 1,000 parcel ID numbers, some from each county, and looked them up, checking the sales history of each parcel for sales we had and sales we didn't have but should have. In one county, the appraiser's office gave us three dirty data sets before getting it right.
Write everything down: The last thing I did should have been the first. As one of the last things I did for the project, I wrote out, in excruciating detail, exactly what I did from the start. Much of it was cobbled together from handwritten scribbling on printouts and scratch paper. It was therapeutic almost to write it all out. In the future, I plan to start by writing out what I am going to do, then keeping an electronic journal on what I've done since then. It will come in very handy when editors begin asking questions about why this was done and that was done when it's been four months since you did them. I'm designing an intranet page whose whole purpose will be to track major data projects like this, from data acquisition to analysis to deadlines for stories.
The reaction to this story has been great - my editor called it the "feel-good investigative project of the year." People who own homes are awed by what they might be able to get for their home. I've received a couple of e-mail messages from house hunters thanking me for showing them that they weren't alone. People were buying the Sunday paper at our front counter days after the story ran. The folks who sell them told me they didn't have many left on Wednesday after it ran.
And, our editors have said, the story resonated with people more than anything we've done in recent memory. My thought is it resonated because we wrote about something people care deeply about: home. And that made all the work well worth it.
The complete package, including interactive features, can be found at: www.sptimes.com/2004/webspecials04/homeprices
Contact Matthew Waite by e-mail at waite@sptimes.com