[mapserver-users] PostGIS syntax error

[Ted Spradley]

Hi All, Happy Halloween

Scenario: I doing a 'mode=query' of a layer that takes the result of the
query
              in the layer template and sends a subsequent 'mode=nquery'
request to a 
              different layer in the mapfile.  The 'nqueried' layer template
then does a
              'mode=nquerymap' to map the results.

#################################################################
I am getting the following syntax error from the 'nqueried' layer DATA
object:

msPostGISLayerWhichShapes(): Query error. Error (ERROR:  syntax error at or
near ","
LINE 1: ....wkb_geometry ) = 0) as myquery where wkb_geometry,county,st...
                                                             ^
) executing query: select
encode(AsBinary(force_collection(force_2d("wkb_geometry,county,state")),'NDR'),'base64')
as geom,"ogc_fid" from (SELECT DISTINCT a.ogc_fid, a.wkb_geometry,
a.county, a.state FROM countyp020 a, countyp020 b WHERE b.county='Brown
County' AND b.state='TX' AND a.wkb_geometry &&
b.wkb_geometry AND distance( a.wkb_geometry, b.wkb_geometry ) = 0) as
myquery where wkb_geometry,county,state &&
GeomFromText('POLYGON((-98.7789255453408
31.1572855453408,-98.7789255453408 31.1572855453408,-98.7789255453408
31.1572855453408,-98.7789255453408 31.1572855453408,-98.7789255453408
31.1572855453408))',-1)

#################################################################

#################################################################
Template for the request being sent to the layer:

<input name="img" type="image"
src="/cgi-bin/mapserv?map=[map]&mode=nquery&qlayer=usa_counties_adjacent&imgxy=[center_x]
[center_y]&county=[county]&state=[state]&imgext=[mapext]" width="[mapwidth]"
height="[mapheight]" 

#################################################################

#################################################################
MapServer's rendering of the above template:

<input name="img" type="image"
src="/cgi-bin/mapserv?map=/path/to/mapfiles/mapfile.map&mode=nquery&qlayer=usa_counties_adjacent&imgxy=800.0
400.0&county=Brown County&state=TX&imgext=-103.782053 28.661976 -93.782052
33.658849" width="1600" height="800" >

#################################################################

#################################################################
Layer generating the syntax error:

    LAYER
	NAME            usa_counties_adjacent
        TYPE            POLYGON
        STATUS          OFF
	DEBUG			3
	TEMPLATE        "/path_to_templates/countyp020_adjacent_bodytemplate.html"

        CONNECTIONTYPE POSTGIS
	CONNECTION "dbname=project1 user=postgres password=password host=localhost
port=5432"
        DATA       "wkb_geometry,county,state from (SELECT DISTINCT
a.ogc_fid, a.wkb_geometry, a.county, a.state FROM countyp020 a, countyp020 b
WHERE b.county='%county%' AND b.state='%state%' AND a.wkb_geometry &&
b.wkb_geometry AND distance( a.wkb_geometry, b.wkb_geometry ) = 0) as
myquery using unique ogc_fid using srid=-1"

	PROJECTION
		"init=epsg:4326"
	END
   END		#	Layer usa_counties_adjacent

#################################################################

I have tested the syntax from the console in pgsql and get the expected
return.  

#################################################################
pgsql:

SELECT county, state FROM (SELECT DISTINCT a.ogc_fid, a.county, a.state FROM
countyp020 a, countyp020 b WHERE b.county='Harris County' AND b.state='TX'
AND a.wkb_geometry && b.wkb_geometry AND distance( a.wkb_geometry,
b.wkb_geometry ) = 0 ORDER BY a.county) as myfoo;
                       county                       | state
----------------------------------------------------+-------
 Brazoria County                                    | TX
 Chambers County                                    | TX
 Fort Bend County                                   | TX
 Galveston County                                   | TX
 Harris County                                      | TX
 Liberty County                                     | TX
 Montgomery County                                  | TX
 Waller County                                      | TX
(8 rows)


Any error glaring out at you from DATA statement?

Thanks,
Ted S.
-- 
View this message in context: http://n2.nabble.com/PostGIS-syntax-error-tp3921276p3921276.html
Sent from the Mapserver - User mailing list archive at Nabble.com.