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.
We also need to set our criteria for identifying Census tracts in Los Angeles county that will likely be the best areas to provide foreclosure related services to homeowners of color. For the purpose of this tutorial, our criteria will that tracts must have (1) have 200 or more units that are occupied by an owner of color and (2) have a population that is 70% or more LSC eligible.

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.
Before we move on to step four, 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. You only need to keep the SFID (tract identifier), IMPACT, and RIMPACT. Delete any blank sheets.In order to upload your data faster to DataPlace, you may want to delete all rows that have a null value for IMPACT and RIMPACT
(view example).

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.
Note: Due to the demographics of Los Angeles county, there is almost no difference between IMPACT and RIMPACT. This may not be the case for your service area. For instance, in Del Norte County, California, there was no overlap between IMPACT and RIMPACT.
Next steps: Your maps let you know where you need to conduct outreach and community education based on actual data. This is a much sounder method than relying on your “gut feeling” regarding what communities are likely to need foreclosure related services. Nonetheless, you still need to put on your
community lawyering hat, leave the office, and work with community groups; all the analysis in the world will not alleviate the hardships that the foreclosure crisis is creating for low-income homeowners.