Here Comes the FY11 Earmarks Database

Mike C — Sun, 19 Dec 2010 04:51:00 GMT

I'm really interested in politics (don't worry, I'm not going to start bashing politicians and hammering you with political rage). The point is when the U.S. FY11 Omnibus Spending Bill (the bill to fund the U.S. Government for another year) was announced it piqued my interest. I'm fascinated by "earmarks" (also affectionally known as "pork"). For those who aren't familiar with U.S. politics, "earmark" is a slang term for "Congressionally Directed Spending". It's basically the set of provisions added to bills by Senators and Representatives in the U.S. Congress, directing money to be spent on certain programs or projects without all the hassle of introducing a separate bill, sending it through umpteen committees, and getting enough supporters in both chambers to vote it up. Most of the time Senators and Representatives introduce earmarks that send money back to their home states (see this guy for details).

Now it looks like the Omnibus Bill is dead for now, but the record of the earmarks is still available at http://appropriations.senate.gov/news.cfm in PDF format (no, you don't have to visit Wikileaks). Just look for the "Earmarks" links at the bottom of the page under "All News Items". So what I have been doing the past few days, since the bill was announced, is loading the earmarks into a SQL database. It turned out to be a little more complex than it should have been. There was the issue of extracting the data out of the PDF files, normalizing it, ETLing it into SQL Server, cleaning it up (a *lot* of manual cleanup required), adding and cleaning up reference data, and finally linking the earmarks to the reference data.

You can download the resulting SQL 2008 database backup file from this post and restore it to your test server to play around with. Here are some sample queries I ran:

SELECT SUM(e.Amount)
FROM dbo.Earmark e;

Result:
8313820025

(That's $8.3 Billion dollars in earmarks -- note that some earmarks did not specify $ amounts, so this number is low)

Want to see total earmarks requested by party affiliation?

WITH CTE
AS
(
SELECT DISTINCT e.EarmarkID, e.Amount, c.Party
FROM dbo.Earmark e
INNER JOIN dbo.EarmarkCongress ec
ON e.EarmarkID = ec.EarmarkID
INNER JOIN dbo.Congress c
ON ec.CongressID = c.CongressID
)
SELECT Party, SUM(Amount)
FROM CTE
GROUP BY Party;

Results:
D, 6779032457
R, 2933829318
I, 216149500

This one actually double-counts some items since the same earmark can be sponsored by a Democrat (D), a Republican (R), and/or an Independent (I) all at the same time.

And if you want to see how much was requested by your Senators and Representatives:

SELECT c.Chamber,
c.LastName,
c.FirstName,
c.Party,
SUM(e.Amount)
FROM dbo.Earmark e
INNER JOIN dbo.EarmarkCongress ec
ON e.EarmarkID = ec.EarmarkID
INNER JOIN dbo.Congress c
ON ec.CongressID = c.CongressID
WHERE c.State = 'NJ'
GROUP BY c.Chamber,
c.LastName,
c.FirstName,
c.Party;

Results:
House, Adler, John D, 23848125
House, Andrews, Rob D, 27710000
House, Holt, Rush D, 103953000
House, Pallone, Frank D, 21810000
House, Pascrell, Bill D, 15143000
House, Payne, Donald D, 52908000
House, Rothman, Steven D, 107153000
House, Sires, Albio D, 120141000
Senate, Lautenberg, Frank D, 239061125
Senate, Menendez, Bob D, 243982125

Again there can be double-counting since an earmark can have multiple sponsors (very likely when you're talking about Senators and Representatives from the same state).

Also note that I had to add in the non-voting Congressional delegates from U.S. territories (Puerto Rico, U.S. Virgin Islands, Guam, etc.) and Washington, D.C. to the reference data. They don't vote, but they're allowed to attach earmarks to bills to bring some cash back home.

I'd love to see what others are able to do with this data. Some ideas -- use Reporting Services to map earmarks across the country; come up with clever queries to allocate earmarks equally across sponsors to avoid the double-counting issues; some Top n style queries by amounts, counts, etc.; combine it with spatial data and other reference data out there; compare things like # of sponsors for an earmark vs. the amount of the earmark, or seniority of sponsors vs. the amount of the earmark; maybe do something clever with it in PowerPivot. If you find this type of data interesting and you download the database to play with, let us know the results and what you come up with.

T-SQL Tuesday #006: Tiger/Line Spatial Data

Mike C — Tue, 11 May 2010 23:11:00 GMT

This month’s T-SQL Tuesday post is about LOB data http://sqlblog.com/blogs/michael_coles/archive/2010/05/03/t-sql-tuesday-006-what-about-blob.aspx.

For this one I decided to post a sample Tiger/Line SQL database I use all the time in live demos. For those who aren't familiar with it, Tiger/Line data is a dataset published by the U.S. Census Bureau. Tiger/Line has a lot of nice detailed geospatial data down to a very detailed level. It actually goes from the U.S. state level all the way down to street, feature and landmark level. Tiger/Line data is very complete and detailed--but the best part is it's FREE. There are lots of applications for Tiger/Line, like national [U.S.] and local mapping and geocoding applications [applications that convert street addresses to (latitude, longitude) coordinates]. All this great data is distributed in the form of a ton of ESRI shapefiles.

A shapefile is basically a file format that contains shape objects like points, lines and polygons. SQL Server doesn’t natively understand ESRI shapefiles, but it also stores geospatial objects like lines, points and polygons. There some handy utilities out there for loading these files into SQL Server. Morten Nielsen has a great utility for loading shapefiles into Geometry and Geography data types at http://www.sharpgis.net/page/Shape2SQL.aspx. Because of the volume of data involved in this project (I loaded hundreds of shapefiles) I decided to roll my own small set of SSIS custom components that read ESRI shapefiles and convert them to SQL Server spatial data types [keep an eye out -- these components are scheduled to be published with source code by SQL Server Standard magazine in the near future].

The sample database can be downloaded from http://www.sqlkings.com/downloads/Tiger_Sample.zip

Download the ZIP file from http://www.sqlkings.com/downloads/Tiger_Sample.zip.
Extract the database backup file and restore it to a SQL Server 2008 instance.

In the future I’ll be sharing some code samples on the blog to demonstrate Tiger/Line data (as well as spatial data from other sources) based on this database.

Here are a couple of quick queries you can run against this sample database to view the spatial data in SSMS 2008.

SELECT *

FROM Tiger.State;

SELECT c.*

FROM Tiger.State s

INNER JOIN Tiger.County c

ON s.STATEFP = c.STATEFP

WHERE s.STUSPS = 'TX';

SELECT *

FROM Tiger.ZCTA z

WHERE z.ZCTA5CE00 LIKE '0[7-8]%';

This last one works because all of the ZIP Code tabulation areas for the state of New Jersey start with '07' and '08'. There are similar relationships between other ZCTA prefixes and their states.

Next time we'll look at using SQL Server-based spatial data with online mapping programs like Bing maps.

*For more information about Tiger/Line data visit http://www.census.gov/geo/www/tiger/tgrshp2009/tgrshp2009.html.

Search results matching tags 'SQL 2008' and 'sample database'

Here Comes the FY11 Earmarks Database

T-SQL Tuesday #006: Tiger/Line Spatial Data