[Mapserver-users] Problem with subquery
Randy Page
randy.page at terraverge.com
Fri Jul 30 15:00:33 EDT 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 (<sub query>) 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>
<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>msDrawMap(): Image handling error. Failed to draw layer named
'thematic'. <br>
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 )' <br>
<br>
Postgresql reports the error as 'ERROR: syntax error at or near =
"=08"
at character 78 '<br>
<br>
Mappostgis.c - version of Jan 23/2004. <br>
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: <br>
<br>
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'.<br>
Standard ways of specifiying are : <br>
(1) 'geometry_column from geometry_table' <br>
(2) 'geometry_column from (<sub query>) as foo using unique =
<column
name> using SRID=3D<srid#>' <br>
<br>
Make sure you put in the 'using unique <column name>' and 'using =
SRID=3D#'
clauses in.</span></font><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>
</div>
</body>
</html>
------=_NextPart_000_00B6_01C47645.F7022370--
More information about the mapserver-users
mailing list