[postgis-users] Use Of Geography-Based Inheritance

[Robert_Clift at doh.state.fl.us]

Good morning list:
 
I learned of the PostgreSQL table inheritance feature while reading
PostGIS In Action and have been intrigued since. One example in the book
involved storing all geometries in one table (parent) then sorting them
into child tables by geometry type. Linestrings from a parent table were
sorted into state-level child tables by fips code in another example.
 
I have a statewide point data set (parent table: 3M rows, 500 columns) I
need to analyze at multiple scales based on several different spatial
divisions. I'll also need breakdowns by a few key attributes. Here's a
list of planned child table types with counts:
**mainRegions (4 child tables, a few extra columns for demographics)
**subRegions (11 child tables, a few extra columns for demographics)
**counties (67 child tables, a few extra columns for demographics)
****KeyAttributeOne (15 child tables, no additional columns)
****KeyAttributeTwo (5 child tables, no additional columns)
****KeyAttributeThree (2 child tables, no additional columns)

Concerning spatial divisions, we aggregate and report exclusively on the
region, sub-region and county level of the state (see image) but drill
down to the census tract or block level for analysis. There are also
three non-spatial attributes in the parent table that can be used to
meaningfully group the records.

I'm most interested in hearing from those using spatial check
constraints to differentiate child tables. Are there any pitfalls? I
know it will be easier to compose queries for one-off reports with
parent/child tables in place but wonder if the time saved writing
spatial joins justifies the front-end labor.

Also, I assume child-table creation can be automated by looping in a
simple PL/pgSQL function. Can anyone share sample code for a similar
function?

Thanks very much.

-Rob

-------------- next part --------------
A non-text attachment was scrubbed...
Name: stateThreeWays.jpg
Type: image/jpeg
Size: 194745 bytes
Desc: stateThreeWays.jpg
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110616/10ba7cd2/attachment.jpg>