[Mapserver-users] Re: Mapscript drawQuery() on big PostGIS layers, oid indexes, and getResult()

[Paul Ramsey]

Probably there is a third solution.
What you are doing is coloring your map based on a criterion, right? 
Well a query and draw query is one way to do that. Another way is a SQL 
statement which creates an attribute that has one color for features 
that meet your criterion and another color for features that do not.  
By dynamically changing your PostGIS SQL statement you can get many 
"query like" effects in a more efficient way than having mapserver do 
them.
Paul

On Thursday, October 16, 2003, at 03:57 AM, Daniel Faivre wrote:

>  Great thanks to David and Paul for their good advices. As I used 
> "using unique", I've wrongly thought that indexing oid wasn't 
> necessary. With an index on oid, my problem was "half-solved": it's 
> now possible to use the drawQuery() on my big layer.
>
> But there's still a problem: how could I properly retrieve fields 
> values for all queried features ? The problem is that if I use 
> $my_big_layer->getResult($idx) in a loop to get the features ids, 
> execution time is too long.
>
> I see two ways to solve this last problem:
> 1) Adding a PostGIS query after the similar Mapserver query, and using 
> PostGIS results for db processes and Mapserver result only for drawing 
> ?
> 2) Adding another web page just to do the same query a second time 
> without any other task, to break the timeout barrier ?
>
> Which is the best idea ? Is there a third solution ?
>
> Best regards,
>
> -- 
> Daniel Faivre
> Ohceo - Camptocamp
> 04 79 75 28 19
> 180 rue du Genevois
> 73000 Chambéry le Haut
>
>
>
>
> David Blasby wrote:
>
>> Paul Ramsey wrote:
>>
>>>   Cost analysis. On large enough tables and complex enough queries,  
>>> cost analysis gets important. A PostGIS installation with the STATS  
>>> option enabled and stats calculated can do the "right thing" in 
>>> query  planning which a naive installation completely screws up.
>>
>>
>> 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.
>>
>> The most likely problem is you dont have an index on OID:
>>
>> CREATE INDEX mybigpolygon_table_oid_idx ON mybigpolygon_table (oid);
>>
>> You might find that turning on stats will give you speed 
>> improvements. To do this:
>> 1. make sure your postGIS makefile has "USESTATS=1" set
>> 2. in postgresql, do a "SELECT UPDATE_GEOMETRY_STATS();"
>> 3. in postgresql, do a "VACUUM ANALYSE"
>>
>> If this doesnt work, I'll need more information.  Turn the layer's 
>> DEBUG flag to on, and run your script.  You should see the SQL query 
>> go by (it starts with "DECLARE CURSOR mycursor FOR ...".
>>
>> If it says "DECLARE CURSOR mycursor FOR SELECT the_geom,oid FROM 
>> mybigpolygon_table", then go into postgresql and type:
>>
>> EXPLAIN ANALYSE SELECT the_geom,oid FROM mybigpolygon_table
>>
>> (I replaced the 'DECLARE CURSOR mycursor FOR' with 'EXPLAIN ANALYSE')
>>
>> The output will tell you what postgresql is doing and how long its 
>> taking.
>>
>> dave
>>
>>
>>
>> _______________________________________________
>> Mapserver-users mailing list
>> Mapserver-users at lists.gis.umn.edu
>> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>
>
> -- 
> Daniel Faivre
> Ohceo - Camptocamp
> 04 79 75 28 19
> 180 rue du Genevois
> 73000 Chambéry le Haut
>
>
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632