Skip site navigation (1) Skip section navigation (2)

PGSQL Query

From: "Jessica Fendos" <Jessica(dot)Fendos(at)state(dot)mn(dot)us>
To: pgsql-novice(at)postgresql(dot)org
Subject: PGSQL Query
Date: 2007-04-29 06:12:29
Message-ID: s633f112.041@ngwmail.des.state.mn.us (view raw or flat)
Thread:
Lists: pgsql-novice
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 

<!-- 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(dot)fendos(at)state(dot)mn(dot)us
Homepage: jessicafendos.com


Responses

pgsql-novice by date

Next:From: Michael FuhrDate: 2007-04-29 13:43:14
Subject: Re: call stored function from ecpg w/cursor
Previous:From: Frank BaxDate: 2007-04-28 12:27:42
Subject: Re: record id

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group