Please Note: The 2008 appropriations are available at EarmarkWatch.org, an ongoing project of Sunlight Foundation and Taxpayers for Common Sense
Sunlight's 2007 Earmark Appropriation Visualization
Did any of the $1/2 billion dollars in 1753 earmarks on a single appropriations bill reach your community?
Select a 2007 Appropriations Bill:
Health and humans services
Housing and urban development
Search by Location:
Enter some form of location: an address, town, zip code, or state (spelled in full).View the 2007 Earmarks Mapped Out
Waiting for Google Maps...
Please be patient. It can take a few seconds to load the data.
<$100k
<$500k
<$1m
>$1m
Appropriation Details
Mouse over a dollar sign for more details about the earmark
Source of data:
Labor, Health and Human Services Appropriations Bill.
Making appropriations for the Departments of Labor, Health and Human Services, and Education, and related agencies for the fiscal year ending September 30, 2007, and for other purposes. Read it here.
We have plotted 1753 of the total 1810 earmarks. (The locations of the remaining earmarks were not clear: they were particular counties, parishes, or townships or single earmarks associated with multiple cities.) In some cases it appears that the final recipient of the earmark money is not in the location listed: for instance, an organization's headquarters might receive money to build a center elsewhere. Thus, these data are vague both here and, more importantly, in the bill itself.
Total amount of earmarks for this one bill: more than $503 million.
Housing and Urban Development Bill
House Conference Report H.R. 5576, the Treasury/Transportation/HUD bill. Read it here.
There were 553 earmarks, totalling more than $132.3 million. As above, we were not able to plot all of the earmarks as they were associated with counties/townships/parishes etc. We plot 477 of these earmarks.
We signed up for a Google Maps API key, and then used the spreadsheet function concatentate to create an API call that would get the coordinates of each specific city; for instance: http://maps.google.com/maps/geo?q=Adrian,MI&output=txt&key=ABQIAAAA24zMYKJgVFY6zVCWfn_1AhRBZ2tPRqR0de0yvU8x9MqGn2JzExTwg1Y8I6hqpRCXkjehBscOkGL00w.
We tooks all of these api calls and put them in a plain text file. We then wrote a perl script that would loop through each one, do the API call and get the returned JSON text object from Google Maps. Next, we wrote a simple java program to extract the cities and latitude and longitude coordinates from the JSON objects (this was quicker than learning how to use java or php JSON). Finally, we took that city and coordinate data, pasted it back into the original speadsheet, used concatenate again to get a final text file consisting of CITY,STATE,LATITUDE,LONGITUDE,DESCRIPTION.
These data were imported into a MySQL database on our server and the javascript in this webpage gets the bounding box of the map from Google, queries the database for any markers in the visible portion of the map and adds them to the map. When the map is moved, a new boiunding box is obtained, the map wiped cleaned and the new markers are added on. We first tried loading all 1753 earmarks onto the map at once but the latency to display the map and for the iuser to be able to interact with the map was unacceptable.
Using Google Maps API was surprisingly easy. Their documentation and examples were excellent. The majority of the time was not spent coding but cleaning the data to make sure that cities were spelled correctly. Total time to do this project: < 4 person days. The basic version was up and running in <2 days. An optimized, debugged version took another 2 days.
Labor, Health and Human Services Appropriations Bill.
Making appropriations for the Departments of Labor, Health and Human Services, and Education, and related agencies for the fiscal year ending September 30, 2007, and for other purposes. Read it here.
We have plotted 1753 of the total 1810 earmarks. (The locations of the remaining earmarks were not clear: they were particular counties, parishes, or townships or single earmarks associated with multiple cities.) In some cases it appears that the final recipient of the earmark money is not in the location listed: for instance, an organization's headquarters might receive money to build a center elsewhere. Thus, these data are vague both here and, more importantly, in the bill itself.
Total amount of earmarks for this one bill: more than $503 million.
Housing and Urban Development Bill
House Conference Report H.R. 5576, the Treasury/Transportation/HUD bill. Read it here.
There were 553 earmarks, totalling more than $132.3 million. As above, we were not able to plot all of the earmarks as they were associated with counties/townships/parishes etc. We plot 477 of these earmarks.
Get involved!
Learn more about earmarks and how you can get involved in researching earmarks and who added them to the billHow did we do this?
The SunlightNetwork provided a spreadhseet that consisted of a city, a state, a dollar amount and a short description.We signed up for a Google Maps API key, and then used the spreadsheet function concatentate to create an API call that would get the coordinates of each specific city; for instance: http://maps.google.com/maps/geo?q=Adrian,MI&output=txt&key=ABQIAAAA24zMYKJgVFY6zVCWfn_1AhRBZ2tPRqR0de0yvU8x9MqGn2JzExTwg1Y8I6hqpRCXkjehBscOkGL00w.
We tooks all of these api calls and put them in a plain text file. We then wrote a perl script that would loop through each one, do the API call and get the returned JSON text object from Google Maps. Next, we wrote a simple java program to extract the cities and latitude and longitude coordinates from the JSON objects (this was quicker than learning how to use java or php JSON). Finally, we took that city and coordinate data, pasted it back into the original speadsheet, used concatenate again to get a final text file consisting of CITY,STATE,LATITUDE,LONGITUDE,DESCRIPTION.
These data were imported into a MySQL database on our server and the javascript in this webpage gets the bounding box of the map from Google, queries the database for any markers in the visible portion of the map and adds them to the map. When the map is moved, a new boiunding box is obtained, the map wiped cleaned and the new markers are added on. We first tried loading all 1753 earmarks onto the map at once but the latency to display the map and for the iuser to be able to interact with the map was unacceptable.
Using Google Maps API was surprisingly easy. Their documentation and examples were excellent. The majority of the time was not spent coding but cleaning the data to make sure that cities were spelled correctly. Total time to do this project: < 4 person days. The basic version was up and running in <2 days. An optimized, debugged version took another 2 days.