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, [email protected]
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
[email protected]
Out here, it pays to
know...
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
