hope everyone's doing well.
I have a statewide point data set (parent table: 600K 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 (48 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 analyze the data at the census tract or block level. There are only two attributes in the parent table that can be used to group the records in any meaningful way.
It's my understanding that implementing an inheritance strategy will allow for easy aggregation by geometry, attribute, or a combination of both. I wonder, however, if the easy ad-hoc queries will be worth the work required up-front.
PostGIS In Action (awesome resource) gives a good example of a linestring table sorted by state_fips but I haven't heard any firsthand accounts of using inheritance with geometries.