Slashdot Mirror
Large, Free, and Interesting SQL-ready Datasets?
Jon H asks: "I'd like to teach myself various platforms or technologies, involving accessing databases. The problem is, my ideas for projects to learn on usually are boring, toy projects, involving lots of boring data entry in order to create a useful database. Things like personal library databases. This doesn't particularly interest me. It would be much easier if I had a big, interesting dataset which I could load into an SQL database without too much trouble. Then I could spend my time on the php, or WebObjects, or JBoss, or whatever.
I'd like something more real than the usual toy demo databases. Something weighty, 20 megabytes and up, big enough for poor software design to cause performance issues which might not be seen in smaller databases.
Ideally, it'd be in a form that could easily be loaded into an SQL database, perhaps even including a schema. Any links would be appreciated. Do such beasts exist?
I am sure that you will get all the data you ever need from that.
As far as real world data, you'll have to collect it yourself. Start with incoming ethernet packets, and file them away into multiple tables. That'll give you a good dataset. You may also want to try your hand at incoming email, HTTP requests, etc...
The radical sect of Islam would either see you dead or "reverted" to Islam.
If this isn't down to earth enough, write a simple application to fill your database with products from Ebay or Amazon - just change the product id and grab the resulting html, parsing out some identifying features and placing them in your DB.
I hate liberals. If you are a liberal, do not reply.
One I've used for laughs is the USDA Nutrient Database. It gives you, well, nutrient information on just about any food you can think of. It's normalized, and just complicated enough to have fun with.
You're going to have to google it yourself, though.
Best Windows Freeware
nist has a bunch of data. i remember a while ago downloading handwritten characters to make handwriting recognition software. they have data for just about everything, the chemistry data is probably some of the best to put in a relational database. check out: http://www.nist.gov/srd/index.htm
I'm currently generating my own test data and you'll probably have to do the same. Data is precious commodity and the more real it is the less likely it will be publicized. I would be fired if I make my current test data public.
-----
One is born into aristocracy, but mediocrity can only be achieved through hard work.
.. I'm putting together some datasets you can have but I'm not willing to post my email (to avoid spam) or website (to avoid the slashdot effect).
...northwind
Here's all the data you'll ever need, free of charge from the gov. Some appears to be freely available and some is restricted. Have fun.
ftp://ftp2.census.gov/census_2000/datasets/
"Eve of Destruction", it's not just for old hippies anymore...
Use IMDbPY to populate a database with all data from the downloadable files from the Internet Movie Database.
Sports stats are always good.
Frankly, 20 MB is not going to give you performance issues. To realistically test the performance of your engine and your queries/schemas, you need at least enough data to fill main memory and cause disks to be used. Much more would be much better.
Your web logs or even your system logs are good candidates as well, as are the package description and dependency databases for any given Linux distribution, and the bug reports for same. One cool project might be to load the Debian, RedHat, etc dependency databases and merge them together and report the differences. That's a good-sized project with the potential to benefit the free software community.
You owe the oracle * FROM wallet WHERE denomination > 20;
Do you have a spam folder? Save all your spam emails with headers intact. Also export your email address list into a text table. Write perl scripts to parse emails and check for whatever variables you are interested in; what type / size attachment, is the sender in your addr book, does the sender addr appear to be valid or not. You could even try to combine this with your web browser history. The key is that while you still need to build the DB yourself, there's a lot of interesting information you can easily extract with simple scripts and minimal actual work.
Ta!
-petertgaffney
Some folks have used the dmoz data. It is in RDF, so should be fairly flexible enough to get into most databases using most languages and an RDF library.
If you want Movie information, you can grab the data files from the Internet Movie Database.
http://www.imdb.com/interfaces
My father has a large VHS collection of movies whose info he keeps updated in a spreadsheet. It was a lot of fun to link his database to the IMDB one, and start searching for movies that included my favorite actors (and were already in the house).
Another source of data that I use from the internet is daily stock data. If you go to this page Yahoo S&P 500 Info and click Historical Prices you will get a page that lists the historical data in a browser-friendly format. At the bottom of this page is an option to download the data in a comma separated value (CSV) file.
I hear the RIAA has some pretty interesting databases. Obtaining them might be challenging, though...
My mom always said, "Jim, you're 1 in a million." Given the current population, there are 7000 of me. God help us all!
You could probably write a script to use the data from the machine learning database collection from UCI.
Some are large, some are interesting, some are simple, but plenty of data.
http://www.ics.uci.edu/~mlearn/MLSummary.html
Method of processing duck feet
Try www.spec.org, you'll find big HTML tables or CSV files for the specs of various computers and CPUs. This is also pretty useful information say if you wanna compare a Pentium1's floating with sparcstation, or if youre looking on eBay to buy a cheap 64-bit cpu machine that can do a decent job of a firewall.
Newer datasets of the specint can help you decide between a Pentium4 CPU and Athlon machines, and their chipsets. So pretty cool and useful information there.
Also heard of telemetry information, on the heights of various spots of ground above sea level against GPS coordinates.... with that data you can build a 3D world thats realistic. Couldnt find the dataset though.
"Give orange me give eat orange me eat orange give me eat orange give me you." -Nim Chimpsky
...are ideal. Come in a standard XML format, which is easily mutable using perl. While I was learning how to clunk about in databases, I had a simple script which made a call to several sites with RDF/RSS, grab a copy of their feed, and check to see if an entry exists in the DB. You can even learn to account for repeat results, and update existing entries. Watch out, though; making too many calls to a website's feed can make the webmastership unhappy with you. My script ran nightly, at 2 AM EST, and grabbed the news from about a dozen sites (slashdot included).
If you want to get REAL creative, you could hack up a little applescript that reads entries from the DB (through perl), and reads you the news. That was neat to do with my PowerBook, as I could get an overview of the day before on my drive to work.
YMMV.
Informatus Technologicus
The audioscrobbler database is available under a creative commons license.
http://www.audioscrobbler.com
Audioscrobbler is a computer system that builds up a detailed profile of your musical taste. After installing an Audioscrobbler Plugin, your computer sends the name of every song you play to the Audioscrobbler Server. With this information, the Audioscrobbler server builds you a 'Musical Profile'. Statistics from your Musical Profile are shown on your Audioscrobbler User Page, available for everyone to view.
Downmix - The Artscene News Source!
Last semester, my final was to develop a database, and write a front end client to interact with the database.
I didn't feel like entering in all the data by hand so I compiled a database of various stocks, and how they have performed over a few months.
All of my data was obtained through finance.yahoo.com, and they allow you to download historical data for numerious stocks and they provide it to you as a comma seperated file.
Go to the NOAA Web Site and download all the weather data from your area going back many, many years.. its facinating to take the datasets and plot the ranges in temperature, humidity, etc..
What are we going to do tonight Brain?
Catch all ``unusual'' packets on your firewall and log them. Lots of data and interesting things to do in order to find patterns in this aparent chaos.
I use iptables for this, but I'm sure you can do this with all the rest. You could even (as an excersize) try to log it directly to database. I just occasionally scan logs left by syslog-ng.
Robert
Bastard Operator From 193.219.28.162
You can always use genomic data - there's plenty of it to go around for everyone. Following is a link to some downloads for mySQL: http://www.ensembl.org/Download/
The USGS has a huge database of Streamflow data online.
.gov sites, and you'll find all sorts of stuff.
You can pull tables for rivers near you, and see how often they flood.
With a bit of work, you can pull all sorts of things out of the current tiger dataset - for example, there are about 4.8 million unique street/zipcode combinations in the US.
See how many streets near where you live are unique ( two streets just down the road from me - Kentvale and Uthers - appear to be unique).
There's lots of interesting data out there, keep poking around in
There are some great collections of historical climate data out there for free. Here's a source for the Western US (a similiar compilation for the entire US would be great). Some earthquake data can be found here.
Heck, just enter "raw data" into google, along with your topic of choice, and have fun.
Method of processing duck feet
There are numerous utilities to put it into a database for you.
If your using MS SQL server you can get the BigPubs2000 database. If you've ever used SQL Server then you probably know what the Pubs database is, and this is a really big version of it. It's about 200 MB. you can google to find a download site for it.
DAFIF data contains all sorts of aviation related airspace and airport information. Here's a link:
f m
https://164.214.2.62/products/digitalaero/index.c
Make some neat tools for that and a zillion simmers (and lots of poor pilots like me) will love you forever. Check out X-Plane while you're at it. Or even better, the open-source Flight Gear could probably use your help!
1. 2.
You could download the human denome database from the NCBI. All the files are here.
Slashdot: Failed Car Analogies. Amateur Lawyering. Anecdote Battles.
Have a look at the wonderful world of bioinformatics, where (hopefully) you should be able to find an array of academic institutions publishing their data for peer review.
To pick just the one place I'm vaguely familiar with, try Boston University's BMERC lab, which publishes both raw genomes and MySQL databases. BMERC's main genome.sql.gz file is 119,294,059 bytes (113 mb, compressed), which should be well into the "large dataset" category you're asking for :-)
There are surely many other schools publishing similar data if you poke around a bit.
Of course, at that point, you start to be bound by the problem domain. Sure, you have lots of data and that's all well & good, but what does it mean? What sorts of analysis can you usefully do on it? Without a biology background, maybe not much, but it's an interesting field and you should be able to give yourself enough of a crash course to make something useful out of it...
Have fun!
DO NOT LEAVE IT IS NOT REAL
A problem I enjoy pulling out to play with now and then is doing a usable data set that can do reverse IP lookup to geographic location . . . there are services that offer it, but there isn't any reason you couldn't write stuff to do this yourself.
.). Between whois info, dns, and trace routes alone you can have some fun doing the mappings . . .and in the end, you have a useful and marketable dataset that you created . . . IP -> Geography is valuable. Once you've got the data though, there's even more . . .how do you manage data sets that large? You can't just throw every IP into a single table . . .not unless you are the index king . . .
.I am so amused.
Big data sets with a lot of the information being available through publicly queryable sources.
There are lots of approaches to mapping it all out (start with class A, then to B, and onward or you can start with specific ranges . .
I don't know of a ready made data set of this nature, but putting the data together into the dataset and loading it is gonna teach you as much about the languages you are using to load it as it will about the data and managing it via sql services. . .
"interesting data set" . .
30 posts and nobody mentions the Northwind Database that comes with MS Access? You can download it and set it up as an ODBC source, which you can use with pretty much anything. Well, provided you're using Windows. ;)
Northwind Access 2000 Download page
"Times have not become more violent. They have just become more televised."
-Marilyn Manson
A dump of the Wikipedia database is available. It's certainly big, and the content is interesting, although the structure isn't.
But then again, I could be wrong.
Seriously. you can search for some online order entry terms, then if you know the default web pages you can get the location of their database. then DL it. No joke. Learned that a guy 4 blocks from me orders some of the most disgusting stuff. shudder. About 1 in 12 of the ones I tested had this thing wide open. I e-mailed a few of them warning them....far as I know they did not make any changes. Go figure.
step 1: install windows xp on PC.
step 2: turn on application crash logging.
step 3: give to mother
step 4: ??? (aka wait 3 months)
step 5: collect 3gb dataset.
Hey Taco! Looks like you're using the "infinite monkeys and typewriters" scheme to generate Ask Slashdots again...
You can download the entire Wikipedia database. It weighs in at 15GB if you include all revisions, or 600MB with just the newest copy of everything. Have fun.
There are plenty of astronomical surveys that are in the public domain. For example, the Sloan Digital Sky Survey (google "SDSS SQL") has its public release in SQL format. I am not sure how they bundle it for download and offline access -- the full set in in the TB, so you would have to make some serious cuts.
Look around for an astronomical survey that interests you. Learn a little of the science so that you can think of interesting things to play with (or just read some of the associated papers to repeat their results.) Keep an eye on the science press to hear about new (and old) surveys, and then look up their homepages to see if they have released the data to the public. Oftentimes, data is released with a paper, so look on xxx.lanl.gov as well.
If you come up with interesting educational uses and a nice interface, consider putting it up on the web and dropping a line to the coordinator. Most surveys don't have dedicated public education staff. (On the other hand, most surveys are very busy, so don't expect a rapid response.)
Important: some of the sets are huge. Be conservative with the bandwidth. Don't download gigs and gigs over and over again, and if you have a choice, pick "off peak" times to grab a set. Also, the surveys usually have their hands full already, so don't expect to get any help with untangling the data -- if a set is not making sense or is poorly documented, your only real option is to move on to another one.
Protect your liberties. Donate to the ACLU
My recommendation is to install Zen-Card on the system.
Zen is a full featured e-commerce solution and is opensource and written in php. When you install it you can choose to have it populate a demo database which has quite a bit of stuff in it. (100s of products).
I don't think its the pinnacle of database engineering but if all you are looking for is mock data there you go.
There are a few related tables available for download, but I mostly use it for Name/Address test data.
Dean
The freedb.org database is available for download.
// TODO: fix sig
I do a little work with environmental data, and in my travels I found this site. It has a huge amount of GIS data collected from various sources for the purposes of mapping various parameters, such as political boundaries, vertical relief, drainage networks, rail/road/piping networks, etc. The only downside is that the data is aging, and the free stuff only goes down to 1:1000000, but its a neat repository if you need a rough map drawn.
Have fun.
I swear, if I see another Slashdot comment with "It will be interesting to see"...
Build a log bot, run it in a few large channels on Freenode.
I'm sure I'll find plenty of stuff to work with.
n load/sr14dnld.html
f m
As a public service, here's a summary of the links I found most interesting:
Food: http://www.nal.usda.gov/fnic/foodcomp/Data/SR14/d
National Inst. of Standards & Tech: http://www.nist.gov/srd/index.htm
Census data: ftp://ftp2.census.gov/census_2000/datasets/
Open Directory: http://search.cpan.org/~ldachary/Catalog-1.02/ and http://rdf.dmoz.org/ and http://amix.dk/codecrib/dmozparser.php
Machine Learning: http://www.ics.uci.edu/~mlearn/MLSummary.html
Music Preferences: http://www.audioscrobbler.com/
Weather: http://www.noaa.gov/ and http://www.wrcc.dri.edu/climsum.html
Genomics http://www.ensembl.org/Download/
Stream flow http://waterdata.usgs.gov/nwis/rt
Earthquakes http://www.ngdc.noaa.gov/seg/hazard/earthqk.shtml
Aviation https://164.214.2.62/products/digitalaero/index.c
Human Genome ftp://ftp.ncbi.nih.gov/snp/
Boston U bioinformatics/genome ftp://mcclintock.bu.edu/BMERC/mysql/
Wikipedia http://download.wikimedia.org/
FCC Licensing http://wireless.fcc.gov/cgi-bin/wtb-datadump.pl
Freedb http://www.freedb.org/
GIS http://data.geocomm.com/
Check out the Nat'l Imaging and Mapping
h tm l
Agency (NIMA)'s database of placenames:
http://earth-info.nga.mil/gns/html/cntry_files.
This is the db (200M compressed, 700M un) of
foreign placenames. I actually used the US
placenames file that NIMA has (don't have the
URL, but it should be free for US residents)
for zips, counties and cities of the US. Each
"placename" has the latitude and longitude as
well.
Note that db's like these need to be scrubbed
and massaged before they can be properly
read into a relational db. That's where the
true expertise comes in; that's also where
perl shines as a language. Once you get the
data clean and in, everything should be easy
after that.
Note that I had a paying gig that worked with
this NIMA data as the basis for a worldwide
db for locating the nearest service center to
a user's location. Learning to be able to
compute distance from two lat/long coord
pairs is an interesting and real-world
exercise.
Good luck,
bmac
. . . the U.S. Department of Justice has a foreign lobbyist database that should be big enough to test with.
CEE5210S The signal SIGHUP was received.
Want a whole bunch (most) registered domain names in the world? You'll need to fill out some forms and wait maybe a week (except edu), but it's worth it. Click for biz, edu, int, info, org, com, net. These files are whoppers for the most part. Perl would not read the com file under redhat 6 its' so big. I use them for my surf engine, iconsurf.com.
Isn't the TPC-C or some similar sql-based (like crashme) database benchmark closer to what you need?
Ever tried DBMonster? its a java app - you tell it your schema, how many rows you want, maybe something to seed the randomness, then off it goes and fills your database with dummy data.
You can go as far as integrating with DBUnit for ant/maven build tasks too...
I think its a sourceforge project, but Google is your friend.
~~~~
Andy Rabagliati
I think that the do not call list would be a great data set with a bunch of records and non-random data.
Did you mount a military-grade, variable-focus MASER on an unlicensed artificial intelligence?
http://www.amazon.com/gp/browse.html/002-9937486-2 304013?node=3427431
I had been parsing amazon webpages until i found this link. Just pull of all the data on 10000 books or something. This is better than boring data that MIGHT have a commercial purpose.