Sources of GIS Data

Data for U.S. Business Locations 1999 to 2010

Harvard has a license for a product named ESRI Business Analyst that provides information on the locations of all of the businesses in the country from 2006 through 2011. These data are available to GSD users through the shared network drive Goliath/geo/esri_business_analyst. Documentation of these datasets can be viewed in Armap through Right-Click > Data > View Item Description.

The GSD also as a commercial CD Phonebook application that purports to provide access to all of the business data fo rthe US for the year 2000. These instructions cover how to download information from this CD and turn it into spots in an ArcGIS map.

Steps involved in using on-line business listings

click the link for more detailed explanation.
  1. Map Drive N: to \\goliath.design.harvard.edu\public\geo\selphone
  2. Click the icon for N:/Select Phone/phonebks.exe
  3. Select a City or zip code
  4. Select an SIC code -- business type.
  5. Retrieve Listings
  6. Scroll to the bottom of the list and select all the records
  7. Copy Listings -- using Edit->Copy.
  8. Open the Excel template table
  9. Paste the records
  10. Select the data-filled cells from the Excel workbook
  11. Save the selection as DBF 3 file
  12. Open the table in ArcGIS
  13. Use Tools->Add XY Data to turn the records into spots.
  14. Export->Data to save your businesses as a shape file
  15. Save a copy of the SIC lookup table
  16. When you run out of free trials, download the registration info to your windows directory.

Related Documents

Map Drive N: to \\goliath.design.harvard.edu\public\geo\selphone

In Windows Explorer, go to Tools > Map Network Drive, pull down the top menu to specify Drive N:\, and fill in the Folder field with \\goliath.design.harvard.edu\public\geo\selphone. Hint: if you don't use drive N: you will not be able to use the business directory!

Click the icon for N:/Select Phone/phonebks.exe

Inside selphone is a directory names Select Phone. Inside this is a green icon that looks like a CD with a phone handset in front of it. The name of the file is phonebks.exe. Double-click it. A lot of advertisements come up. Click them and they go away. Finally a grey window arises that asks you if you want to register or continue. Just click continue. If it says you have exceeded your allocation of free uses, see the last item on this list of steps.

Select a City or zip code

You want to avoid downloading more than a couple hundred records at the same time. So it is best to restrict your queries geographically or by the industry code, or both. Begin by going to the Search menu and pulling down to Lookup and choose City.

Select an SIC code -- Standard Industrial Classification

Likewise, do a lookup on SIC code. If you can't guess the exact names of an SIC, you can enter any sequence of characters into the search box, and click search. When you find what you need, select it and click OK.

Retrieve Listings

The last two steps should have resulted in the City and SIC blanks being filled in at the top of your blank table. When you choose Retrieve Listings from the Select menu, it will pull out the correct records from the directory.

Scroll to the bottom and select them all

If you don't have too many records retrieved, you should be able to scroll to the bottom of the list by pulling the scroll bar down several times. Pulling it once usually isn't enough; watch out! When you finally get to the bottom, click and drag to the top, highlighting all the records.

Copy Listings -- using Edit->Copy

Not much more to say here. You can also use Ctrl-C to copy all the records to the clip board. Do not use File->Copy Special... or you will have to pay!

Open the Excel template table

There is an excel template in a folder named GSD_Special, within your drive N:. Open it. This has the column names and all the right data types corresponding to the stuff you copied out of the selphone directory.

Paste the records

The first row in the excel template is the column names. You should scroll all the way to the left, and select the cell just under the cell that says "name" in it and use Edit->Paste to paste the records you copied from the Phone Book app. This should populate your table. Check to make sure the data under each column name seems logical

Select the data-filled cells from the Excel workbook

The idea now is to select the data and headings, but not a lot of blank cells, so you can export the table out as a dbase file which can be imported into arcGIS. The best way to do this is to scroll down and over to the bottom-right corner of the sheet. Then click and drag back to the top left, selecting all the data-filled cells.

Save the selection as DBF 3 file

Use File->save as to save as a DBF3 format file. Save this file with the rest of your GIS data, and give it a meaningful name like Gas_Stations.dbf.

Open the table in ArcGIS

In ArcGIS, use File->Add Data to find your dbf table and add it to your map. Note that in your list of layers on the left of the map frame there are two tabs for "Display and Source" Tables only show up in the layer list when the source tab is selected.

Use Tools->Add XY Data to turn the records into an 'Event Theme'. In AddXY Data dialog box, the Lattitude and Longitude fields from the dbase table should be correctly designated as Y and X.

Export your Shape FIle

Once your amazement subsides, you may want to save your new theme as a shape file so that it is easy to add in the future. Right-click on the theme, and choose Data->Export Data to preserve all of this as an ESRI Shape file that you can open in future ArcGIS sessions.

Save a copy of the SIC Lookup Table

While you have \\maps\selphone mapped, drag a copy of the file, sic_lut.dbf from the gsd_special directory, into tha same directory as your new shape file. This lookup table can be used to create custom categories of businesses according to the 400-class Standard Industrial Classsification. For tips, see the GSD Manual page, Using Lookup Tables in ArcGIS

When you run out of free trials, download this.

The CD application has an annoying feature that wants you to register after you have used it 8 times. The GSD has 5 legitamately licencensed copies of this application, which are managed through our keyserver, so it is OK for everyone to register their copy. Here's how: right-click here and save this file into your c:\windows folder or your c:\winnt folder if you have one. The idea is to replace the existing file named phonebks.ini. This should let you play several more times.