STUDY METHODOLOGY

Companies create a MS Excel spreadsheet with a row for each employee in SRP office(s): Column A: street number & street name, Column B: city, Column C: state, Column D: zip code (see below). Other file formats beside Excel such as DBF 3 and CSV were also accepted. In most instances, this file took companies less than 15 minutes to create. The file was then placed on a floppy, zip cartridge, or CD.

<Sample file. These are not real addresses>

Cities21 then brought a laptop PC on site to each SRP company to process the spreadsheet. The spreadsheet was imported into the industry standard geographic information systems software application, ESRI ArcView. Typically, 90% of the addresses imported without problem. Some re-formatting of address data was often performed on site to increase address import acceptance rate. Address data was never removed from company premises.

A new spreadsheet was created containing a "1/5 mile Bay Area grid number" for each address. This "geo coding" took approximately 3 minutes per 1,000 employees. Because commercial applications Excel and ArcView were used rather than custom software, the chance for hidden sleight of hand to leave with the addresses was eliminated. The calculation to convert from address to grid uses a GIS function to calculate longitude & latitude from a Bay Area street map database and then converts longitude/latitude to the 1/5 mile grid. The resultant file contains no personal information:

Employee grid information was then consolidated with data from other companies to produce a Bay Area map showing origination density.

Detailed documentation of the laptop address processing procedure can be found in this 4 page PDF.