[Mapserver-users] Problem with subquery

Fri Jul 30 12:00:33 PDT 2004

This is a multi-part message in MIME format.

------=_NextPart_000_00B6_01C47645.F7022370
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi All,

=20

I am attempting to join an attribute table to a spatial table and am =
running
into allot of errors.   Below is my basic setup:

=20

-Both tables are stored in the same PostgreSQL database.   The spatial =
table
is tracts_c, and the attribute table is c2000_01_tr.

-I am attempting to add a subquery to the data statement in the mapfile. =
  I
can run the sql successfully in psql if I omit the "using unique oid =
using
SRID=3D-1" portion of the statement.  If I include it, PSQL says there =
is a
syntax error near "using".

=20

Here is my data string:

=20

DATA 'select the_geom FROM (SELECT tracts_c.the_geom AS the_geom,
tracts_c.oid AS oid,"C2000_01_TR"."TOTPOP00" FROM tracts_c LEFT JOIN
"C2000_01_TR" ON tracts_c.id =3D "C2000_01_TR"."NAME") AS foo using =
unique oid
using SRID=3D-1'

=20

I have tried ALOT of variations on this, but this is the only format =
that
will run in PSQL.   When I attempt to generate a map with this included, =
I
get the error pasted below.

=20

Any ideas?

=20

Thanks,

=20

Randy

=20

=20

msDrawMap(): Image handling error. Failed to draw layer named =
'thematic'.=20
prep_DB(): Query error. Error executing POSTGIS DECLARE (the actual =
query)
statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(=08PSx)),'NDR'),oid::text from =
=08PSx_poly
WHERE =08PSx && setSRID('BOX3D(-79.1047 35.6605387954545,-78.90507
35.8056212045455)'::BOX3D, -1 )'=20

Postgresql reports the error as 'ERROR: syntax error at or near "=08" at
character 78 '

Mappostgis.c - version of Jan 23/2004.=20
msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data =
variable.
Must contain 'geometry_column from table_name' or 'geom from (subselect) =
as
foo' (couldnt find ' from '). More help:=20

Error with POSTGIS data variable. You specified 'select the_geom FROM
(SELECT tracts_c.the_geom AS the_geom, tracts_c.oid AS
oid,"C2000_01_TR"."TOTPOP00" FROM tracts_c LEFT JOIN "C2000_01_TR" ON
tracts_c.id =3D "C2000_01_TR"."NAME") AS foo using unique oid using =
SRID=3D-1'.
Standard ways of specifiying are :=20
(1) 'geometry_column from geometry_table'=20
(2) 'geometry_column from () as foo using unique <column =
name>
using SRID=3D<srid#>'=20

Make sure you put in the 'using unique <column name>' and 'using =
SRID=3D#'
clauses in.

=20

------=_NextPart_000_00B6_01C47645.F7022370
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Hi All,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I am attempting to join an attribute table to a =
spatial
table and am running into allot of errors.   Below is my basic =
setup:<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>-Both tables are stored in the same PostgreSQL
database.   The spatial table is tracts_c, and the attribute =
table is
c2000_01_tr.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>-I am attempting to add a subquery to the data =
statement in
the mapfile.   I can run the sql successfully in psql if I =
omit the
“using unique oid using SRID=3D-1” portion of the =
statement.  If
I include it, PSQL says there is a syntax error near =
“using”.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Here is my data string:<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>DATA 'select the_geom FROM (SELECT tracts_c.the_geom =
AS
the_geom, tracts_c.oid AS =
oid,"C2000_01_TR"."TOTPOP00" FROM
tracts_c LEFT JOIN "C2000_01_TR" ON tracts_c.id =3D
"C2000_01_TR"."NAME") AS foo using unique oid using
SRID=3D-1'<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I have tried ALOT of variations on this, but this is =
the
only format that will run in PSQL.   When I attempt to =
generate a map
with this included, I get the error pasted =
below.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Any ideas?<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Thanks,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Randy<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

msDrawMap(): Image handling error. Failed to draw layer named
'thematic'. 
prep_DB(): Query error. Error executing POSTGIS DECLARE (the actual =
query)
statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(=08PSx)),'NDR'),oid::text from =
=08PSx_poly
WHERE =08PSx && setSRID('BOX3D(-79.1047 =
35.6605387954545,-78.90507
35.8056212045455)'::BOX3D, -1 )' 
 
Postgresql reports the error as 'ERROR: syntax error at or near =
"=08"
at character 78 ' 
 
Mappostgis.c - version of Jan 23/2004. 
msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data =
variable.
Must contain 'geometry_column from table_name' or 'geom from (subselect) =
as
foo' (couldnt find ' from '). More help: 
 
Error with POSTGIS data variable. You specified 'select the_geom FROM =
(SELECT
tracts_c.the_geom AS the_geom, tracts_c.oid AS
oid,"C2000_01_TR"."TOTPOP00" FROM tracts_c LEFT JOIN
"C2000_01_TR" ON tracts_c.id =3D =
"C2000_01_TR"."NAME")
AS foo using unique oid using SRID=3D-1'. 
Standard ways of specifiying are : 
(1) 'geometry_column from geometry_table' 
(2) 'geometry_column from () as foo using unique =
<column
name> using SRID=3D<srid#>' 
 
Make sure you put in the 'using unique <column name>' and 'using =
SRID=3D#'
clauses in.<o:p></o:p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>

</div>

</body>

</html>

------=_NextPart_000_00B6_01C47645.F7022370--