You could eliminate met_grid_id from the cells table and replace it with an expression cell_id/2500. This expression will automatically truncate, giving you met_grid_id whenever you need it. This will save around 5 MB for a 1M row cell table.
Also, queries in the cells table by met_grid_id, if you do that, can be made much faster using the relationship above. If you query directly on met_grid_id, sqlite will have to do a table scan. But if you use the relationship, sqlite can use the index, for example: select * from cells where met_grid_id = N (table scan required) becomes: select *,cell_id/2500 as met_grid_id from cells where cell_id between N*2500 and (N*5000)-1 (can use the cell_id index) I think by the same method you can eliminate met_grid_id from the met table, saving around 14.5MB Jim On 3/15/09, P Kishor <punk.k...@gmail.com> wrote: > 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 > -- Software first. Software lasts! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
