[sqlite] designing a db to hold repeating data

[P Kishor]

I have a grid of 1000 x 1000 cells with their own data as well as 20
years of daily weather data (20 * 365 = 7300 rows) for each contiguous
group of 50 x 50 cell.

CREATE TABLE cells (
        cell_id INTEGER PRIMARY KEY,
        other cell attributes,
        lat,
        lon,
        met_grid_id INTEGER
);

cell_id met_grid_id
------- ------------
0       0
1       0
..
2499    0
2500    1
2501    1
..
4999    1

CREATE TABLE met (
        met_id INTEGER PRIMARY KEY,
        other met attributes,
        met_grid_id INTEGER
);

met_id met_grid_id
------ -----------
0      0
1      0
..
7299   0
7300   1
7301   1
..

CREATE VIRTUAL TABLE cell_index USING rtree (
        cell_id INTEGER,
        minx REAL,
        maxx REAL,
        miny REAL,
        maxy REAL
)

The db is about 350 MB with the cell table with 1000,000 rows and the
met table with 2,920,000 rows and the R*Tree index.

Is there any other better way that jumps out at any of you?


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users