#11 Long term historical data - sql

[Jonathan Dewey]

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.

[Anonymous]

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]

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]

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]

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.