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

[Milo van der Linden]

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.



Wouldn't it be more performance effective
Neil Best schreef:
> Last fall I got some help for the list on how to use Postgres as a 
> source for a tile index that gives locations of raster files on disk 
> and that has been working beautifully.
>
> My new question is this:  Is it possible for a tile index location to 
> indicate something other than a path to a file, such as a PostGIS table?
>
> The reason that I ask is that I have implemented table partitioning 
> for large county-by-county (or smaller) data sets such as SSURGO and 
> USDA CLU.  At the outset I convinced myself mistakenly that Postgres 
> could leverage spatial check constraints for query planning using the 
> constraint_exclusion = on mechanism, but it can't.  It only works with 
> fields where the check constraint is based on a straight 
> equal/not-equal comparison to a constant, AFAICT.  Neither && 
> ::geometry or ~ ::text checks work for this reason.
>
> Getting Mapserver to leverage this advanced query planning means 
> adding logic to my application to inject appropriate values into a 
> WHERE clause in the layer's DATA query string.  To do this I need some 
> kind of generalized layer that indicates the spatial organization of 
> the attributes used for the partitioning.  This is essentially a tile 
> index, so if I could just create a layer of bounding boxes for each 
> table and let Mapserver decide which tables it needs to scan rather 
> than adding complexity to my application in order to have the query 
> planner do the same thing, that would be ideal.
>
> Anybody have any thoughts or experiences to share?  Thanks.
>
> Neil
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>