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

[Andy Colson]

On 4/28/2011 1: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
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

 > 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?

I doubt a view would be faster or slower.  I'd bet it would be the same. 
  (Engine wise its going to process the same sql statement, collect/join 
the data the same way, etc).

 > I read something that you were not supposed to
 > use WHERE clauses in the Mapserver data definition.

Where?  I disagree with that totally.


 > Would this be better
 > to do as a view and link to the view table from the data definition?

1) performance wise, no, I doubt it because:

create view test1 as select * from fname where id < 5;

Then:

"select * from test1" and "select * from fname where id < 5"

are processed exactly the same way... and it would even be the same as:

create view test2 as select * from fname;

select * from test2 where id < 5;

performance wise, the backend processes them all the same way.

2) usage wise, no because the view limit's your usage of the where.  You 
cannot pass or change arguments:

create view test1 as select * from fname where id < 5;

There is no way to change the "id < 5" condition.  You can't pass it as 
an argument to the view, and you can't modify it without recreating the 
view.


I'd say its okay to stay with sql in your mapfile.  The only time I have 
used views is when I need to query different tables with different 
names/columns in a clean consistent way.  I sometimes get data that has 
a column parcel_number, or PID, or parcelNum or ...  I create a view for 
each database, then my view can use the actual name, but my mapfile 
always looks the same:

select pin, the_geom from parcels;

Another good use for view's is if you want to hide some of the 
complexity of all the joins.  It might make your mapfile look a little 
nicer and easier to edit.  But dont put the where's inside the view... 
because then everyone is stuck with it. You mapfile should look like:

select ...,the_geom from view where something = x;

-Andy