Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Storing time series data

    Hi All,

    I am working on an application that uses climate data stored in a database. The amount of datapoints is quite overwhelming.

    We have 64800 cells to cover the whole panet
    * for each variable (about 20)
    * for each month
    * for each year (100 years total)
    * for each model scenario (3 total)
    * for each model (starting with one but adding more later up to 5-6).
    = A whole lot of datapoints.

    To keep things manageable and somewhat searchable we had decided to use the following structure.
    One table for each model and each scenario for a total of about 15 tables.
    Then each table would have a cell ID (so 64800 rows) and a column for each variable. Then all the data for that variable at that cell (12months*100years=1200datapoints) would be stored in a postgres array. By using the array data type we can call up any month easily.

    Any other suggestions for the data structure, does the above outlined structure even make sense? Relational databases are not that great at handling multidimensional data. When I read about the array datatype in postrgres it seemed like a way to get it to work.

    Bert

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by revenazb
    Relational databases are not that great at handling multidimensional data.
    What a strange comment! Relational databases are all about handling multidimensional data. Each dimension equates to a column in a relational table, so your data could be stored in a table like this:
    Code:
    create table data
    ( cell_id
    , year
    , month
    , model
    , scenario
    , variable1
    , variable2
    , ...
    , variable20
    , primary key (cell_id, year, month, model, scenario)
    );
    I don't know PostgresSQL, but most modern DBMSs have features (not just indexes) to improve performance of queries on large data sets - such as Materialized Views to store pre-computed aggregates.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •