[postgis-users] Loading Tiger2007fe data - Thoughts on usinginherited tables

[Paragon]

Can you cluster on a gist index? Yes you can cluster on a gist index as long
as you don't have nulls.  I do that most of the time.  Actually in later
versions of PostgreSQL, I think you might even be able to cluster if you
have nulls although haven't tried.

Thoughts?  
Well the whole idea with constraint exclusion is to do exactly what you are
saying - and the bounding box would be embedded in the constraint.  I
concluded it doesn't work not because it can't with spatial indexes but
because the constraint exclusion logic is designed to deal with ranges of
the
form

the_geom BETWEEN x and y

or the_geom = y

Rather than 
The_geom && BBOX(....)

So we could create a constraint something like this
pgis_quad_hash(the_geom)  = 'S1'


Where pgis_quad_hash would be some random function that looks up in a grid
table to figure out the quadrants a geometry falls in and hashes those all
together.

Then a function that does

SELECT the_geom && BBOX AND pgis_quad_hash(the_geom)  BETWEEN
pgis_quad_hash_min(BBOX)  AND pgis_quad_hash_max(BBOX)  

Instead of the standard the_geom && BBOX

Well that's the beginning of my thoughts on the topic and I know my last
part is definitely flawed, but you get the idea.


Thanks,
Regina



  

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
Woodbridge
Sent: Saturday, April 05, 2008 11:22 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Loading Tiger2007fe data - Thoughts on
usinginherited tables

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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users