If I might chime in on a generally good set of ideas... On Fri, Mar 7, 2008 at 1:58 PM, Obe, Regina <[EMAIL PROTECTED]> wrote: > Dana, > > Have you thought about using inherited tables and constraint exclusion. > It has a couple of advantages over loading everything in one table
The only problem is that primary keys and foreign keys are kind of broken in Posgtres if you use the built in "inherits" facility. I haven't built a parent-children set up recently without the inherits keyword, but I think it is pretty easy; I would avoid the built in functionality until indexes (and thus PK constraints) can be inherited. Such a set up requires some TRIGGER and VIEW magic, but I think avoiding the INHERITS keyword is worth it (I have regretted using the inherits functionality when I wanted to set up primary key constraints on the parent table that propagate to the children, if I remember correctly). I have never set up partitioning, but that might be worth it with TB of geographic data, in which case you need (I *think*) INHERITS?? Or PL/Proxy? With those clarifications or complications, everything Regina says is, I think, a good idea (as usual). > 1) Your master table can have fewer fields than the other tables, that > way you can have the core fields in master and still maintain some of > the other fields for the counties that vary. Yes -- and construct "all counties" or "statewide" or whatever views for national or regional level analysis. > 2) It makes dropping data a lot easier - e.g. if I need to reload say a > county, I can simply drop the child table that holds the data or just > truncate that child table. TRUNCATE TABLE operation on the child table > and is much faster than a DELETE FROM since it for the most part is not > logged. Cool. > 3) With constraints in place that constrict the bounding box of each > county or by county code or whatever, your queries will be much more > efficient. I presume most of the time you'd be looking at a particular > region. Nifty idea -- a check constraint on the geometry of the child table? Cool! _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
