Thursday ~ January 10, 2008
GIS tutorial: identifying where low-income homeowners reside to direct foreclosure related services
LSNC and other Legal Services corporations have been inundated with foreclosure related issues in recent months. As most poverty attorneys who have handled a foreclosure case will attest, prevention and early intervention are key to preventing foreclosure. Of course, before you can deliver education and community outreach to LSC eligible homeowners you need to know were they live. You also need to identify where populations of LSC eligible homeowners of color live. In many areas, due to the greater frequency of asset accumulation of Whites, if you do not include a separate analysis of race and ethnicity you will end up serving mostly White LSC eligible homeowners and excluding homeowners of color.
Lets conduct this analysis for Los Angeles County, California, using free online mapping software and free census data extraction tools so that any Legal Services program can replicate this process for their service area if it has a computer, Microsoft Office, and an internet connection. The online mapping software we will use is DataPlace and the free census extraction tool is GIStools. For purposes of space, this tutorial assumes that you have created an account with DataPlace and have downloaded the software and relevant datasets for GIStools.
First, we need to identify what Census data to use. Since we want to identify areas where foreclosure services outreach will have the greatest impact, we need to find the areas in Los Angeles county where there is a high percentage of homeownership and where there is a high percentage of LSC eligible clients. Since we also want to make sure that our outreach effectively reaches all LSC eligible homeowners we also want to find areas where there is a high percentage of homeowners of color and a high percentage of LSC eligible persons.
My suggestion is to use three datasets from the 2000 Census, SF3, to identify areas for possible outreach - P088 (Population by ratio of income to poverty), H007 (Tenure), and H013 (Tenure, White alone not Hispanic). Since we will be using DataPlace to map the data, we need to obtain the data at the Census Tract summary level (the smallest unit of Census geography that DataPlace can map). If I was going to map the data using ArcGIS, I would probably want to obtain the data at the Block Group summary level (the smallest unit of Census geography SF3 data is available at). Using GIStools extract the P088 and H013 data for California at the Census Tract summary level. Open the dbf. files (one for P088, one for H007, and one for H013) using Excel and copy and paste the all the data sets into one Excel spreadsheet (view example). Delete all rows for Census tracts not in Los Angeles county. You can tell which rows are for tracts in Los Angeles County by looking at the FIPSCO column. The first two numbers in the entry (”06″) represent the state code for California. The next three numbers are the FIPS codes for the various coutys in California. Los Angeles county’s FIPS code is “037.” Simply delete all rows where the value of the FIPSCO entry is not “06037″ (view example).
Second, we need to combine some of the data fields to simplify our spread sheet. Columns P088001 - 10 represent the various data fields of P088. Take a look at the Cenus Bureau’s technical documentation of the 2000 Census SF3 file to see what each of these columns represent. Since we want to know the count of person’s below 200% of the poverty level (our approximation of LSC client eligibility), we will create a new column (LSCPOP) that is the sum of P088002 - 09. The values of LSCPOP give us the count of potentially LSC services eligible persons for every Census tract in Los Angeles county (view example). Since it would also be helpful to know what percentage of the population is LSC eligible, lets create a new column (PERLSC) and use Excel to divide LSCPOP by P008001 giving use the percentage of the population that is LSC eligible for every Census tract in Los Angeles county (view example).
Columns H007001 -03 and H013001-03 represent the various data fields of H007 and H013 respectively. Take a look at the Cenus Bureau’s technical documentation of the 2000 Census SF3 file to see what each of these columns represent. We are going to use these columns to calculate the count of homeowners, the count of homeowners of color, and various percentages related to these categories.
The count of homeowners in a Census tract is simply H007002. We need to create a new column (PEROWN) to calculate the percentage of the population in each Census tract that is a homeowner. The values for this column are obtained by dividing H007002 by H007001 (view example).
The count of White homeowners is simply H013002. We need to calculate a new column (PERWHITE) to calculate the percentage of homeowners who are White in a Census tract. The values for this column are obtained by dividing H013002 by H007002 (view example).
We need to create a new column (HOCOLOR) to calculate the count of homeowners of color in a given Census tract. The values for this column are obtained by subtracting H013002 from H007002 (view example). We also need a new column (PERHOCO) to calculate the percentage of homeowners who are homeowners of color in a Census tract. The values for this column are obtained by dividing HOCOLOR by H007002 (view example).
Before we move on to step three, lets “clean up” our spread sheet by deleting columns that we no longer need. Before we do this, copy the whole spreadsheet and “Paste Spacial” it into a new spreadsheet. Paste “Values” only. Take a look at our the “cleaned up” spreadsheet showing the columns that we need to keep (view example). You should also replace all “#DIV/0!” with a null value and delete any extra sheets.
Third, we are going to identify the census tracts in Los Angeles county that will likely be the best areas to provide foreclosure related services to. We need to set the a criteria for identifying the tracts. What criteria you set is, of course, fairly subjective. When I create criteria like this I tend to select criteria that are restrictive enough to eliminate the majority of tracts but not so restrictive that I the pool of identified tracts is too small to be useful. For the purpose of this tutorial, our criteria will that tracts must have (1) 200 or more units that are owner occupied and (2) have a population that is 70% or more LSC eligible.
Create a new column (IMPACT). Using the Excel “If function” calculate whether each tract meets the criteria discussed above. Our “If Function” will read “=IF(PERLSC<.7,"",IF(H007002<200,"",1))"(view example). This formula will return a one in the IMPACT field if the tract meets our criteria and a null value if it does not.
Create a new column (RIMPACT). Using the Excel “If function” calculate whether each tract meets the criteria discussed above. Our “If Function” will read “=IF(PERLSC<.7,"",IF(H0COLOR<200,"",1))"(view example). This formula will return a one in the RIMPACT field if the tract meets our criteria and a null value if it does not.
Fourth, lets map! Save the Excel file as “Foreclosure Data.” Logon to Dataplace. Select “My Dataplace.” Select “Create New Project.” Title your project and select “Save.” Select “Upload the file from your computer.” Browse to the “Foreclosure Data” and upload it. Select “Extract Tables.” Select “Create Dataset.” Enter the required information. You may leave the default dates or enter todays date. The source of the data is “SF3 2000 Census.”Select “OK.” Select the entry “SFID.” Unselect “Indicator” and select “Part of Region.” Select “Update.” Select “Generate Indicators.” (It will take a few minutes for DataPlace to generate the indicators…be patient.) Lastly, map the indicator of your choice (IMPACT or RIMPACT) and zoom in to the appropriate level so that you can see those Census Tracts were large populations of low-income homeowners live.