Green Building Forum - SQL

SQL

SteamyTea — Tue, 25 Feb 2014 14:05:10 +0000

I have access to the Met Office weather database. This sounds impressive but is really just a bunch of text files. Large text files mind, and this is the problem. Once I have merged each years files (The DOS Copy command is just brilliant, had forgotten about it) I end up with a file that can be approaching 10 GB. This is too large for Access or Excel to handle.
I was wondering if one of the flavours of SQL could handle it. And if it can how easy is it to use.
All I really need to do is some conditional searching on location and time and maybe a bit of stats work on them, though Excel does this nicely (and charting).
Anyone an expert on SQL?

SQL

jamesingram — Tue, 25 Feb 2014 14:14:18 +0000

Posted By: SteamyTeaI have access to the Met Office weather database.

Excellent , so tell me, is it ok to strip some chimney flashing this afternoon or will it rain later ? thames valley area.

SQL

Paul in Montreal — Tue, 25 Feb 2014 14:22:15 +0000

Posted By: SteamyTeaAnyone an expert on SQL?

If you're on a 64-bit platform you're good up to between 2 and 4TB for the database size.

You'll have to design a table structure for the data, but it should be fairly straightforward.

http://dev.mysql.com/doc/refman/5.6/en/table-size-limit.html

http://www.phpmyadmin.net/home_page/index.php is useful to admin a database and run queries.

Paul in Montreal.

SQL

SteamyTea — Tue, 25 Feb 2014 14:45:45 +0000

On 32 bit on the old Desktop.

Any particular flavour I should download, was going to go for MySQL.
I can install Linux if that would be easier.

SQL

Paul in Montreal — Tue, 25 Feb 2014 15:20:00 +0000

I'd say go for MySQL - you might want to go the linux route with LAMP (Linux, Apache, MySQL, PHP) - as this is a very common combination for web-based work, though you probably don't need Apache if you're not hosting a website.

See http://www.mysqlperformanceblog.com/2011/12/08/which-linux-distribution-for-mysql-server/

Paul in Montreal

SQL

SteamyTea — Tue, 25 Feb 2014 15:24:04 +0000

Thanks Paul, shall take a look.
As I use NTFS, I should be OK to handle the big files.
Do you know of a good tutorial as I can't find one, the 3 sites I looked at jump straight into the commands. I need to know how to set it up from scratch.

SQL

Paul in Montreal — Tue, 25 Feb 2014 15:29:09 +0000

I haven't had to do it myself - all the MySQL databases I use are on hosted webservers so there was nothing much to do.

However, take a look at these:

http://www.elated.com/articles/mysql-for-absolute-beginners/

http://www.dreamincode.net/forums/topic/42301-beginners-guide-to-creating-simple-mysql-tables/

Paul in Montreal.

SQL

SteamyTea — Tue, 25 Feb 2014 15:40:25 +0000

That first one seems to make sense already.

I am sure that importing data is not that hard, already done it in Access. Actually linked to the *.csv files.

SQL

ted — Tue, 25 Feb 2014 15:52:22 +0000

Most of the MySQL admin tools are able to import directly from csv files.

You will probably want to play around with small files first rather than 10GB in one go.

SQL

SteamyTea — Tue, 25 Feb 2014 15:55:18 +0000

Yes, I have learn to test on a few lines of data, then a few hundred. Spent way to many mornings crying into my tea after another wasted night of processing. I usually forget what do to in case of an error

SQL

Ed Davies — Tue, 25 Feb 2014 16:43:56 +0000

I know next to nothing about relational databases (probably biggest single gap in my computing knowledge) but, yep, from what I understand MySQL is the obvious choice. The only plausible case for something else I can think of is that PostgreSQL has plug-ins which deal with geographical positions - e.g., find all records with lat/longs within 50 km of this point. If that's relevant it might be worth a look.

SQL

SteamyTea — Tue, 25 Feb 2014 17:20:21 +0000

Shall have to check that out as it is a useful feature as the not every station has full records from 1850. Thanks

SQL

bot de paille — Tue, 25 Feb 2014 17:52:19 +0000

A while ago I downloaded the full precipitation record for the UK going back to 1850, in txt file. I couldn't figure out how to get all the data into usable database.

Maybe you could try ST, would be interesting to see what it shows.

SQL

SteamyTea — Tue, 25 Feb 2014 18:14:52 +0000

The Rainfall one is pretty 'busy', most of the data is the QA side and once filtered can be ignored. Do you know if it was rain gauge of river gauge data?

SQL

Andrew_Doran — Tue, 25 Feb 2014 22:50:29 +0000

Depending on the data formats and such in the file, you may have to tell the database how to interpret/convert a few fields when you import the file, such as dates for example. If fields are not important, best to skip them as it will help keep the table size and your query times down.

To enable queries to run in a reasonable time frame you may need to create some indexes, which act like an index in the back of a book does for a human. For example, an index on the "date" field sounds like it would be a good candidate given that you mention doing queries by date. I'd try a few queries once up and running and see.

More techy alternatives to MySQL would be PostgreSQL as mentioned (lightweight, more featureful), or IBM DB2 Express-C (not "Express", lots of documentation, tools) which is available free for personal use. SQL query syntax is very similar between all of the products until you start getting into esoteric stuff.

SQL

bot de paille — Tue, 25 Feb 2014 23:13:56 +0000

Posted By: SteamyTeaThe Rainfall one is pretty 'busy', most of the data is the QA side and once filtered can be ignored. Do you know if it was rain gauge of river gauge data?

No idea, though I had the impression the data was rainfall.

There was a lot of data in the file, every single day for every single month going back to 1850.

I think now they have changed the Met office website so that you need to register to get the data.

SQL

SteamyTea — Wed, 26 Feb 2014 06:56:25 +0000

Posted By: Andrew_Doransuch as dates for example

Yes, I found this out, the date comes as YYYY-MM-DD hh:mm but Excel prefers (and I) DD/MM/YYYY hh:mm. Though I am not sure how to get around dates before 1900 when using Excel.
I don't mind using Access, just the file size is the problem.
Good tip about skipping data, had not thought of that one.

SQL

bot de paille — Wed, 26 Feb 2014 11:51:06 +0000

Have you read this ST, met office data into excel

http://www.metoffice.gov.uk/climate/uk/stationdata/help_import.html

SQL

nigelm — Wed, 26 Feb 2014 12:54:23 +0000

SQL express is worth a look, free with a 10GB data limit

SQL

djh — Wed, 26 Feb 2014 13:39:41 +0000

Since MySQL was bought by the evil Oracle empire, it is slowly being replaced by MariaDB, which is a work-alike by the original developer. But either work OK. A quick look shows I have one 20 GB instance lying about. There's also sqlite if you're just messing around.

Oh and you ought to stick with the ISO date format; it will make your life easier. Translate it to an old-school format when printing or whatever if you insist.