Menu

#11 Long term historical data - sql

open
nobody
Databases (3)
5
2003-07-22
2003-07-22
No

I was wondering about adding in long term storage of
fetched METAR data. I think it could be useful to be
able to pull up data from previous dates for comparison
and/or be able to do trending.

There are many possible ways to do it, but I was
thinking to have a selectable feature that enables a
seperate module. For example I want to enable trending
for ICAO KRDU. Using sql this would create a table
Trend-KRDU or such, (would be same for flat file thou
larger and slower). The module would run on an hourly
cron as to not miss any data, and would insert metar,
timestamp, etc, into both the currently used cache table
and the ICAO trend table. It might be easier to put the
decoded metar info into the trend table, that would
make searching a little easier.

From there record highs and lows, strongest winds, you
name it, would be available with basic searches.

Just a thought, I have been trending my local weather
using metar.pl and mrtg, but I can' look for a specific
day, etc.

This looks to be a great program so far, keep up the
great work.

Discussion

  • Anonymous

    Anonymous - 2003-07-27

    Logged In: YES
    user_id=104098

    I agree that it would be cool to store the data for longterm
    analysis and trending. The question is how to do it?

    As it is now, then all the database backends offer the same
    functionality --- I don't know if we can keep it this way if
    we begin with these kind of features.

    One way of handling this would be to give the backends
    methods that give you an iterator to the data stored. The
    iterator returned by the null backend would already be at
    it's end since the null backend doesn't store any new data,
    whereas the proper backends would return a proper iterator
    that would run through the data. The amount of data could be
    limited by given a start and/or end date.

     
  • Jonathan Dewey

    Jonathan Dewey - 2003-07-31

    Logged In: YES
    user_id=800627

    I was thinking about this after submitting the feature
    request. I'm thinking that it would need to be written as a
    seperate utility or plugin to collect and archive the data.
    Most likely with the data already decoded. Then put in hooks
    to the database. IE, user goes to page to get todays
    weather, php sub grabs the temp for the same month/day,
    just a year before... It was a thought I had, never said it
    would be easy. Thanks for the quick reply thou.

     
  • Etienne Tourigny

    Logged In: YES
    user_id=626224

    I think that the recording should be done simply by the
    backend at each insert or update. I've done this in my own
    version. I use a table pw_metars_arch (and pw_tafs_arch)
    that has an extra column (time) which is a timestamp
    YYYYMMDDhhmmss.

    I use this to get the data, although I haven't used this
    aside from consulting the table.

    I would gladly put this in cvs.

     
  • Anonymous

    Anonymous - 2003-09-30

    Logged In: YES
    user_id=104098

    That sounds like a good way to do it. We could then extend
    the retrieval functions so that they take an optional
    argument which indicate when to fetch data from. If the
    optional argument is lacking, then just fetch the most
    recent data available, as we do it now.

     
  • Etienne Tourigny

    Logged In: YES
    user_id=626224

    I've added the insertion in the archive databases in CVS.
    We still have to modify data_retrieval.php, pw_*.php and
    maybe phpweather.php for the retrieval portion. Do we add
    2 class members to data_retrieval such as time_start and
    time_end or implicitly put them in the call of get_metar()
    and get_taf()? I would prefer the first solution, and the
    addition of a set_time() function.

    On another note, I think that there is an unnecessary key in
    the MySQL pw_metars table: UNIQUE KEY icao (icao).
    There's already PRIMARY KEY (icao). The same thing for
    pw_stations and pw_tafs. Can I remove this from pw_db_mysql.php?

     
  • Anonymous

    Anonymous - 2003-09-30

    Logged In: YES
    user_id=104098

    Great work! I don't know about the time_start and time_end
    variables --- I just imagined an extra (optional) argument
    to get_metar(). The argument would be a timestamp and
    get_metar() would then return the youngest report older than
    the timestamp. If no timestamp is given, then just return
    the youngest report as we do now.

    But if you like the other way better, then please implement
    it that way. Or perhaps we could sidestep the time issue and
    just make a method that returns reports x to y, sorted by
    their timestamps. That should be very easy to implement as a
    'SELECT * from pw_metars SORTED BY timestamp LIMIT x, y-x'
    query. We could even have both :-)

    Oh, and I cannot see why we're having that extra key in the
    tables, a PRIMARY KEY (icao) ought to be enough.

     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.