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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> wrote: >> Stephen, >> >> >> * Stephen Woodbridge ([EMAIL PROTECTED]) 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 >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
