[mapserver-users] Spatial queries to Mapserver

[Ben Madin]

Gabriel,

You need to add a layer with your query in it - you can use variable  
substitution etc.

an example (may or may not be a good one!) :

     LAYER
         DEBUG 3
         NAME "buffalo"
         GROUP "Movement"
         METADATA
             layer_title "Buffalo"
             layer_order "2"
         END
         CONNECTIONTYPE postgis
         CONNECTION "host=localhost port=5432 dbname=prices"
		PROCESSING "CLOSE_CONNECTION=DEFER"
         DATA "route FROM (
                 SELECT s.id, 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
                 FROM (
                     SELECT m.id, c.commodity, sp.shortname, c.amount,  
r.name AS purpose,
                     		initcap(o.name) as origprov, initcap(d.name) as  
destprov,
                     		ST_Centroid(o.geom) as startpoint,  
ST_Centroid(d.geom) as endpoint, origindate
                     FROM movements m
                     LEFT OUTER JOIN locations o
                     ON m.originprovince = o.hiercode
     	                AND o.del = 0
	                    AND o.level = 2
                     LEFT OUTER JOIN locations d
                     ON m.destinationprovince = d.hiercode
                     	AND d.del = 0
                     	AND d.level = 2
                     JOIN commodities c
                     ON m.id = c.movementid
                     	AND c.del = 0
       	              AND c.commodity = 2
         			LEFT OUTER JOIN species sp
         			ON c.commodity = sp.id
         				AND sp.del = 0
         			LEFT OUTER JOIN reasons r
         			ON c.purpose::int = r.id
         				AND r.del = 0
                     WHERE m.del = 0
						AND originprovince is not null
						AND originprovince not like '0'
						AND destinationprovince is not null
						AND destinationprovince not like '0'
                 ) AS s
                 WHERE s.origindate > '%start%'
         	        AND s.origindate < '%end%'
                 ORDER BY s.origindate
         ) AS foo USING UNIQUE id USING srid=4326"
         TYPE LINE
         STATUS ON
         HEADER 'templates/trade_header.html'
         FOOTER 'templates/trade_footer.html'
         CLASSITEM commodity
         CLASS
             NAME "Buffalo"
             EXPRESSION "2"
             STYLE
	            SYMBOL "arrow1"
	            OFFSET 0 0
     	        SIZE 12
         	    COLOR 0 0 255
         	END
             TEMPLATE 'templates/trade_query.html'
         END
     END


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.

I'm open to any feedback on this - it's a modification of one I  
modified from one Regina Obe posted somewhere.

I should say, note the use of debug. the error messages are invaluable  
when it doesn't work.

good luck

cheers

Ben





On 26/06/2009, at 2:47 PM, mapserver-users-request at lists.osgeo.org  
wrote:

> Subject: [mapserver-users] Spatial queries to Mapserver
>
>
> Hi all,
>
> Any one know of examples about how to call spatial queries using  
> Postis functions from Mapserver?

-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20090626/98fbc357/attachment.htm>