[mapserver-users] PostGIS syntax error

[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 <tspradley at snoogems.com> 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 &quot;,&quot;
> LINE 1: ....wkb_geometry ) = 0) as myquery where wkb_geometry,county,st...
>                                                             ^
> ) executing query: select
> encode(AsBinary(force_collection(force_2d(&quot;wkb_geometry,county,state&quot;)),&#39;NDR&#39;),&#39;base64&#39;)
> as geom,&quot;ogc_fid&quot; from (SELECT DISTINCT a.ogc_fid, a.wkb_geometry,
> a.county, a.state FROM countyp020 a, countyp020 b WHERE b.county=&#39;Brown
> County&#39; AND b.state=&#39;TX&#39; AND a.wkb_geometry &amp;&amp;
> b.wkb_geometry AND distance( a.wkb_geometry, b.wkb_geometry ) = 0) as
> myquery where wkb_geometry,county,state &amp;&amp;
> GeomFromText(&#39;POLYGON((-98.7789255453408
> 31.1572855453408,-98.7789255453408 31.1572855453408,-98.7789255453408
> 31.1572855453408,-98.7789255453408 31.1572855453408,-98.7789255453408
> 31.1572855453408))&#39;,-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
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>