[postgis-users] Re: [mapserver-users] can an ogr tileindex refer to a PG table?

[Neil Best]

Milo van der Linden wrote:
> Hello Neil,
> 
> One thing you can do to create some sort of tileindex that can be part 
> of the where clause is create a view in postGIS that contains the 
> boundingboxes of a table based upon an attribute:
> 
> CREATE VIEW MYBBOX AS
> SELECT
> table1.uniqueID,
> extent(table1.the_geom) as box,
> box2d(scale(extent(table1.the_geom),1.01,1.01)) as bbox
> FROM
> table1
> GROUP BY
> table1.UniqueID;
> 
> Where BOX is the exact extent and bbox is the extent + a little margin, 
> this I used to zoom with an area around it because my original MYBBOX 
> view was based on a points table.
> 
> This view could be used as a helper to build the right queries.
> 

THanks for your note, Milo.  I am pursuing your suggestion but having 
trouble optimizing the queries.  It would probably be more apporpriate 
to continue this thread on postgis-users so I am cc-ing there as well.

First, I should mention that I believe your example has a flaw that you 
might want to consider.  As I read it the PostGIS scale() function 
multiplies the x-coordinates by the first factor and the y-coordinates 
by the second resulting in a scaling relative to the origin rather than 
an expansion of the geometry relative to its centroid.  PostGIS expand() 
does this but by taking a single argument and using that as an offset in 
all directions, not a multiplier.

The problem that I am having is that the query planner is not taking 
advantage of the sub-query on my tile index that is supposed to provide 
the short list of attributes to drive the constraint exclusion feature 
for table partitioning.

EXPLAIN ANALYZE SELECT count(*) FROM mapunits
WHERE areasymbol IN (SELECT areasymbol AS a
     FROM tileindex
     WHERE areasymbol ~ '^IA'
       AND wkb_geometry && geomFromText(
           'POLYGON((-47671.875 2252234.375, -40343.750 2252234.375, 

                     -40343.750 2259562.500, -47671.875 2259562.500, 

                     -47671.875 2252234.375))', 32767));

The sub-query by itself is blindingly fast ( < 0.1s) and returns a 
single value 'IA119'.  If I write:

EXPLAIN ANALYZE SELECT count(*) FROM mapunits
WHERE areasymbol IN ('IA119');

it also comes back relatively quickly.  Constraint exclusion kicks in 
and limits the scan to only children whose table constraints satisfy the 
WHERE clause.

The query plan for the full query above indicates that it is doing a 
"Hash IN Join" with "Hash Cond: (ssurgo.mapunits.areasymbol = 
tileindex.areasymbol)" and scanning all of the children of mapunits 
before using the results of the sub-query to limit which children is 
scans.

The big question:  How do I rewrite this to take advantage of constraint 
exclusion in a spatial query?  Ultimately I will use Mapserver CGI 
substitution to  replace the constants in the WKT into the bounding box 
of the requested map, making the sub-query capable of returning variable 
results of indeterminate length.  This is not the full query of my 
application, rather a test that I cooked up to try to understand the 
behavior of the query planner.

As it is my Mapserver application performs tolerably well without trying 
to optimize for constraint exclusion, presumably because it is checking 
the bounding box of the spatial query against the spatial index of each 
child table and moving through them relatively quickly, but it seems 
that there should still be some performance gains to be had.

Thanks, everyone.

Neil