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

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

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

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

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

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

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

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

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

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

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

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.

[sqlite] designing a db to hold repeating data

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