<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.19046">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Garamond>Thanks so much!</FONT></DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="FONT: 10pt arial; BACKGROUND: #e4e4e4; font-color: black"><B>From:</B>
<A title=andy@squeakycode.net href="mailto:andy@squeakycode.net">Andy
Colson</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=dolson@glifwc.org
href="mailto:dolson@glifwc.org">Dara Olson</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Cc:</B> <A
title=mapserver-users@lists.osgeo.org
href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, April 28, 2011 1:56
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [mapserver-users] Efficiency
- Postgres/gis view or Mapserver data definition join</DIV>
<DIV><BR></DIV>On 4/28/2011 1:34 PM, Dara Olson wrote:<BR>> Greetings. I am
hoping that I have posted this on the most appropriate<BR>> list, please
let me know if I should be posting to a different list.<BR>> In our
Mapserver application, we join a lot of tables together -<BR>> generally
one table with geometry to a "flat" table with tabular data.<BR>> My
question is - is it more efficient/faster to create a view in<BR>>
PostgreSQL doing the join and link to the view table from Mapserver or<BR>>
to define the join (SQL statement) with Mapserver in the data
definition<BR>> within the layer definition? Does it even matter because
Mapserver sends<BR>> the request to PostgreSQL/GIS?<BR>> Also, we have
joins that take a field within the flat table to determine<BR>> which table
to join to get geometry. For example, if the flat table<BR>> column "join"
value is equal to 1 then it joins to "table1" to get<BR>> geometry or if
column "join" value is equal to 2 then it joins to<BR>> "table2" to get
geometry. I read something that you were not supposed to<BR>> use WHERE
clauses in the Mapserver data definition. Would this be better<BR>> to do
as a view and link to the view table from the data definition?<BR>> Any
advice would be greatly appreciated. Thanks in advance!<BR>>
Dara<BR>><BR>><BR>><BR>>
_______________________________________________<BR>> mapserver-users
mailing list<BR>> <A
href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</A><BR>>
<A
href="http://lists.osgeo.org/mailman/listinfo/mapserver-users">http://lists.osgeo.org/mailman/listinfo/mapserver-users</A><BR><BR> >
is it more efficient/faster to create a view in<BR> > PostgreSQL doing
the join and link to the view table from Mapserver or<BR> > to define
the join (SQL statement) with Mapserver in the data definition<BR> >
within the layer definition?<BR><BR>I doubt a view would be faster or
slower. I'd bet it would be the same. <BR> (Engine wise its going
to process the same sql statement, collect/join <BR>the data the same way,
etc).<BR><BR> > I read something that you were not supposed
to<BR> > use WHERE clauses in the Mapserver data
definition.<BR><BR>Where? I disagree with that
totally.<BR><BR><BR> > Would this be better<BR> > to do as a
view and link to the view table from the data definition?<BR><BR>1)
performance wise, no, I doubt it because:<BR><BR>create view test1 as select *
from fname where id < 5;<BR><BR>Then:<BR><BR>"select * from test1" and
"select * from fname where id < 5"<BR><BR>are processed exactly the same
way... and it would even be the same as:<BR><BR>create view test2 as select *
from fname;<BR><BR>select * from test2 where id < 5;<BR><BR>performance
wise, the backend processes them all the same way.<BR><BR>2) usage wise, no
because the view limit's your usage of the where. You <BR>cannot pass or
change arguments:<BR><BR>create view test1 as select * from fname where id
< 5;<BR><BR>There is no way to change the "id < 5" condition. You
can't pass it as <BR>an argument to the view, and you can't modify it without
recreating the <BR>view.<BR><BR><BR>I'd say its okay to stay with sql in your
mapfile. The only time I have <BR>used views is when I need to query
different tables with different <BR>names/columns in a clean consistent
way. I sometimes get data that has <BR>a column parcel_number, or PID,
or parcelNum or ... I create a view for <BR>each database, then my view
can use the actual name, but my mapfile <BR>always looks the
same:<BR><BR>select pin, the_geom from parcels;<BR><BR>Another good use for
view's is if you want to hide some of the <BR>complexity of all the
joins. It might make your mapfile look a little <BR>nicer and easier to
edit. But dont put the where's inside the view... <BR>because then
everyone is stuck with it. You mapfile should look like:<BR><BR>select
...,the_geom from view where something =
x;<BR><BR>-Andy</BLOCKQUOTE></BODY></HTML>