Dear Milo,
Thank you for your interest.
Mysql runs under my localhost that version is 4.1.18.
So this version of MySql does not support view function..
so far I have try to create a view at same database which the version is
5.0 and runs at another computer.
When I try to create the view I have been warned by the computer to add
group clause to the sql statement.
when I add group statement to the script It could created view. Then
So I have tried to add group clause to the sql statement which I forgot
it before in my localhost.
Then My map file draw map without any problem. I hope I explained what
I have done.
<SrcSQL>SELECT m_village1.latitude,m_village1.longitude,
sum(d_damage.cmpdb_rc+d_damage.prtdb_rc) as total_rc from d_damage,
m_village1 where d_damage.eq_id like "%eq_id%" and
d_damage.region_id=m_village1.dist_id group by d_damage.region_id</SrcSQL>
Regards...
Murat
Milo van der Linden wrote:
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>
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
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.
--
This message has been scanned for viruses and
dangerous content by *MailScanner* <http://www.mailscanner.info/>, and is
believed to be clean.
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
