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 hole. For example, your met_grid covers a defined land area. It would be much better to use the geographic coordinates (lat/lon, UTM, State Plane) of the center of each grid as the primary key. On the other hand, if all your information is associated with the met_grid, then its center should be the unique identifier. Your *_id are artificial and convey no useful information. > 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. What you could do is have a Grid table whose primary key is the center of the central met_grid, and which references all the other met_grids it contains. Given your schema above, how can you tell that a specific 1 km**2 met cell belongs to a specific 2500 km**2 grid cell? The numbers are meaning less. Geographic coordinates use real, meaningful data to associate all 2500 met cells in each grid cell. > 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. Then your Met table contains 7 columns (one for each attribute), plus the geographic center columns (Northing/Easting, Lat/Lon), and the date. That's 10 columns and 7300 rows. Now you have a start toward a normalized table. I suspect that your thinking has been shaped by trying to use spreadsheets as databases, and thinking of SQL as a procedural language rather than a declarative language that works on sets of tables (rows, columns) instead of files (records, fields). Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
