[postgis-users] Enormous file geodatabase feature class

[Webb Sprague]

If I might chime in on a generally good set of ideas...

On Fri, Mar 7, 2008 at 1:58 PM, Obe, Regina <robe.dnd at cityofboston.gov> 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!