<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><div>Make sure your user has limited table access. This example would allow for SQL injection.</div><div style="font-family:arial, helvetica, sans-serif;font-size:10pt"><br><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><font size="2" face="Tahoma"><hr size="1"><b><span style="font-weight: bold;">From:</span></b> Gabriel Messner <gabmessner@gmail.com><br><b><span style="font-weight: bold;">To:</span></b> mapserver-users@lists.osgeo.org<br><b><span style="font-weight: bold;">Sent:</span></b> Friday, June 26, 2009 6:51:43 AM<br><b><span style="font-weight: bold;">Subject:</span></b> Re: [mapserver-users] Spatial queries to Mapserver<br></font><br>
Thanks, IŽll digest it :-)<br><br><br><div class="gmail_quote">2009/6/26 Ben Madin <span dir="ltr"><<a rel="nofollow" ymailto="mailto:ben@remoteinformation.com.au" target="_blank" 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 rel="nofollow" target="_blank" href="http://s.id">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 rel="nofollow" target="_blank" href="http://m.id">m.id</a>, c.commodity, sp.shortname, c.amount, <a rel="nofollow" target="_blank" href="http://r.name">r.name</a> AS purpose, </div><div>
<span style="white-space:pre;"> </span>initcap(<a rel="nofollow" target="_blank" href="http://o.name">o.name</a>) as origprov, initcap(<a rel="nofollow" target="_blank" href="http://d.name">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 rel="nofollow" target="_blank" href="http://m.id">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 rel="nofollow" target="_blank" href="http://sp.id">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 rel="nofollow" target="_blank" href="http://r.id">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 rel="nofollow" ymailto="mailto:mapserver-users-request@lists.osgeo.org" target="_blank" href="mailto:mapserver-users-request@lists.osgeo.org">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 rel="nofollow" ymailto="mailto:ben@remoteinformation.com.au" target="_blank" href="mailto:ben@remoteinformation.com.au">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>
</div></div><div style="position:fixed"></div></div><br>
</body></html>