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

[Paul Ramsey]

Are you confirming that still doesn't work? What I make of it is that
the interaction of the SQL parser, the planner and the constraint
exclusion code might not allow your sub-select trick, but you should
seek the advice of experts on the pgsql list.

P.

On 2/27/08, Neil Best <nb...@lanworth.com> wrote:
> Paul Ramsey wrote:
> > What happens when you remove PostGIS from the equation. This might be
> > a core PgSQL question.

> >
> > EXPLAIN ANALYZE SELECT count(*) FROM mapunits

> > WHERE areasymbol IN (SELECT 'IA199' as a)
> >
>
>
> I think you're on to something there, Paul. Very astute. What do you
> make of this?

[Neil Best]

Paul Ramsey wrote:
> Are you confirming that still doesn't work? What I make of it is that
> the interaction of the SQL parser, the planner and the constraint
> exclusion code might not allow your sub-select trick, but you should
> seek the advice of experts on the pgsql list.
>

Precisely. I will either pursue that advice or settle for the
work-around of issuing the sub-query separately and stuffing the results
back into a subsequent query. Not very satisfactory on the elegance
front but high coefficient of laziness. Thanks, Paul.