Home  5  Books  5  GBEzine  5  News  5  HelpDesk  5  Register  5  GreenBuilding.co.uk
Not signed in (Sign In)

Categories



Green Building Bible, Fourth Edition
Green Building Bible, fourth edition (both books)
These two books are the perfect starting place to help you get to grips with one of the most vitally important aspects of our society - our homes and living environment.

Buy individually or both books together. Delivery is free!


widget @ surfing-waves.com




Vanilla 1.0.3 is a product of Lussumo. More Information: Documentation, Community Support.

Welcome to new Forum Visitors
Join the forum now and benefit from discussions with thousands of other green building fans and discounts on Green Building Press publications: Apply now.




    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 25th 2014
     
    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?
  1.  
    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.
    :bigsmile:
  2.  
    <blockquote><cite>Posted By: SteamyTea</cite>Anyone an expert on SQL?</blockquote>

    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.
    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 25th 2014
     
    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.
  3.  
    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
    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 25th 2014
     
    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.
  4.  
    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.
    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 25th 2014
     
    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.
    •  
      CommentAuthorted
    • CommentTimeFeb 25th 2014
     
    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.
    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 25th 2014
     
    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 :devil:
    • CommentAuthorEd Davies
    • CommentTimeFeb 25th 2014
     
    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.
    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 25th 2014
     
    Shall have to check that out as it is a useful feature as the not every station has full records from 1850. Thanks
  5.  
    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.
    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 25th 2014
     
    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?
  6.  
    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.
  7.  
    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.
    •  
      CommentAuthorSteamyTea
    • CommentTimeFeb 26th 2014 edited
     
    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.
  8.  
    Have you read this ST, met office data into excel

    http://www.metoffice.gov.uk/climate/uk/stationdata/help_import.html
    • CommentAuthornigelm
    • CommentTimeFeb 26th 2014
     
    SQL express is worth a look, free with a 10GB data limit
    •  
      CommentAuthordjh
    • CommentTimeFeb 26th 2014
     
    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.
Add your comments

    Username Password
  • Format comments as
 
   
The Ecobuilding Buzz
Site Map    |   Home    |   View Cart    |   Pressroom   |   Business   |   Links   
Logout    

© Green Building Press