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. |
Paul Ramsey
|
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 |
||||||||||||||||
|
Ted Spradley
|
Some javascript/style in this post has been disabled (why?)
Many thanks Paul!
|
|||||||||||||||
| Free Embeddable Forum Powered by Nabble | Help |
