Thanks, I´ll digest it :-)<br><br><br><div class="gmail_quote">2009/6/26 Ben Madin <span dir="ltr"><<a href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</a>></span><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div style="word-wrap: break-word;">Gabriel,<div><br></div><div>You need to add a layer with your query in it - you can use variable substitution etc.</div><div><br></div><div>an example (may or may not be a good one!) :</div>
<div><br></div><div><div> LAYER</div><div> DEBUG 3</div><div> NAME "buffalo"</div><div> GROUP "Movement"</div><div> METADATA</div><div> layer_title "Buffalo"</div>
<div> layer_order "2"</div><div> END</div><div> CONNECTIONTYPE postgis</div><div> CONNECTION "host=localhost port=5432 dbname=prices"</div><div><span style="white-space: pre;"> </span>PROCESSING "CLOSE_CONNECTION=DEFER"</div>
<div> DATA "route FROM (</div><div> SELECT <a href="http://s.id" target="_blank">s.id</a>, s.commodity, s.shortname, s.amount, s.purpose, s.origprov, s.destprov, ST_MakeLine(s.startpoint, s.endpoint) as route, to_char(s.origindate,'DD/MM/YYYY') as date</div>
<div> FROM (</div><div> SELECT <a href="http://m.id" target="_blank">m.id</a>, c.commodity, sp.shortname, c.amount, <a href="http://r.name" target="_blank">r.name</a> AS purpose, </div><div>
<span style="white-space: pre;"> </span>initcap(<a href="http://o.name" target="_blank">o.name</a>) as origprov, initcap(<a href="http://d.name" target="_blank">d.name</a>) as destprov,</div><div> <span style="white-space: pre;"> </span>ST_Centroid(o.geom) as startpoint, ST_Centroid(d.geom) as endpoint, origindate</div>
<div> FROM movements m</div><div> LEFT OUTER JOIN locations o</div><div> ON m.originprovince = o.hiercode</div><div> <span style="white-space: pre;"> </span> AND o.del = 0</div>
<div><span style="white-space: pre;"> </span> AND o.level = 2</div><div> LEFT OUTER JOIN locations d</div><div> ON m.destinationprovince = d.hiercode</div><div> <span style="white-space: pre;"> </span>AND d.del = 0</div>
<div> <span style="white-space: pre;"> </span>AND d.level = 2</div><div> JOIN commodities c</div><div> ON <a href="http://m.id" target="_blank">m.id</a> = c.movementid</div>
<div> <span style="white-space: pre;"> </span>AND c.del = 0</div><div> <span style="white-space: pre;"> </span> AND c.commodity = 2</div><div> <span style="white-space: pre;"> </span>LEFT OUTER JOIN species sp</div>
<div> <span style="white-space: pre;"> </span>ON c.commodity = <a href="http://sp.id" target="_blank">sp.id</a></div><div> <span style="white-space: pre;"> </span>AND sp.del = 0</div><div> <span style="white-space: pre;"> </span>LEFT OUTER JOIN reasons r</div>
<div> <span style="white-space: pre;"> </span>ON c.purpose::int = <a href="http://r.id" target="_blank">r.id</a></div><div> <span style="white-space: pre;"> </span>AND r.del = 0</div><div> WHERE m.del = 0</div>
<div><span style="white-space: pre;"> </span>AND originprovince is not null</div><div><span style="white-space: pre;"> </span>AND originprovince not like '0'</div><div><span style="white-space: pre;"> </span>AND destinationprovince is not null</div>
<div><span style="white-space: pre;"> </span>AND destinationprovince not like '0'</div><div> ) AS s</div><div> WHERE s.origindate > '%start%'</div><div> <span style="white-space: pre;"> </span> AND s.origindate < '%end%'</div>
<div> ORDER BY s.origindate</div><div> ) AS foo USING UNIQUE id USING srid=4326"</div><div> TYPE LINE</div><div> STATUS ON</div><div> HEADER 'templates/trade_header.html'</div>
<div> FOOTER 'templates/trade_footer.html'</div><div> CLASSITEM commodity</div><div> CLASS</div><div> NAME "Buffalo"</div><div> EXPRESSION "2"</div><div>
STYLE</div><div><span style="white-space: pre;"> </span> SYMBOL "arrow1"</div><div><span style="white-space: pre;"> </span> OFFSET 0 0 </div><div> <span style="white-space: pre;"> </span> SIZE 12</div>
<div> <span style="white-space: pre;"> </span> COLOR 0 0 255</div><div> <span style="white-space: pre;"> </span>END</div><div> TEMPLATE 'templates/trade_query.html'</div><div> END</div>
<div> END</div><div><br></div><div><br></div><div>Actually, I don't think it's a great example, but it shows you how to create a line on the fly using the centroid (probably should have been point on surface) showing the direction the animals moved, within a user specified time frame.</div>
<div><br></div><div>I'm open to any feedback on this - it's a modification of one I modified from one Regina Obe posted somewhere.</div><div><br></div><div>I should say, note the use of debug. the error messages are invaluable when it doesn't work.</div>
<div><br></div><div>good luck</div><div><br></div><div>cheers</div><div><br></div><div>Ben</div><div><br></div><div><br></div><div><br></div><div><br></div><div><br></div><div><div>On 26/06/2009, at 2:47 PM, <a href="mailto:mapserver-users-request@lists.osgeo.org" target="_blank">mapserver-users-request@lists.osgeo.org</a> wrote:</div>
<br><blockquote type="cite"><div style="margin: 0px;"><span style="font-family: Helvetica; font-size: medium;"><b>Subject:<span> </span></b></span><span style="font-family: Helvetica; font-size: medium;"><b>[mapserver-users] Spatial queries to Mapserver</b><br>
</span></div><div class="im"><br><br>Hi all,<br><br>Any one know of examples about how to call spatial queries using Postis functions from Mapserver?<br></div></blockquote></div><br><div> <span style="border-collapse: separate; border-spacing: 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;"><div style="word-wrap: break-word;">
<div style="margin: 0px;">-- </div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;">Ben Madin</div><div style="margin: 0px;">REMOTE INFORMATION</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px;">t : +61 8 9192 5455</div><div style="margin: 0px;">f : +61 8 9192 5535</div><div style="margin: 0px;">m : 0448 887 220</div><div style="margin: 0px;">Broome WA 6725</div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px; min-height: 14px;"><a href="mailto:ben@remoteinformation.com.au" target="_blank">ben@remoteinformation.com.au</a></div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px; min-height: 14px;">
<br></div><div style="margin: 0px; min-height: 14px;"><br></div><div style="margin: 0px;"><span style="white-space: pre;"><span style="white-space: pre;"> </span></span>Out here, it pays to know...</div></div><br></span> </div>
<br></div></div></blockquote></div><br>