Tags : SQL

Analysis shows shortcomings in N.J. tax incentives

When states suffered severe job losses during the recession, many responded by pouring money into tax incentive programs.

Most states offer a litany of incentives targeted for different types of companies. Some are meant to lure employers from neighboring jurisdictions, while others fund expansions of existing businesses.

Our story focused on New Jersey, which had recently issued multiple high-profile awards.

The data analysis attempted to answer two questions. First, we needed to gauge the extent to which the state increased its tax incentive awards. I also wanted to examine how the deals played out and whether any companies later cut ...

Read more ...

Data matching finds felons with N.C. gun permits

Gun control in North Carolina has always been a heated topic.

The massacre at Sandy Hook Elementary School in Connecticut and the decision by a suburban New York City newspaper to publish the addresses of local gun permit holders gave both sides of the North Carolina gun control issue ammunition.

And, as usual, much of the battle unfolded in the state’s General Assembly, where lawmakers introduced some two dozen bills related to guns during the 2013-14 session. Of all the bills, one gained particular interest among Charlotte Observer journalists.

North Carolina is one of 12 states with open gun ...

Read more ...

Getting started with an open-source database manager: MySQL

If you’re working from a Mac computer or getting into truly large datasets, Access may not be a viable option as a database manager. (It doesn’t work on Macs, and there are row limitations.) A good alternative is MySQL, an open-source database manager, which Alex Richards of the Chicago Tribune taught on Friday.

MySQL essentially installs a server that runs in the background on your computer. But if you don’t really understand what that means, don’t let it scare you. There are a handful of secondary applications that let you interface with MySQL in a way ...

Read more ...

First venture: Probing pipeline leak detection

I became interested in pipeline data after reporting on the Keystone XL oil pipeline. There was (and still is) a lot of debate about the pipeline's projected spill rate and safety. TransCanada, the Canadian company behind the project, already has one U.S. pipeline, which leaked 14 times within its first year of operation. I didn't know if that was unusual, so I wanted to compare TransCanada's record to the leak rates from other companies.

That story eventually proved too much to tackle, but it led me to another story about leak detection. As it turns out ...

Read more ...

INSERT IGNORE helps update huge database

Dead people have been giving us headaches at the NICAR Database Library. Our effort to update the Social Security Death Master File this year was quite a battle. Emerging victorious, one warrior has helped lead the way: the INSERT IGNORE INTO Structured Query Language (SQL) statement.

The Death Master File (DMF) is a gigantic database with nearly 86 million records as of the most recent release. In 1983 Congress required the Social Security Administration (SSA) to collect records of deaths from states, which were then stored in the newly-created DMF. The database contains deaths going back decades. Each record in ...

Read more ...

Data matching uncovers convict school cops

Until recently, getting arrested in Philadelphia for possession of crack cocaine and admitting drug dependency would not preclude being hired or continuing to work as a police officer in the public school system.

A month-long, data-driven investigation  by The Philadelphia Inquirer found that in more than a dozen cases school police were themselves getting into trouble with the law. Even an open bench warrant issued for one officer charged with a drug offense failed to trip the school district's alarm.

In another case, an officer who showed up in court to face charges after her second arrest for drug ...

Read more ...

Scouring MAUDE data to find faulty metal hips

New York Times reporter Barry Meier knew lawsuits against the manufacturers of all-metal artificial hips were on the rise. But it wasn’t until I queried a balky Food and Drug Administration database that he was able to confirm that all-metal hip implants were quickly becoming the biggest and costliest medical implant problem since Medtronic recalled a widely used heart device in 2007.

The FDA collects voluntary reports from patients, health care providers and medical device manufacturers about problems experienced with specific devices. The federal agency compiles the reports of deaths, injuries and product malfunctions in a database known as ...

Read more ...

First Venture: Local eateries with serious health violations

Every day, thousands of Muskegon County residents pour into their favorite restaurants to dine with friends and family. Yet until now, little was known about whether those restaurants followed practices aimed at preventing people from getting sick.

While looking through more than 22,000 electronic health-inspection records spanning four years, I found numerous instances where restaurants repeatedly violated rules that help prevent foodborne illness. Schools, hospitals and food stands were cited for breaking the rules, too.

Raw chicken and crabmeat sitting out at room temperature. Food kept past its expiration date. Cockroaches, mice and fruit flies living in kitchens. Employees ...

Read more ...

SQLite: simple, open-source database manager

Your average CAR geeks - especially the old timers - follow a predictable route in tools they use for data analysis and sharing.

You start with Microsoft Excel spreadsheets, and everything’s going fine. But then someone tells you about relational databases, and suddenly you notice all of the things you can’t easily do in Excel.

Step up to Microsoft Access database manager and pretty soon you’re joining tables right and left, slipping terms like "Group By" and "normalization" into conversations and generally feeling pretty good about yourself.

But at some point, someone in your newsroom looks over your shoulder ...

Read more ...

Nursing home promises fall short

When the California legislature passed a law to drastically increase funding to nursing homes, it came with a promise that worker wages would rise, staffing would soar and patient care would improve.

The law passed in 2004. When I started working on investigative articles for California Watch in the fall of 2009, it seemed like a good idea to take a close look at whether the promises attached to hundreds of millions of dollars came true.

What we found was noteworthy. State and federal funders poured an additional $880 million into nursing homes over five years, moving the annual funding ...

Read more ...