On Sun, 2005-06-12 at 09:12 +0100, Jonathan Gregory wrote:
> This is like storing all the timeseries in a single SQL table, when you would
> extract the timeseries for a particular station using
> select time,measure1 where station=something;
> Is that how you did it in SQL, Magi?
>
I use two main tables, one for the measurements and one for the
locations:
for the measurements I store
measure_id, loc_id, time, observation, errors
and for the locations:
loc_id, name, long, lat
I've built indexes on loc_id and (long, lat)
Searching the database is straight forward. So for example to get all
time series for specified geographic region, you can do:
SELECT * FROM location WHERE long BETWEEN %f AND %f AND lat BETWEEN %f
AND %f
etc. It might not be the fastest, but logically it seemed to make most
sense to me. I think the fundamental problem is that time data are not
as well structured as gridded data which makes it hard to store it in a
data format designed to be used with gridded data. I guess it will
really depend on the nature of the time series, for example storing a
fixed number of time series where measurements are taken at the same
time will be straight forward. In my case I had to store relative sea
level observations, with different numbers of observations at different
times with different associated measurement errors. I also store
additional information like authors and name of the collection of data
in separate tables.
Cheers
magi
--
Magnus Hagdorn
School of GeoSciences
The University of Edinburgh
Grant Institute
West Mains Road
Edinburgh EH9 3JW
Scotland
PHONE: (+44) 131 650 8523
FAX: (+44) 131 668 3184
email: Magnus.Hagdorn at glg.ed.ac.uk
web: http://www.glg.ed.ac.uk/~mhagdorn
Received on Sun Jun 12 2005 - 05:27:23 BST
