[mapserver-users] PostGIS connecton error

[Steve White]

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
<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?

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080708/c86e5656/attachment-0001.html