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

[Stephen Woodbridge]

On 4/28/2011 2:34 PM, Dara Olson wrote:
> 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

Dara,

I don't think it matter if you create views or not from a performance 
point of view. View do allow you to hide much of the internals from the 
mapfile but that might not matter.

The follow are some random examples of postgis DATA statements from some 
of my mapfiles. If you want to do complex queries then you need to wrap 
them into sub-query and that can have joins, where, union whatever you 
need in it.

  DATA "the_geom from (select gid, name, the_geom from lebanon_data.ai 
union all select gid, name, the_geom from data.ai) as foo using unique 
gid using srid=4326"

  DATA "the_geom from (select gid, coalesce(key::text,'') as key, 
feature, coalesce(st_type_abbr,' ') as st_type_abbr, 
coalesce(civic_num,' ') as civic_num, coalesce(street_name,' ') as 
street_name, the_geom from v_polygon) as foo using unique gid using 
srid=2036"

DATA "the_geom from (select id, chk, the_geom from vertices_tmp where 
chk=1 and the_geom && setsrid(!BOX!, 4326) ) as foo using SRID=4326 
using unique id"

The !BOX! feature might be a custom patch I create or a standard 
mapserver feature I can't remember off the top of my head and don't have 
time at the moment to check into that, but what is does is substitute 
the text to define a box representing the image extents so I can place 
the in the subquery rather than the main query for performance reasons 
as the vertices_tmp table is huge.

-Steve W