On Sun, Mar 15, 2009 at 7:29 PM, John Machin <[email protected]> wrote: > On 16/03/2009 11:00 AM, P Kishor 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. >> > [snip] >> >> CREATE TABLE met ( >> met_id INTEGER PRIMARY KEY, >> other met attributes, >> met_grid_id INTEGER >> ); > > [snip] > >> 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? > > How many unique sets of "other met attributes" are there? If the answer is > substantially less than 2,920,000, you could consider parking those on yet > another table, referenced from the "met" table. >
I am not sure what that gains me? Imagine a million square kms area divided into 1 sq. kms. cells. Those are the land cells. Now imagine another overlay, the met grid, on top of the land grid. The met grid is also a million square kms., but is tessellated into 2500 sq. kms. cells, that is, a 20 x 20 grid. I have 7 met attributes for every day of 20 years (that is, 7300 rows) for each "met_cell". So, imagine a stack of 7300 rows sitting on each met cell. In other words, each set of 7300 rows is related to a block of 2500 land cells. Hope that helps picture the problem. -- 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
