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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
