Re: [sqlite] designing a db to hold repeating data

2009-03-16 Thread P Kishor
On Sun, Mar 15, 2009 at 8:57 PM, Rich Shepard wrote: > On Sun, 15 Mar 2009, P Kishor wrote: > >> CREATE TABLE met ( >>        met_id INTEGER PRIMARY KEY, >>        other met attributes, >>        met_grid_id INTEGER >> ); > >   Given the quantity of data you have I urge you to read Joe Celko's "SQ

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread Rich Shepard
On Sun, 15 Mar 2009, P Kishor wrote: > My only concern is query speed, data integrity (of course, that would be > helped by avoiding redundancy), and ease of querying. If I have a 300+ MB > db, saving 5 or 14 MB doesn't gain me anything. > I want to do something like this -- > > "Give me all the

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread Rich Shepard
On Sun, 15 Mar 2009, P Kishor wrote: > CREATE TABLE met ( >met_id INTEGER PRIMARY KEY, >other met attributes, >met_grid_id INTEGER > ); Given the quantity of data you have I urge you to read Joe Celko's "SQL Programming Style" before you dig yourself into a really deep

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread P Kishor
On Sun, Mar 15, 2009 at 8:28 PM, Jim Wilcoxson wrote: > 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 cel

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread Jim Wilcoxson
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 y

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread John Machin
On 16/03/2009 11:45 AM, P Kishor wrote: > On Sun, Mar 15, 2009 at 7:29 PM, John Machin 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

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread P Kishor
On Sun, Mar 15, 2009 at 7:29 PM, John Machin 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 TABL

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread John Machin
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 attri

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread P Kishor
On Sun, Mar 15, 2009 at 7:16 PM, Alex Mandel wrote: > You should talk with Alessandro Furieri a.furi...@lqt.it who is > currently working on adding Raster support to spatialite. > http://www.gaia-gis.it/spatialite/ > > It may be more efficient to store the data separately in well used > raster for

Re: [sqlite] designing a db to hold repeating data

2009-03-15 Thread Alex Mandel
You should talk with Alessandro Furieri a.furi...@lqt.it who is currently working on adding Raster support to spatialite. http://www.gaia-gis.it/spatialite/ It may be more efficient to store the data separately in well used raster formats and attach them to the database as needed for queries. Ale

[sqlite] designing a db to hold repeating data

2009-03-15 Thread 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