[mapserver-users] Spatial queries to Mapserver

[Gabriel Messner]

Thanks, I´ll digest it :-)


2009/6/26 Ben Madin <ben at remoteinformation.com.au>

> 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/ea41a58e/attachment.htm>