I agree. I have done similar work for GIS data. Unless you absolutely need access to inidvidual x,y data at the *sql* level, it is much better to use memory storage for these. And, if you plan to do operations like point-in-polygon, which I am sure you will, you need access to all the points at the same time, which means that streaming them into a BLOB will always be much better. One tip: if you can, use a binary storing technqiue instead of a text//based one. Converting to/from floating point numbers is a rather expensive operation. So, serialize the points to a data structure, and write it with one operation.
It helps if you violate some design rules, when you do this. Consider this: In Delphi, which is my native language, the *good* design (theoretically speaking) would be this : Type Tpoint=class x, y:double End; Tpolygon=class points:tlist; ... Other properties and methods End; Or even : Tpolygon=class points:array of tpoint; End; However, this design does not serialize wellm as each TPOINT element is not just data, but has "hidden" pointer that cannot be serialized Now, consider this: Tpoint=RECORD x,y:double End; Tpolygon=class points:array of tpoint; End; Now, "points" is a sequencial array of records in memory, so you can stream to/from a blob with a single operation... > -----Original Message----- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 17, 2005 5:30 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] BLOB versus table storage > > On Thu, 2005-02-17 at 10:12 -0500, [EMAIL PROTECTED] wrote: > > Does anyone have a recommendation as to which solution > would be more > > optimal, both for space and processing time? In a worst case > > scenario, I could insert over 1 million polygons, resulting > in a max > > 20 million point list table. > > > > I think storing the points in a BLOB will likely be much > faster and more compact too. The downside is that you cannot > query for polygons by point values (unless, perhaps, you > create some custom SQL functions to scan the > BLOBs.) > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > >
