Chameleon SQLquery Widget

[Ei Fujioka]

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
   efujioka at duke.edu
======================