PostGIS Connection Trouble

[Pericles S. Nacionales]

Hi Arnulf,

Is it possible that you don't have the proper permission on one of the tables 
you're using?  The USER_POLYGON table perhaps?  The queries all look correct 
to me.

Cheers!
-Perry

On Wednesday 19 January 2005 11:05 am, Arnulf Christl 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.

-- 
Pericles S. Nacionales

Dept. of Ecology, Evolution, and Behavior
University of Minnesota
work: 612 624 6713
e-mail: nacional at cbs.umn.edu