PostGIS Connection Trouble

[Steven Monai]

Whoops, guess I should be a little more observant. You're using a recent
version of MapServer. Is it possible that the PostGIS-keyword-case bug is
still alive?

-Steve M.
--



On Wed, 19 Jan 2005 18:05:19 +0100, Arnulf Christl
<arnulf.christl at CCGIS.DE> wrote:

>Hi,
>I am at a loss, we use MapServer/PostGIS every day but this one wont work.
>
>PostgreSQL 7.4.2
>PostGIS 0.9.0
>SuSE Linux Enterprise Server 9
>
><!-- MapServer version 4.4.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP
>SUPPORTS=PROJ SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT
>SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER
>INPUT=POSTGIS INPUT=ORACLESPATIAL INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE -->
>
>The error message looks strange to me, never saw that one. Especially
>the part:
>"oid::text from WHERE &&"
>Anybody any idea? See the MAP-file DATA string at the bottom.
>
>###
><?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?>
><!DOCTYPE ServiceExceptionReport SYSTEM
>"http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">
><ServiceExceptionReport version="1.1.1">
><ServiceException>
>msDrawMap(): Image handling error. Failed to draw layer named
>'poly'.
>prep_DB(): Query error. Error executing POSTGIS DECLARE (the actual
>query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
>asbinary(force_collection(force_2d()),'NDR'),oid::text from
>WHERE && setSRID('BOX3D(3275833.33333155
>5400833.33332511,3524166.66666667 5649166.66666667)'::BOX3D,
>find_srid('','','') )' <br><br>
>
>Postgresql reports the error as 'FEHLER: Fehler »syntax error« bei
>»WHERE« at character 104
>'<br><br>
>
>More Help:<br><br>
>
>Error with POSTGIS data variable. You specified '&lt;check your
>.map file&gt;'.<br>
>
>Standard ways of specifiying are : <br>
>(1) 'geometry_column from geometry_table' <br>
>(2) 'geometry_column from (&lt;sub query&gt;) as foo using
>unique &lt;column name&gt; using SRID=&lt;srid#&gt;'
><br><br>
>
>Make sure you put in the 'using unique &lt;column
>name&gt;' and 'using SRID=#' clauses in.
>
><br><br>For more help, please see
>http://postgis.refractions.net/documentation.php
>
><br><br>Mappostgis.c - version of Jan 23/2004.
>
>msPOSTGISLayerParseData(): Query error. Error parsing POSTGIS data
>variable. Must contain 'geometry_column from table_name' or
>'geom from (subselect) as foo' (couldnt find ' from ').
>More help: <br><br>
>
>Error with POSTGIS data variable. You specified 'SELECT the_geom
>FROM (SELECT the_geom AS the_geom, betrnr AS betrnr, jahr AS jahr, oid
>AS oid FROM user_polygon) AS foo USING UNIQUE oid
SRID=31467'.<br>
>Standard ways of specifiying are : <br>
>(1) 'geometry_column from geometry_table' <br>
>(2) 'geometry_column from (&lt;sub query&gt;) as foo using
>unique &lt;column name&gt; using SRID=&lt;srid#&gt;'
><br><br>
>
>Make sure you put in the 'using unique &lt;column
>name&gt;' and 'using SRID=#' clauses in.
>
><br><br>For more help, please see
>http://postgis.refractions.net/documentation.php
>
><br><br>Mappostgis.c - version of Jan 23/2004.
>
></ServiceException>
>
></ServiceExceptionReport>
>###
>
>The history of my helplessness from bottom to top. We've tried the
>SELECTS with capitals, repeated uses of 'USING' etc. wont work.
>
>DATA "SELECT the_geom FROM (SELECT the_geom AS the_geom, betrnr AS
>betrnr, jahr AS jahr, oid AS oid FROM user_polygon) AS foo USING UNIQUE
>user_polygon.oid USING SRID=31467"
>
># DATA "the_geom FROM (SELECT the_geom AS the_geom, betrnr AS betrnr,
>jahr AS jahr, oid AS oid FROM user_polygon) AS foo USING UNIQUE oid
>USING SRID=31467"
>
># DATA "SELECT the_geom FROM (SELECT the_geom AS the_geom, betrnr AS
>betrnr, jahr AS jahr, oid AS oid FROM user_polygon) AS foo USING UNIQUE
>oid USING SRID=31467;"
>
># DATA "SELECT the_geom FROM (SELECT the_geom AS the_geom, betrnr AS
>betrnr, jahr AS jahr, oid AS oid FROM user_polygon) AS foo USING UNIQUE
>oid USING SRID=31467"
>
># DATA "the_geom FROM (SELECT the_geom AS the_geom, betrnr AS betrnr,
>jahr AS jahr, oid AS oid FROM user_polygon) as foo using unique oid
>using SRID=31467"
>
># DATA 'the_geom FROM (SELECT betrnr, jahr FROM user_polygon) AS foo
>USING UNIQUE SRID=31467'
>
># DATA "the_geom FROM user_polygon"
>
>
>Best regards,
>Arnulf.