EGAP site is down for maintenance Please check back soon
The Recovery Act has poured an unprecedented $787 billion into existing and new programs including transportation, education, health care, housing, broadband expansion, etc. This spending raises key qauestions for those who care about equity. Six organizations came together to try to answer the question "Are federal dollars going to where they are needed?" Those organizations are Azavea, The Center for Social Inclusion, Esri, The Gamaliel Foundation/Transportation Equity Network, Good Jobs First, and OMB Watch. Preliminary research suggests that poor communities, particularly those fo color, have been disproportionately affected by the recession. The EGAP tool has been designed to as a means to investigagte the extent to which Recovery Act dollars went to these communities.
EGAP is a flash application which utilizes a heavily modified ESRI ArcGIS viewer for flex. It was created in collaboration with ESRI, Azavea and OMB Watch. It resides on an OMB Watch server, and can be accessed at www.equitygap.net. In addtion to the application, there are approximately 60 publicly available map services, also hosted on an OMB Watch server, that have been created in support of the application. There are four file geodatabases that contain the layer files and data for the map services.
The underlying data has been compiled from various sources including the Census Bureau, www.fedspending.org and ESRI. None of the underlying data is changed. In some cases, this results in apparent errors.
The current live version can be found here. The test version (which may be the same as the current live version) can be found here
II. The data
III. Map Services
IV.Databases
Compressed file geodatabases can be found here. They are the *.gdb.zip files.
The application was written using the ESRI viewer for flex. ESRI documentation for the flex api can be found here. The basic application is written in Adobe flex and action script. A compiled version is on the OMB Watch server in the /var/www/edutest/EGAP/bin-release directory. An uncompiled version of the code will be placed on the server soon.
The application allows users to select from a preset list of demographics and combine that with federal spending in the form of projects that were funded through the recovery act.
There are many widgets that make up the application. This is the current list of modules that are in the build list:
GraphicComparisonWidget
EgapDetailsContainer
EgapInformationContainer
EgapToolContainer
HeaderWidget
EgapLegendWidget
LevelListingWidget
MapViewWidget
SplashWidget
HeaderControllerWidget
IdentifyFTAWidget
IdentifyWidget
EgapMapListener
LayerListWidget
MapSwitcherWidget
NavigationWidget
OverviewMapWidget
PrintWidget
WidgetContainerWidget
BasicMapWidget
The spending data is from the federal government's usa spending website (www.usaspending.gov). The data is downloaded by OMB Watch, then the 'amount retained' is calculated. This is in an effort to ensure that money is not double counted, as many contracts involve sub-contractors and vendors, and the money to them comes from the original contract. Once that amount has been calculated, a subset of fields is downloaded, geocoded, and aggregated at various geographic levels.
Each project is individually 'geocoded', ie placed as a dot on a map, based on the place of performance. Please note that the place of performance may differ greatly from the location of the contract awardee. This data is reported by the recipient of the contract, and in some cases is not complete. In the cases where an incomplete address was given, the zip code, if reported, was used to 'geocode' the data. There are some projects that are not able to be located on the map, as a result of incomplete or incorrect data. This is a very small percentage of the total. For projects that have multiple recipients (prime contractors, subcontractors and/or vendors) there will be a dot for each of those recipients, along with the details about the project. The map service that shows these projects as individual dots can be found here Please note that the project dots are only visible at zoom levels of 1:18000000 and in.
Once the projects are 'geocoded', the amount of money retained, per project, is aggregated at the state, congressional district, county and census tract levels. This results in a total amount of money per geographic location. The spending is then broken down into the following sub-categories: Department of Transportation, Federal Highway Administration, Federal Railroad Administration and Federal Transit Administration, based on the funding agency. All projects that fall into those sub-categories are aggregated at the same geographic levels as the full database. Each of the subcategories is also mapped on individual map services which can be found here:
The following information about each project is included:
RecipRole This is the 'role' of the recipient. Allowable codes are P (for primary recipient), S (for subcontractor), PV (for primary vendor), and SV (for sub-vendor).
FundingAgCd This is the numeric code of the agency that funded the project. This is a federal code, and is used in determining the spending subsets.
FundingAgName This is the name of the agency that funded the project.
AwardAgCd The numeric code of the agency that awarded the project. This is a federal code.
AwardAgName The name of the agency that awarded the project.
FundingTAS The numeric Treasury Account Symbol (TAS) code. More info on this code can be found here or here
TASSubAcct The numeric TAS sub account code.
RecipDUNS The DUNs number of the recipient.
SubDUNS The DUNs ASK RICH IF THIS IS SUBRECIPIENT, OR ANOTHER DUNS FOR PRIME
RecipName The name of the recipient.
RecipZip Recipient zip code.
RecipSt Recipient state.
AwardNum Award number.
OrderNum Order number.
SubAwardNum Sub award number
CFDANum Catalog of Federal Domestic Assistance (CFDA) number. More info on this number can be found here
AwdType Award type.
AwdDate Date of the award.
OrigAwdAmt Original award amount.
LocalAmt Local award amount.
ProjStatus Project status.
NumJobs Number of full-time equivalents (fte) created/retained, as reported by awardee.
PopStreet Place of performance street address line one.
PopStreet2 Place of performance street address line two.
PopState Place of performance state.
PopCountry Place of performance country.
PopCity Place of performance city.
PopZip Place of performance zip.
ActivityCd Activity code.
ActivityCd2 Activity code 2.
PopLat Place of performance latitude.
PopLon Place of performance longitude.
AwdDt Award date
AwdAmt Award amount.
AmtSub Amount to subcontractor.
AmtVend Amount to vendor.
AmtRetain Amount retained.
AwdAgCat Awarding agency category.
FundAgCat Funding agency category.
All aggregated spending has a map service. Further details about the map services can be found here
Demographic data has been compiled from two sources, the Census Bureau, and ESRI. Workers without vehicles, workers who take public transit to work, workers who drive to work alone and poverty data are all from the Census Bureau. In particular, the data is from the American Community Survey (ACS) 5 year estimates. More information about this data can be found here This data will most likely be updated using the most recent census data in the near future.
The demographic data from the census bureau is already broken down geographically. This information needs to be downloaded from (here, then columns are added to the pertinent layer files in the EGAP geodatabase (ie PctNoCars).
Unemployment and race data is all courtesy of ESRI.
Back to TopThere are approximately 60 publicly available map services that are used in the application. Most of these map services are accessed through the application, and the user is able to interact with them in some way. Some of the services are used to locate things that the user has clicked on. This is the list of all map services that are associated with the EGAP project:
RCV/AmerInd (MapServer)--Depicts the percentage of American Indian/Native Alaskans
RCV/AsianNH (MapServer)--Depicts the percentage of Asians
RCV/Black (MapServer)--Depicts the percentage of Blacks
RCV/CensusTracts (MapServer)--Used to 'locate' census tracts
RCV/CongDistLocation (MapServer)--Used to 'locate' congressional districts
RCV/CountyLocation (MapServer)--Used to 'locate' counties
RCV/DOTPCapSymbolized (MapServer)--Depicts the amount of money from projects funded by the Department of Transportation per person as proportional icons.
RCV/DOTProjects (MapServer)--Shows the DoT funded projects as dots on the map.
RCV/DOTRetDriveAlone (MapServer)--Depicts the amount of DoT money per worker who drives to work alone.
RCV/DOTRetNoCar (MapServer)--Depicts the amount of DoT money per worker who does not have a vehicle.
RCV/DOTRetPerCap (MapServer)--Depicts the amount of DoT money per person.
RCV/DOTRetPov (MapServer)--Depicts the amount of DoT money per household in poverty.
RCV/DOTRetPubTrans (MapServer)--Depicts the amount of DoT money per worker who take public transit to work.
RCV/DOTRetUnemployed (MapServer)--Depicts the amount of DoT money per unemployed person.
RCV/DOTSymbolized (MapServer)--Depicts the amount of DoT money as proportional icons.
RCV/DriveToWork (MapServer)--Depicts the percentage of workers who drive to work alone.
RCV/FHAPCapSymbolized (MapServer)--Depicts the amount of money from projects funded by the Federal Highway Administration per person as proportional icons.
RCV/FHAProjects (MapServer)--Shows the FHA funded projects as dots on the map.
RCV/FHARetDriveAlone (MapServer)--Depicts the amount of FHA money per worker who drives to work alone.
RCV/FHARetNoCar (MapServer)-Depicts the amount of FHA money per worker who does not have a vehicle.
RCV/FHARetPerCap (MapServer))-Depicts the amount of FHA money per person.
RCV/FHARetPov (MapServer))-Depicts the amount of FHA money per household in poverty.
RCV/FHARetPubTrans (MapServer)-Depicts the amount of FHA money per worker who takes public transit to work.
RCV/FHARetUnemployed (MapServer))-Depicts the amount of FHA money per unemployed person.
RCV/FHASymbolized (MapServer)--Depicts the amount of money from projects funded by the Federal Highway Administration as proportional icons.
RCV/FRAPCapSymbolized (MapServer)--Depicts the amount of money from projects funded by the Federal Railroad Administration per person as proportional icons.
RCV/FRAProjects (MapServer)--Shows the FRA funded projects as dots on the map.
RCV/FRARetDriveAlone (MapServer)--Depicts the amount of FRA money per worker who drives to work alone.
RCV/FRARetNoCar (MapServer)-Depicts the amount of FRA money per worker who does not have a vehicle.
RCV/FRARetPerCap (MapServer))-Depicts the amount of FRA money per person.
RCV/FRARetPov (MapServer))-Depicts the amount of FRA money per household in poverty.
RCV/FRARetPubTrans (MapServer)-Depicts the amount of FRA money per worker who takes public transit to work.
RCV/FRARetUnemployed (MapServer))-Depicts the amount of FRA money per unemployed person.
RCV/FRASymbolized (MapServer)--Depicts the amount of money from projects funded by the Federal Railroad Administration as proportional icons.
RCV/FTAPCapSymbolized (MapServer)--Depicts the amount of money from projects funded by the Federal Transit Administration per person as proportional icons.
RCV/FTAProjects (MapServer)--Shows the FTA funded projects as dots on the map.
RCV/FTARetDriveAlone (MapServer)--Depicts the amount of FTA money per worker who drives to work alone.
RCV/FTARetNoCar (MapServer)-Depicts the amount of FTA money per worker who does not have a vehicle.
RCV/FTARetPerCap (MapServer))-Depicts the amount of FTA money per person.
RCV/FTARetPov (MapServer))-Depicts the amount of FTA money per household in poverty.
RCV/FTARetPubTrans (MapServer)-Depicts the amount of FTA money per worker who takes public transit to work.
RCV/FTARetUnemployed (MapServer))-Depicts the amount of FTA money per unemployed person.
RCV/FTASymbolized (MapServer)--Depicts the amount of money from projects funded by the Federal Transit Administration as proportional icons.
RCV/HI_PINH (MapServer)--Depicts the percentage of Native Hawaiians/Pacific Islanders
RCV/Hispanic (MapServer)--Depicts the percentage of Hispanics.
RCV/NoCar (MapServer)--Depicts the percentage of workers without a vehicle.
RCV/NonWhite (MapServer)--Depicts the percentage of Non-whites.
RCV/Population (MapServer)--Depicts the population.
RCV/Poverty (MapServer)--Depicts the percentage of households in poverty.
RCV/PublicTrans (MapServer)--Depicts the percentage of workers who take public transit to work.
RCV/RecoveryAct (MapServer)--Depicts that amount of money retained from Recovery Act Awards, aggregated by place of performance.
RCV/RecoveryPCapSymbolized (MapServer)--Depicts that amount of money retained from Recovery Act Awards, per person as a proportional icon.
RCV/RecoveryProjects (MapServer)--Shows all recovery act projects as dots on a map
RCV/RecoverySymbolized (MapServer)--Depicts that amount of money retained from Recovery Act Awards, as a proportional icon.
RCV/RetDOT (MapServer)--Depicts the amount of money from DOT funded projects.
RCV/RetDriveAlone (MapServer)--Depicts the amount of money from all projects per worker who drives to work alone.
RCV/RetFHA (MapServer)--Depicts the amount of money from FHA funded projects
RCV/RetFRA (MapServer)--Depicts the amount of money from FRA funded projects
RCV/RetFTA (MapServer)--Depicts the amount of money from FTA funded projects
RCV/RetNoCar (MapServer)--Depicts the amount of money from all projects per worker who doesn't have a vehicle.
RCV/RetPerCap (MapServer)--Depicts the amount of money from all projects per person.
RCV/RetPerCapout (MapServer)--Depicts the amount of money from all projects per person, with the states that have the highest population outlined in red.
RCV/RetPov (MapServer)--Depicts the amount of money from all projects per household in poverty.
RCV/RetPubTrans (MapServer)--Depicts the amount of money from all projects per worker who takes public transit to work.
RCV/RetUnemployed (MapServer)--Depicts the amount of money from all projects per worker who is unemployed./p>
RCV/StateLocation (MapServer)--Used to 'locate' states.
RCV/StatesOutlined (MapServer)--Used to show an outline of the states when the user has zoomed in.
RCV/TractLocation (MapServer)--Used to 'locate' census tracts.
RCV/Unemployment (MapServer)--Depicts the rate of unemployment.
RCV/White (MapServer)--Depicts the percentage of whites.
This list can be found here.
Each map service that is used for user interaction, consists of 6 layers. Layer 5 is the state layer, layer 4 is the congressional district layer, layer 3 is the county layer, layer 2 shows the states with the five highest percentage of whatever demographic has been chosen, layer 2 shows census tracts, and layer 0 is another state layer, used to outline the states when the user has selected the county view at the full extent. In general, all map services that the user can interact with follow this pattern. This is in an effort to streamline the programming. Each layer is created from a Locator services generally consist of one layer, which is the layer the service is used to 'locate'. These map services have very little underlying data (as opposed to the interactive map services), and are used to speed up searching.
In some cases, the amount of money is divided by the demographic total in the geographic area to come up with 'an amount per' number. For example, if the user selects 'Poverty' as the demographic, and 'All Recovery Spending', they will be shown a map that depicts the amount of money from recovery act projects, per household in poverty. This amount will be shown at the state, congressional district, county and census tract levels. In general, the darker the color on the map, the higher the dollar amount. In other cases, the spending is shown as a proportional icon overlay on top of a map depicting the demographic that was chosen. For example, if the user chooses 'Asian' as the demographic, and 'Department of Transportation' as the spending, they will be shown a map that depicts the percentage of the population that is Asian at state, congressional district, county or census tract levels, with an overlay that depicts the amount of money from projects funded by the Department of Transportation aggregated in each of those areas. In general, the spending data is not divided by the race data. Please note that Unemployment data is not available at the census tract level.
The application only uses file geodatabases. These are the databases that are part of the ESRI ArcGIS software. They are created using desktop software, from data either downloaded from various locations, or included with the ESRI software.
For data that is included with the ESRI software, it is a matter of determining which data you want to use, then copying it from the layer file into the corresponding layer file. Each geographic level is represented by a layer file.
If the data is from an external source, and is not already broken down by geography, it will have to be downloaded, then put into a format that the gis software can use to 'geocode' the data. For example, the recovery act project data (spending details), is downloaded from a mySQL database on the OMB Watch fedspending server (currently tesla). The database is called rcv***** where the *s are replaced by the current version number (2011c as of 3/7/12). The table is rcv_main. The fields that we currently use can be found in the data section. If this list of fields changes, the map services that depict the projects as dots will all have to be changed as well. The data is currently downloaded as a delimited text file. Once the data has been downloaded, the text file needs to be edited to include the field names (first row) and the data fields must be delimited by ",". The first row of the file should look like this:
"RecipRole","FundingAgCd","FundingAgName","AwardAgCd","AwardAgName","FundingTAS","TASSubAcct","RecipDUNS","SubDUNS","RecipName","RecipZip","RecipSt","AwardNum","OrderNum","SubAwardNum","CFDANum","AwdType","AwdDate","OrigAwdAmt","LocalAmt","ProjStatus","NumJobs","PopStreet","PopStreet2","PopState","PopCountry","PopCity","PopZip","ActivityCd","ActivityCd2","PopLat","PopLon","AwdDt","AwdAmt","AmtSub","AmtVend","AmtRetain","AwdAgCat","FundAgCat"
Due to the amount of data (almost 500,000 records), the file has to be split into at least three files. Each of those files must then be geocoded, using the PopStreet, PopCity, PopState and PopZip fields. All records that cannot be geocoded using that method are then extracted from the resulting geodatabases, and a second attempt at geocoding based on PopZip only, is run. Those files and the successfully geocoded records from the previous address geocoding sessions then need to be merged to create one large file. This layer file is then stored in the EGAP file geodatabase, and is used as the basis for aggregating all spending data.
The EGAP file geodatabase (EGAP.gdb) consists of 5 point layers and 4 polygon layers. The 5 point layers are the individual projects, and the polygon layers are state, congressional district, county and census tract layer files. The polygon layers are named according to the geographic level they represent. The state layer is S_EGAP, county is C_EGAP, congressional district is CD_EGAP and the tract layer is T_EGAP. Each of the project layers is named according to the funding it shows. The layer with all of the projects is Recover_Projects, DOT projects is DoT_Projects, FHA is FHA_Projects, FRA is FRA_Projects and FTA is FTA_Projects.
The demographic data from the census bureau is already broken down geographically. This information needs to be downloaded from (here. The data will be in the form of either an excel apreadsheet or a text file. The data will need to be in a delimited text file, then using the desktop software, columns are added to the pertinent layer files in the EGAP geodatabase (ie PctNoCars) using a geographic identifier, like FIPS, to append the data to the proper row.
There are two other geodatabases that the maps currently use. Tracts.gdb and RetPov.gdb. The Tracts.gdb contains the census tract layer files with a subset of the spending data that can be found in the EGAP.gdb census tract layer files. The RetPov.gdb contains layer files specifically for the map that depicts amount of recovery spending combined with poverty.