4

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.

State of Florida Divided Three Ways

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.

-Rob

Improve this question
3
  • Are the divisions a geography or simply an attribute? Exactly how are you thinking of setting up the inheritance? What does the parent table look like? What do the child tables look like?
    – Sean
    Commented Jun 15, 2011 at 18:17
  • It's hard to tell what you're doing here but I'm pretty sure inheritance is the wrong approach. Why are there so many tables? How can you have a child table that doesn't have additional fields beyond what's in the parent?
    – Sean
    Commented Jun 16, 2011 at 15:59
  • @Sean -- Facepalm. Can do what I need to by adding a couple of varchar columns to my main set of points. But, in regard to your last question, "CREATE TABLE two() INHERITS(one);" will do just that.
    – rec.thegeom
    Commented Jun 17, 2011 at 20:24

1 Answer 1

Reset to default
4

Rob,

I guess it depends on the amount of data you are talking about and why you are doing it. BTW -- the tiger_geocoder packaged with PostGIS 2.0.0 does use inheritance for geographic separation (the main reason we do it is for reason 1,2, 5). My sense is that inheritance may be overkill for your needs.

The reason we use inheritance in our work is:

1) Easier loading -- if you load data (especially read-only data) by region, its way easier with inheritance since you can just truncate the child table or even drop it. when you are talking about millions of records having to delete those with the slow DELETE FROM ... that is a huge advantage.

2) Another reason is for regional break out, making those queries faster. For 600,000 records, there isn't much of a benefit in speed there I don't think. When you start getting a bit more like a million, it makes a lot more sense.

3) Archiving -- by date range -- you want to keep data you have from like 10 years ago and do a lot of date range queries. You don't want that old data you rarely query affecting the speed of your newer data. Keep in mind this only works if you often query by date ranges.

4) Another one that you brought up -- having the option to store more data about children than the parent. For this reason -- you might be right and inheritance is the best solution. For this its always a decision between having a bunch of empty columns rarely used in one table or using child tables and querying the child tables directly.

5) Abstraction -- the ability to remove tables without changing the interface of your applications. So for example take the tiger geocoder we have -- we created a tiger schema that has all the parent tables. One advantage of that is next year I can disinherit all my 2010 tables and replace them with 2011. I may want to keep the 2010 around for historical reasons. In fact I can disinherit piece meal -- like disinherit 2010 FL data and replace with 2011 FL data simply by inheriting 2011 and keep all the others as is. This is absolutely wonderful for processes that take a long time. I call it the tire replacement model.

Improve this answer
1
  • Thanks very much for the thoughtful answer--gives me a better understanding of inheritance. I was hoping to pre-group my points by location to avoid running ST_Within more than once per division area, without: a) taking up more disk space than necessary; or b) hiding the inefficiency of the spatial query behind a bunch of views. It has only just occurred to me that I can do all of that by relating the point records to the id fields of the various divisions.
    – rec.thegeom
    Commented Jun 17, 2011 at 20:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.