[mapserver-users] PostGIS connecton error

[Paul Ramsey]

Both the_geom and gid (or whatever you declare are your
geometry-holding and uid-holding columns) have to exist as output from
your subquery, otherwise the code on the outside can't see them to
make use of them. So change your DATA to

DATA "the_geom from (SELECT the_geom,gid from
medievalbattlefieldstable WHERE itemname LIKE '%Lewes%') as foo using
unique gid using SRID=4326"

P.

On Tue, Jul 8, 2008 at 11:07 AM, Steve White
<steve at online-archaeology.co.uk> wrote:
> After much fiddling around I managed to install PostgreSQL with PostGIS and
> set up a connection in a layer like this:
>
>
>
> MAP
>
>   NAME                                                                 Test
>
>   STATUS                                              ON
>
>   UNITS                                                 METERS
>
>   SHAPEPATH                                      "C:\Inetpub\scripts\data\"
>
>   IMAGECOLOR                                  0 255 255
>
>   SIZE                                                      400 400
>
>   DEBUG                                                               ON
>
>   IMAGETYPE                                      PNG
>
>   EXTENT                                               -4.35 56.14 -3.23
> 56.91
>
>   DATAPATTERN                                                '.*'
>
>
>
> WEB
>
>   METADATA
>
>     "wms_title"                                    "Test MS"
>
>     "wms_onlineresource"             "http://localhost/scripts/mapserv.exe&"
>
>     "wms_srs"                                      "EPSG:4326 EPSG:54004
> EPSG:27700"
>
>     "wms_feature_info_mime_type" "text/html"
>
>   END
>
>
>
> IMAGEPATH                                       "C:\Inetpub\wwwroot\tmp\"
>
> IMAGEURL                                          "C:\Inetpub\wwwroot\tmp\"
>
> LOG
> "C:\Inetpub\wwwroot\tmp\mapserver.log"
>
> TEMPLATE
> "C:\Inetpub\wwwroot\OAMap\templates\template.html"
>
> EMPTY
> "C:\Inetpub\wwwroot\OAMap\templates\template.html"
>
>
>
> END
>
>
>
> #QUERYMAP
>
> #             STATUS                                                ON
>
> #             STYLE                                     HILITE
>
> #             COLOR                                  255 255 0
>
> #             SIZE                                        10
>
> #END
>
>
>
> PROJECTION
>
>   "init=epsg:4326"
>
> END
>
>
>
> SYMBOL
>
>   NAME "circle"
>
>   TYPE ellipse
>
>   FILLED true
>
>   POINTS
>
>     1 1
>
>   END
>
> END
>
>
>
> LAYER
>
>                 NAME
> "test"
>
>                 CONNECTIONTYPE
> postgis
>
>                 CONNECTION                                    "user=oa
> password=******* dbname=OA"
>
>                 METADATA
>
>         "wms_title"                                                "test"
>
>                 "gml_include_items"                     "all"
>
>                 "wms_include_items"                   "all"
>
>                 "wms_feature_info_mime_type"           "text/html"
>
>                 END
>
>                 TYPE
> POINT
>
>                 DATA
> "the_geom from (SELECT the_geom from medievalbattlefieldstable WHERE
> itemname LIKE '%Lewes%') as foo using unique gid using SRID=4326"
>
>
> STATUS                                                                ON
>
>                 DUMP                                                   TRUE
>
>
>
>                 PROJECTION
>
>
>
> "init=epsg:4326"
>
>                 END
>
>         CLASS
>
>                                 TEMPLATE
> "C:\Inetpub\wwwroot\OAMap\templates\template.html"
>
>                                 SYMBOL "circle"
>
>                                 SIZE 10
>
>                                 COLOR 255 0 255
>
>         END
>
> END #end of layer
>
>
>
> END # end of mapfile
>
>
>
> Here's the exception from MapServer:
>
>
>
> <ServiceExceptionReport version="1.1.1">
>
> <ServiceException>
>
> msDrawMap(): Image handling error. Failed to draw layer named
> &#39;test&#39;.
>
> prepare_database(): Query error. Error executing POSTGIS DECLARE (the actual
> query) statement: &#39;DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(the_geom)),&#39;NDR&#39;),gid::text from
> (SELECT the_geom from medievalbattlefieldstable WHERE itemname LIKE
> &#39;%Lewes%&#39;) as foo WHERE the_geom &amp;&amp;
> setSRID(&#39;BOX3D(-5.625 52.4827802220782,0 55.7765730186677)&#39;::BOX3D,
> 4326 )&#39;
>
>
>
> Postgresql reports the error as &#39;ERROR:  column &quot;gid&quot; does not
> exist
>
> LINE 1:
> ...inary(force_collection(force_2d(the_geom)),&#39;NDR&#39;),gid::text ...
>
>                                                              ^
>
> &#39;
>
>
>
> More Help:
>
>
>
> Error with POSTGIS data variable. You specified &#39;check your .map
> file&#39;.
>
> Standard ways of specifiying are :
>
> (1) &#39;geometry_column from geometry_table&#39;
>
> (2) &#39;geometry_column from (sub query) as foo using unique column name
> using SRID=srid#&#39;
>
>
>
> Make sure you put in the &#39;using unique  column name&#39; and &#39;using
> SRID=#&#39; clauses in.
>
>
>
> For more help, please see http://postgis.refractions.net/documentation/
>
>
>
> Mappostgis.c - version of Jan 23/2004.
>
>
>
> </ServiceException>
>
> </ServiceExceptionReport>
>
>
>
> As you can see, it says column 'gid' does not exist. This doesn't make any
> sense to me because I can see a gid column in my table and it has been set
> up as the PK. If I take out the sub query and just run it in the Query tool
> using pgAdmin III it works!
>
>
>
> SELECT the_geom from medievalbattlefieldstable WHERE itemname LIKE '%Lewes%'
>
>
>
> Can anyone give me some clues what's going on here?
>
>
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>