[Chameleon] SQL Query and Table Widgets - Please Advise

[Jessica Fendos]

Hi list:

I am using sqlquery to build a report on employment change in my
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 draw the select 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 it is also 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. 

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>
....


Jessica M. L. Fendos
Research Analysis Specialist Sr./GIS Application Developer
Labor Market Information (LMI) Office
MN Department of Employment and Economic Development
Tel: 651-296-3739
jessica.fendos at state.mn.us
Homepage: jessicafendos.com