[Mapserver-dev] Re: [Mapserver-users] Mapscript drawQuery() on big PostGIS layers doesn't work properly ?

[Daniel Morissette]

Paul Ramsey wrote:
> David Blasby wrote:
> 
>> Whats happening is that postgresql is using the spatial index (which 
>> presumably returns the entire layer), then does a sequencial scan to 
>> find the rows with the "filter" attributes.
> 
> 
> Incidentally, it is worth pointing out that the currect behavior for 
> query functionality is almost painfully unsuited to database backends. 
> The shapefile heritage really shows through in the query workflow, which 
> if I am not mistaken is "tell me the record numbers of all records of 
> interest (that match the criteria)", "give me record 1", "give me record 
> 2", etc. To emulate this, we do exactly as asked. Give the list of 
> record numbers (unique ids in our case) and then perform repeated 
> queries -- the opposite of efficient, given the capabilities of the 
> backend. Obviously it would be preferable to turn the query into a 
> single resultset and then scroll through it, once.
> 

I may be wrong since it was a while since I played in that part of 
MapServer, but I think it should be possible for the postgis driver to 
do what you suggest.  If I remember correctly that's more or less the 
way the OGR driver works.  When a layer is drawn or a query is done, the 
following happens:

msLayerWhichShapes() is called to initialize the query.  In the case of 
shapefiles this means retrieving the list of shapeIds, but in the case 
of PostGIS it could be just performing the query to the DB.  I believe 
that's what msPOSTGISLayerWhichShapes() does today.

Then msLayerNextShape() is called until there are no more shapes to 
read.  The postgis implementation of this could simply scroll through 
the resultset that was created in msLayerWhichShapes().

Daniel
-- 
------------------------------------------------------------
  Daniel Morissette               morissette at dmsolutions.ca
  DM Solutions Group              http://www.dmsolutions.ca/
------------------------------------------------------------