[mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join

[pcreso at pcreso.com]

Hi Dana, 

Usually it does not matter much, as mapserver passes the query to Postgis anyway, as you noted. However, mapserever does do some parsing & rewriting of the query before issuing it, and this can cause issues.

Also note that when working in the db, it is easier to use explain, check indexes, etc to optimise your query, so if it is anything but a simple join, I'd suggest creating it in the database is the tidier way to do this.

I guess I approach it with the premise that mapserver is the rendering engine, Postgis is the data management/query engine. If your dba is going to be able to see the views/queries/joins which the db implements, & manage/optimise the db for these, it is much easier if they are inplemented in the db than scattered around in mapfiles.

This said, I use where clauses all the time in mapfiles, for layers based on data driven categories within a single table, for example, map LAYER DTIS = 
"select ... where category='DTIS';", etc. I'd suggest views in this case are just clutter :-)

It is a case-by-case basis... for informed advice, seeing the actual query would be useful
.
Cheers,

 Brent Wood

--- On Fri, 4/29/11, Dara Olson <dolson at glifwc.org> wrote:

From: Dara Olson <dolson at glifwc.org>
Subject: [mapserver-users] Efficiency - Postgres/gis view or Mapserver data definition join
To: mapserver-users at lists.osgeo.org
Date: Friday, April 29, 2011, 6:34 AM



 
 


Greetings.  I am hoping that I have posted 
this on the most appropriate list, please let me know if I should be 
posting to a different list. 
 
In our Mapserver application, we join a lot of tables 
together - generally one table with geometry to a "flat" table with 
tabular data.  My question is - is it more efficient/faster to create 
a view in PostgreSQL doing the join and link to the view table from 
Mapserver or to define the join (SQL statement) with Mapserver in 
the data definition within the layer definition? Does it even matter 
because Mapserver sends the request to PostgreSQL/GIS? 
 
Also, we have joins that take a field within the flat 
table to determine which table to join to get geometry.  For example, 
if the flat table column "join" value is equal to 1 then it joins to 
"table1" to get geometry or if column "join" value is equal to 2 then it joins 
to "table2" to get geometry. I read something that you were not supposed to use 
WHERE clauses in the Mapserver data definition.  Would this be better to do 
as a view and link to the view table from the data definition?
 
Any advice would be greatly appreciated.  Thanks 
in advance!
Dara
-----Inline Attachment Follows-----

_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20110429/f4e1052c/attachment.htm>