second times sql and SUM function in Map script

[Milo van der Linden]

Hello Murat!

What I would do (as I always do when queries get complex) is create a
view in the mysql database and call that view. That will speed up
performance and give you less chance of error in your mapfile.

So in MySQL:

CREATE OR REPLACE VIEW MyVIEW as
SELECT
d_damage as damage,
m_village1.name as villagename,
m_village1.latitude as latitude,
m_village1.longitude as longitude,
sum(d_damage.deadp_rc + d_damage.deadp_wood + d_damage.deadp_briq +
d_damage.deadp_brick + d_damage.deadp_stone + d_damage.deadp_adobe) as
totaldamage
from
    d_damage,
    m_village1
where
d_damage.region_id=m_village1.dist_id

And in the MapFile;
select latitude, longitude, totaldamage from myVIEW where d_damage.eq_id
like "%eq_id%" and m_village1.name not like "%(%"


This will make life less complicated, plus you can test the query better
in mySQL!


Murat Beyhan schreef:
> Dear Friends,
>
> I would like to draw a casualty distribution map for  various building
> types.
>
> On the srcSQL as you see  I need summation of the casualties of each
> type of buildings(rc,wood,briq,brick,stone,adobe)
> So I have used sum function to obtain total number of casualties. But
> when I tried to make legend I have to select classitem so I have used
> AS totald for that reason
> then I have used totald as a classitem.
> But mapserver returns an error message.
>
> When I select only one item for select query for example select
> d_damage.deadp_wood from d_damage
> then If I use deadp_wood as classitem the query works and the map drawn.
>
> For my intend what I have to do?
> Regards...
> MURAT
>
> script: as follows
>
>
> LAYER
> NAME "Dhuman"
>   CONNECTIONTYPE OGR    CONNECTION '<OGRVRTDataSource>
>   <OGRVRTLayer name="Dhuman">
> <SrcDataSource>MYSQL:turkey,user=root,password=mysql_123,host=localhost,port=3306,tables=d_damage
> m_village1</SrcDataSource>
>       <SrcSQL>SELECT m_village1.latitude,m_village1.longitude,
> *sum(d_damage.deadp_rc + d_damage.deadp_wood + d_damage.deadp_briq +
> d_damage.deadp_brick + d_damage.deadp_stone + d_damage.deadp_adobe) as
> totald *from d_damage, m_village1 where d_damage.eq_id like "%eq_id%"
> and d_damage.region_id=m_village1.dist_id and m_village1.name not like
> "%(%"</SrcSQL>
>       <GeometryType>wkbPoint</GeometryType>
>       <GeometryField encoding="PointFromColumns" x="longitude"
> y="latitude"/>
>   </OGRVRTLayer>
> </OGRVRTDataSource>'
>  TYPE POINT
>   DATA Dhuman
>   STATUS default
>           CLASSITEM "totald"
>   CLASS
>   NAME "HUMAN DAMAGE"
>   EXPRESSION ([totald] < 0)
> #    COLOR 255 0 255
> #    SYMBOL "cpoint"
> #    SIZE 12
> END
>
>
>
>   CLASS
>   NAME "1-10"
>   EXPRESSION ([totald] GE 1.0 AND [totald] LT 10.0)
>  SYMBOL "cpoint"
>  SIZE 5
>   COLOR 255 250 250
> END
>
>   CLASS
>   NAME "11-50"
>   EXPRESSION ([totald] GE 11.0 AND [totald] LT 50.0)
>  SYMBOL "cpoint"
>  SIZE 5
>   COLOR 248 248 255
> END
>
> CLASS
>   NAME "51-100"
>   EXPRESSION ([totald] GE 51.0 AND [totald] LT 100.0)
>  SYMBOL "cpoint"
>  SIZE 5
>   COLOR 255 250 240
> END
> CLASS
>   NAME "101-200"
>   EXPRESSION ([totald] GE 101.0 AND [totald] LT 200.0)
>  SYMBOL "cpoint"
>  SIZE 5
>   COLOR 102 255 204
> END
> CLASS
>   NAME "201-400"
> EXPRESSION ([totald] GE 201.0 AND [totald] LT 400.0)
>   SYMBOL "cpoint"
>   SIZE 5
>   COLOR 102 53 0
> END
> CLASS
>   NAME "400<"
>    EXPRESSION ([totald] GT 400.0)
>   SYMBOL "cpoint"
>   SIZE 5
>   COLOR 102 153 255
> END
> END
>
>
>
> the error message as follows:
>
> msDrawMap(): Image handling error. Failed to draw layer named 'Dhuman'.
> msOGRFileOpen(): OGR error. Open failed for OGR connection `
> MYSQL:turkey,user=root,password=mysql_123,host=localhost,port=3306,tables=d_damage
> m_village1 SELECT m_village1.latitude,m_village1.longitude,
> sum(d_damage.deadp_rc+ d_damage.deadp_wood +d_damage.deadp_briq +
> d_damage.deadp_brick + d_damage.deadp_stone + d_damage.deadp_adobe) as
> totald from d_damage, m_village1 where d_damage.eq_id like
> "200006060242-auto-000" and d_damage.region_id=m_village1.dist_id and
> m_village1.name not like "%(%" wkbPoint '. SQL statement failed, or
> returned no layer result: SELECT
> m_village1.latitude,m_village1.longitude, sum(d_damage.deadp_rc+
> d_damage.deadp_wood +d_damage.deadp_briq + d_damage.deadp_brick +
> d_damage.deadp_stone + d_damage.deadp_adobe) as totald from d_damage,
> m_village1 where d_damage.eq_id like "200006060242-auto-000" and
> d_damage.region_id=m_village1.dist_id and m_village1.name not like "%(%"
>
>


-- 


	

Milo van der Linden
skype: milovanderlinden <skype:milovanderlinden?add>
mlinden at zeelandnet.nl <mailto:mlinden at zeelandnet.nl>
milovanderlinden at gmail.com <mailto:milovanderlinden at gmail.com>
milo at 3dsite.nl <mailto:milo at 3dsite.nl>
http://www.3dsite.nl

	  	

De informatie in dit bericht reflecteert mijn persoonlijke mening en
niet die van een bedrijf of instantie. Aan de informatie kunnen geen
rechten worden ontleend. Indien dit bericht onderdeel is van een forum,
mailing-list of community dan gelden automatisch de bij het betreffende
medium behorende voorwaarden. The information in this message reflects
my personal opinion and not that of a company or public body. All rights
reserved.If this message is contained in a mailing-list or community,
the rights on the medium are automatically adapted.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20070815/45812d3c/attachment.html