[mapserver-users] PostGIS syntax error

[Ted Spradley]

Many thanks Paul!
  ----- Original Message ----- 
  From: Paul Ramsey [via OSGeo.org] 
  To: Ted Spradley 
  Sent: Friday, October 30, 2009 3:44 PM
  Subject: Re: [mapserver-users] PostGIS syntax error


  The format of DATA is 'geocolumn from geosource', so your DATA should be 

  DATA       "wkb_geometry from (SELECT DISTINCT 

  not 

  DATA       "wkb_geometry,county,state from (SELECT DISTINCT 

  don't worry, the SQL driver will add calls for those other columns 
  when they are needed. You just need to make sure your subselect always 
  includes all the columns you want used by other mapserver subsystems 
  (label columns for labels, template columns for templates, etc, etc) 

  P. 

  On Fri, Oct 30, 2009 at 1:23 PM, Ted Spradley <[hidden email]> wrote: 

  > 
  > 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. 
  > _______________________________________________ 
  > mapserver-users mailing list 
  > [hidden email] 
  > http://lists.osgeo.org/mailman/listinfo/mapserver-users
  > 
  _______________________________________________ 
  mapserver-users mailing list 
  [hidden email] 
  http://lists.osgeo.org/mailman/listinfo/mapserver-users



------------------------------------------------------------------------------

  View message @ http://n2.nabble.com/PostGIS-syntax-error-tp3921276p3921386.html 
  To unsubscribe from PostGIS syntax error, click here. 


-- 
View this message in context: http://n2.nabble.com/PostGIS-syntax-error-tp3921276p3921452.html
Sent from the Mapserver - User mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20091030/193f2d7c/attachment.html