[postgis-users] Loading Tiger2007fe data - Thoughts on using inherited tables

[Stephen Woodbridge]

I had not seen this before, thanks!

I seems from the discussion on this that it does not work with spatial 
data. I had the idea that it would be cool if each table kept its 
current bbox for the table and that if you were doing a query that it 
would only look at the tables with bboxes that overlap the query area of 
interest. And if each of the tables were index then you should get 
pretty good performance when using reasonably small areas of interest. 
This is not really "constraint exclusion".

Any thoughts on doing this?
Would it be useful to support inherited tables with something like this? 
or do you already get this if you just build a gist index and cluster on 
it?
Can you cluster on a gist index?

With Tiger I think the real issue becomes managing all the data and all 
the tables. With 3300+- counties and just and about 5-6 layers per 
county plus state and national layers, you are looking an nearly 15-16K 
files. Rolling county level data up to the state level improves this a 
lot, but disassociates the data from its original file if that is 
important to anyone. One use case for know this association is when 
Census releases an updated county to fix some internal issues they 
found. It would be nice to be able to easily drop that one file or 
county and reload the new data. In most cases we are using Tiger as load 
once, read a lot and not making edits to it.

-Steve

Paul Ramsey wrote:
> Look at "constraint exclusion" as a strategy...
> 
> http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
> 
> It actually *does* use inheritance, but it does fancy query re-writes too.
> 
> P.
> 
> On Fri, Apr 4, 2008 at 11:10 AM, Stephen Frost <sfrost at snowman.net> wrote:
>> Stephen,
>>
>>
>>  * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
>>  > Have look into using inherited tables? This would allow you to have a
>>  > nation view or a state view of the county data. View being used in the
>>  > literal not postgres in this case.
>>
>>  Honestly, I havn't.  I'm really not all that keen on inheiritance in PG
>>  anyway.  What I'm probably going to do instead though, so you know, is
>>  combine all of the county-level data into state-level tables, and then
>>  create a view across the state tables to generate a national table.
>>  This partitioning should make things go pretty quickly, imv.  I'll
>>  certainly let folks know if I'm happy with it or not.
>>
>>         Thanks,
>>
>>                 Stephen
>>
>> -----BEGIN PGP SIGNATURE-----
>>  Version: GnuPG v1.4.6 (GNU/Linux)
>>
>>  iD8DBQFH9m8DrzgMPqB3kigRAhj1AJ9upuYPkTNrzLQWv74QW1hewjtxJQCfXCZy
>>  feifOYH9dPLDphnwVOd788M=
>>  =izsS
>>  -----END PGP SIGNATURE-----
>>
>> _______________________________________________
>>  postgis-users mailing list
>>  postgis-users at postgis.refractions.net
>>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users