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

[Paul Ramsey]

Indexes, indexes, indexes.
   Do you have indexes on the columns of interest (spatial column,  
unique identifier (or oid), and any attributes you are querying on?) if  
you have not declared a unique identifier, then the oid will be  
silently used, and you will need an oid index (create index tbl_oid_idx  
on tbl (oid)).
   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.

Paul

On Wednesday, October 15, 2003, at 04:02 AM, Daniel Faivre wrote:

> Hello,
>
> I've encountered serious difficulties with "big drawQueries" after a  
> queryByShape() on PostGIS layers, that do not happen with shapefiles.
>
> Here is a sample php source code for problematics spatial queries with  
> a postgis layer: the
> drawQuery() after a queryByShape() is not efficient enough (see sample  
> execution times commented in the code). Furthermore, tests shows that  
> replacing $sl_thm2 by a postgis layer lead to increased computing  
> delays.
>
> I assume that querying PostGIS layers in mapserver 4 is not efficient  
> enough ?
>
> I've noticed comments in mapserver source code about an improved  
> algorithm, yet not implemented.
>
> My PostGIS layer is properly spatial-indexed, and I've also tried to  
> add USING UNIQUE USING SRID="-1" without any improvement ...
> _______________________________________________________________________ 
> ________________________________
> $Map = ms_newMapObj('my_mapfile.map');
>
> $sl_thm1 = $Map->getlayerbyname('mybigpolygonslayer');      // A big  
> polygons PostGIS layer
> $sl_thm2 = $Map->getlayerbyname(myquerylayer');                 // A  
> shapefile with one multiplygon
>
> $sl_thm2->queryByRect($Map->extent);                                   
> // Necessary for queryByFeatures
>       $res = $sl_thm1->queryByFeatures($sl_thm2->index);
> $num_results = $sl_thm1->getNumResults();
>
> echo "nb find: ".$num_results;               // about 2000 items  
> founds, in my sample
>
> //$map_img = $Map->draw();                // 2  seconds to display the  
> result
> $map_img = $Map->drawQuery();        // 160 to 260 seconds for a  
> postgis layer, 2  seconds for the same layer as shapefile !!!
>
> $imgurl = $map_img->saveWebImage();
>
> echo '<img src='.$imgurl.'>';
> _______________________________________________________________________ 
> ________________________________
>
> What can I do ??? Have you experienced same difficulties with big  
> postgis layers ? Any idea ? Workaround ?
>
> Best regards,
>
> -- 
> Daniel Faivre
> Ohceo - Camptocamp
> +33 4 79 75 28 19
> 180 rue du Genevois
> 73000 Chambéry le Haut - FRANCE
>
>
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632