Hi Jessica,
I don't have an exact answer for you but thought this might be a clue.
Chameleon is quite hard to grasp what it really does.
Have you tried to dig into the PHP source codes?
What I found is that the polygon information is stored in
$_SESSION['ROIRENDERER'].
You can extract lat/lon of the vertices with something like this.
if (isset($_SESSION['ROIRENDERER'])) {
$nROI = count($_SESSION['ROIRENDERER']);
// Currently only last polygon is treated.
for($i=0; $i<$nROI; $i++) {
$coords = implode(",", $_SESSION['ROIRENDERER'][$i]['aGeoCoords']);
$roi_mode = $_SESSION['ROIRENDERER'][$i]['type'];
}
}
So if you create PHP codes that is called when you click "Report", you
can issue the SQL, which you entered for SQL Query widget, to extract
data (I mean without using SQL Query).
To see if the ROI information is also stored in shared resources which
SQL Query can access, you will need to look into the source codes.
As I said, it is hard to trace the Chameleon codes so I might have
missed more convenient ways, though.
Good luck.
Ei
fend0009 wrote:
Hi list:
I am using a sqlquery widget to build a report on employment change in a
mapping application. Having little experience in PostgreSQL and Chameleon, I
managed to upload a shapefile (empchgfinal.shp) to PostgreSQL database,
created a spatial index (gist type, name "emp") for the shapefile and wrote
the following SQL query in the html file. However, when I selected the area
of interest (using ROIManager widget), I can see the area is highlighted, but
when I click on generate report, I got an empty result. 1) What is
sharedresourcename in the SQL Query and Table widgets for and how should it
be specified in the table widget? ("EMP" is a report type out of 4 link
options in the report.html and "emp" is the name for the spatial index in the
shapefile). 2) Is there some thing I should do extra in PostgresSQL so that
this will work? Could someone please give me some guidance as to how I can
fix this? Any suggestions will be highly appreciated. Thanks!
Sincerely,
Jessica Fendos
<!-- Query for Employment Report -->
<cwc2 type="SQLQuery" server="XXXXX" database="XXX" username="postgres"
password="mapsXXX" dbtype="PGSQL"
sqlquery="select sum(e.aest_00) as avgest00,sum(e.aest_05) as
avgest05,sum(e.e_est) as estchange,
round(sum(e.e_est)/sum(e.aest_00)*100, 1) as p_estchg,
sum(e.aemp_00) as avgemp00,sum(e.aemp_05) as avgemp05,
sum(e.e_change) as empchange,
round(sum(e.e_change)/sum(e.aemp_00)*100, 1)
as p_empchange,count(*) as n_blockgroups from empchg_final e where
e.the_geom && SetSRID('BOX3D([$_MinX_$] [$_MinY_$] , [$_MaxX_$]
[$_MaxY_$])'::box3d,-1) AND within (e.the_geom, SetSRID
('BOX3D([$_MinX_$] [$_MinY_$],[$_MaxX_$][$_MaxY_$])'::box3d,-1));"
sharedresourcename="EMP">
<ONEVENT Event="ConnectFailed" Text="Connection failed."/>
</cwc2>
<!-- Table for Employment Report -->
<cwc2 type="Table" sharedresourcename="EMP" >
<template name="header"><![CDATA[
<TABLE BORDER=1 CELLSPACING=1 CELLPADDING=1 WIDTH=600>
]]></template>
<template name="body"><![CDATA[
<TR HEIGHT=16 >
<TD WIDTH=52% ALIGN=LEFT > <BR></TD>
<TD WIDTH=12% ALIGN=RIGHT ><FONT style=FONT-SIZE:10pt FACE="Arial"
COLOR=#000000>2000#</P></TD>
<TD WIDTH=12% ALIGN=RIGHT ><FONT style=FONT-SIZE:10pt FACE="Arial"
COLOR=#000000>2005#</P></TD>
<TD WIDTH=12% ALIGN=RIGHT BGCOLOR="#dddddd"><I><FONT style=FONT-SIZE:10pt
FACE="Arial" COLOR=#000000>Change</P></I></TD>
<TD WIDTH=12% ALIGN=RIGHT BGCOLOR="#dddddd"><I><FONT style=FONT-SIZE:10pt
FACE="Arial" COLOR=#000000>Change %</P></I></TD>
</TR>
<TR HEIGHT=18 >
<TD ALIGN=CENTER COLSPAN=5><BR><P CLASS="sub">Employment Statistics</P></TD>
</TR>
<TR HEIGHT=15>
<TD ALIGN=LEFT ><P CLASS="detail">Average Establishment</P></TD>
<TD ALIGN=RIGHT ><P CLASS="detail">%avgest00%</P></TD>
<TD ALIGN=RIGHT ><P CLASS="detail">%avgest05%</P></TD>
<TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
CLASS="detail"><I>%estchange%</I></P></TD>
<TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
CLASS="detail"><I>%p_estchg%</I></P></TD>
</TR>
<TR HEIGHT=15 >
<TD ALIGN=LEFT ><P CLASS="detail">Average Employment</P></TD>
<TD ALIGN=RIGHT ><P CLASS="detail">%avgemp00%</P></TD>
<TD ALIGN=RIGHT ><P CLASS="detail">%avgemp05%</P></TD>
<TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
CLASS="detail"><I>%empchange%</I></P></TD>
<TD ALIGN=RIGHT BGCOLOR="#dddddd"><P
CLASS="detail"><I>%p_empchange%</I></P></TD>
</TR>
....
--
======================
Ei Fujioka
Research Associate
Duke University
(919) 613-8021
[EMAIL PROTECTED]
======================
