PostGIS Connection Trouble

Arnulf Christl arnulf.christl at CCGIS.DE
Wed Jan 19 09:05:19 PST 2005


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.



More information about the MapServer-users mailing list