<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<META content="text/html; charset=utf-8" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18828">
<DIV><FONT size=2 face=Arial>Many thanks Paul!</FONT></DIV>
<BLOCKQUOTE style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV style="FONT: 10pt arial; BACKGROUND: #e4e4e4; font-color: black"><B>From:</B>
<a href="http://n2.nabble.com/user/SendEmail.jtp?type=node&node=3921452&i=0" target="_top" rel="nofollow">[hidden email]</a> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <a href="http://n2.nabble.com/user/SendEmail.jtp?type=node&node=3921452&i=1" target="_top" rel="nofollow">[hidden email]</a> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Friday, October 30, 2009 3:44
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [mapserver-users] PostGIS
syntax error</DIV>
<DIV><BR></DIV>The format of DATA is 'geocolumn from geosource', so your DATA
should be <BR><BR>DATA "wkb_geometry from (SELECT
DISTINCT <BR><BR>not <BR><BR>DATA
"wkb_geometry,county,state from (SELECT DISTINCT <BR><BR>don't worry, the SQL
driver will add calls for those other columns <BR>when they are needed. You
just need to make sure your subselect always <BR>includes all the columns you
want used by other mapserver subsystems <BR>(label columns for labels,
template columns for templates, etc, etc) <BR><BR>P. <BR><BR>On Fri, Oct 30,
2009 at 1:23 PM, Ted Spradley <<A href="http://n2.nabble.com/user/SendEmail.jtp?type=node&node=3921386&i=0" rel="nofollow" target=_top>[hidden email]</A>> wrote:
<DIV class=shrinkable-quote><div class='shrinkable-quote'><BR>> <BR>> Hi All, Happy Halloween <BR>>
<BR>> Scenario: I doing a 'mode=query' of a layer that takes the result of
the <BR>> query <BR>> in
the layer template and sends a subsequent 'mode=nquery' <BR>> request to a
<BR>> different layer in
the mapfile. The 'nqueried' layer template <BR>> then does a <BR>>
'mode=nquerymap' to map the
results. <BR>> <BR>>
################################################################# <BR>> I
am getting the following syntax error from the 'nqueried' layer DATA <BR>>
object: <BR>> <BR>> msPostGISLayerWhichShapes(): Query error. Error
(ERROR: syntax error at or <BR>> near "," <BR>> LINE 1:
....wkb_geometry ) = 0) as myquery where wkb_geometry,county,st... <BR>>
^ <BR>> ) executing
query: select <BR>>
encode(AsBinary(force_collection(force_2d("wkb_geometry,county,state")),'NDR'),'base64')
<BR>> as geom,"ogc_fid" from (SELECT DISTINCT a.ogc_fid, a.wkb_geometry,
<BR>> a.county, a.state FROM countyp020 a, countyp020 b WHERE
b.county='Brown <BR>> County' AND b.state='TX' AND a.wkb_geometry
&& <BR>> b.wkb_geometry AND distance( a.wkb_geometry,
b.wkb_geometry ) = 0) as <BR>> myquery where wkb_geometry,county,state
&& <BR>> GeomFromText('POLYGON((-98.7789255453408 <BR>>
31.1572855453408,-98.7789255453408 31.1572855453408,-98.7789255453408 <BR>>
31.1572855453408,-98.7789255453408 31.1572855453408,-98.7789255453408 <BR>>
31.1572855453408))',-1) <BR>> <BR>>
################################################################# <BR>>
<BR>> #################################################################
<BR>> Template for the request being sent to the layer: <BR>> <BR>>
<input name="img" type="image" <BR>>
src="/cgi-bin/mapserv?map=[map]&mode=nquery&qlayer=usa_counties_adjacent&imgxy=[center_x]
<BR>> [center_y]&county=[county]&state=[state]&imgext=[mapext]"
width="[mapwidth]" <BR>> height="[mapheight]" <BR>> <BR>>
################################################################# <BR>>
<BR>> #################################################################
<BR>> MapServer's rendering of the above template: <BR>> <BR>>
<input name="img" type="image" <BR>>
src="/cgi-bin/mapserv?map=/path/to/mapfiles/mapfile.map&mode=nquery&qlayer=usa_counties_adjacent&imgxy=800.0
<BR>> 400.0&county=Brown County&state=TX&imgext=-103.782053
28.661976 -93.782052 <BR>> 33.658849" width="1600" height="800" >
<BR>> <BR>>
################################################################# <BR>>
<BR>> #################################################################
<BR>> Layer generating the syntax error: <BR>> <BR>>
LAYER <BR>> NAME
usa_counties_adjacent <BR>>
TYPE POLYGON <BR>>
STATUS OFF <BR>>
DEBUG
3 <BR>> TEMPLATE
"/path_to_templates/countyp020_adjacent_bodytemplate.html"
<BR>> <BR>> CONNECTIONTYPE POSTGIS <BR>>
CONNECTION "dbname=project1 user=postgres
password=password host=localhost <BR>> port=5432" <BR>>
DATA "wkb_geometry,county,state from (SELECT
DISTINCT <BR>> a.ogc_fid, a.wkb_geometry, a.county, a.state FROM countyp020
a, countyp020 b <BR>> WHERE b.county='%county%' AND b.state='%state%' AND
a.wkb_geometry && <BR>> b.wkb_geometry AND distance(
a.wkb_geometry, b.wkb_geometry ) = 0) as <BR>> myquery using unique ogc_fid
using srid=-1" <BR>> <BR>> PROJECTION
<BR>>
"init=epsg:4326" <BR>> END <BR>>
END # Layer
usa_counties_adjacent <BR>> <BR>>
################################################################# <BR>>
<BR>> I have tested the syntax from the console in pgsql and get the
expected <BR>> return. <BR>> <BR>>
################################################################# <BR>>
pgsql: <BR>> <BR>> SELECT county, state FROM (SELECT DISTINCT a.ogc_fid,
a.county, a.state FROM <BR>> countyp020 a, countyp020 b WHERE
b.county='Harris County' AND b.state='TX' <BR>> AND a.wkb_geometry
&& b.wkb_geometry AND distance( a.wkb_geometry, <BR>>
b.wkb_geometry ) = 0 ORDER BY a.county) as myfoo; <BR>>
county
| state
<BR>> ----------------------------------------------------+------- <BR>>
Brazoria County
| TX
<BR>> Chambers County
| TX <BR>> Fort Bend County
| TX <BR>> Galveston County
| TX <BR>> Harris County
| TX <BR>> Liberty County
| TX <BR>> Montgomery County
| TX <BR>> Waller County
| TX <BR>> (8 rows)
<BR>> <BR>> <BR>> Any error glaring out at you from DATA statement?
<BR>> <BR>> Thanks, <BR>> Ted S. <BR>> -- <BR>> View this
message in context: <A href="http://n2.nabble.com/PostGIS-syntax-error-tp3921276p3921276.html" rel="nofollow" target=_top>http://n2.nabble.com/PostGIS-syntax-error-tp3921276p3921276.html</A><BR>>
Sent from the Mapserver - User mailing list archive at Nabble.com. <BR>>
_______________________________________________ <BR>> mapserver-users
mailing list <BR>> <A href="http://n2.nabble.com/user/SendEmail.jtp?type=node&node=3921386&i=1" rel="nofollow" target=_top>[hidden email]</A> <BR>> <A href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="nofollow" target=_top>http://lists.osgeo.org/mailman/listinfo/mapserver-users</A><BR>>
</DIV>_______________________________________________ </div>mapserver-users
mailing list <BR><A href="http://n2.nabble.com/user/SendEmail.jtp?type=node&node=3921386&i=2" rel="nofollow" target=_top>[hidden email]</A> <BR><A href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="nofollow" target=_top>http://lists.osgeo.org/mailman/listinfo/mapserver-users</A><BR></BLOCKQUOTE>
<br><hr align="left" width="300">
View this message in context: <a href="http://n2.nabble.com/PostGIS-syntax-error-tp3921276p3921452.html">Re: [mapserver-users] PostGIS syntax error</a><br>
Sent from the <a href="http://n2.nabble.com/Mapserver-User-f1969211.html">Mapserver - User mailing list archive</a> at Nabble.com.<br>