[mapserver-users] Spatial queries to Mapserver

[Dan Little]

Make sure your user has limited table access.  This example would allow for SQL injection.



________________________________
From: Gabriel Messner <gabmessner at gmail.com>
To: mapserver-users at lists.osgeo.org
Sent: Friday, June 26, 2009 6:51:43 AM
Subject: Re: [mapserver-users] Spatial queries to Mapserver

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