Re: PGSQL Query

On 4/29/07, Jessica Fendos <Jessica.Fendos@state.mn.us> wrote:
> Hi list:
>
> I am using sqlquery to build a report in a chameleon
> (php/mapscript)-enabled online mapping application. Having little
> experience in PostgreSQL, I uploaded 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 ROI widget) and click generate report, I got an empty result.
> Could someone please give me some guidance as to how to fix it?  Any
> suggestions will be highly appreciated.
>
> Sincerely,
> Jessica Fendos
>

Forgive me for asking the obvious general questions, but can you get
the page to display with a much simpler query, like 'SELECT * FROM
empchg_final LIMIT 1', just to troubleshoot if the connection works at
all?   If that works, can you copy the text of the actual query and
run it against the database directly (through the command line psql or
through pgAdmin 3) to make sure it does not generate an error.

I can't help more than that, as I am not familiar with php.

Good luck.
-Mike

> <!-- 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@state.mn.us
> Homepage: jessicafendos.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>