Solved: [UMN_MAPSERVER-USERS] PostGIS Connection Trouble

[Arnulf Christl (CCGIS)]

Hi,
thanx a lot - the capitals were the problem. Stupid me, yet again! Maybe
somebody (Paul :-) will have pity on me and remedy this capital string
problem. Else you tell me why these work:

DATA "geom from (select geom as geom, fld2 as fld2 from tab)as foo using
unique oid SRID=12345"

DATA "geom from (SELECT geom AS geom, fld2 AS fld2 FROM tab)as foo using
unique oid SRID=12345"

and these wont:
DATA "geom from (select geom as geom, fld2 as fld2 from tab)AS foo using
unique oid SRID=12345"

While you are at it don't forget to bump up the PostGIS version from 0.9
to 1.0...


Best regards,
Arnulf

> http://mapserver.gis.umn.edu/bugs/show_bug.cgi?id=623
>
> Sean
>
> On Jan 20, 2005, at 10:38 AM, Steven Monai wrote:
>
>> 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=3DPNG OUTPUT=3DJPEG OUTPUT=3DWBMP
>>> SUPPORTS=3DPROJ SUPPORTS=3DFREETYPE SUPPORTS=3DWMS_SERVER
>>> SUPPORTS=3DWMS_CL=
>> IENT
>>> SUPPORTS=3DWFS_SERVER SUPPORTS=3DWFS_CLIENT SUPPORTS=3DWCS_SERVER
>>> INPUT=3DPOSTGIS INPUT=3DORACLESPATIAL INPUT=3DOGR INPUT=3DGDAL
>>> INPUT=3DSHAP=
>> EFILE -->
>>>
>>> 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=3D'1.0' encoding=3D"ISO-8859-1" standalone=3D"no" ?>
>>> <!DOCTYPE ServiceExceptionReport SYSTEM
>>> "http://schemas.opengeospatial.net/wms/1.1.1/exception_1_1_1.dtd">
>>> <ServiceExceptionReport version=3D"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 =C2=BBsyntax
>>> error=C2=AB=
>>  bei
>>> =C2=BBWHERE=C2=AB 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=3D&lt;srid#&gt;'
>>> <br><br>
>>>
>>> Make sure you put in the 'using unique &lt;column
>>> name&gt;' and 'using SRID=3D#' 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=3D31467'.<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=3D&lt;srid#&gt;'
>>> <br><br>
>>>
>>> Make sure you put in the 'using unique &lt;column
>>> name&gt;' and 'using SRID=3D#' 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=3D31467"
>>>
>>> # 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=3D31467"
>>>
>>> # 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=3D31467;"
>>>
>>> # 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=3D31467"
>>>
>>> # 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=3D31467"
>>>
>>> # DATA 'the_geom FROM (SELECT betrnr, jahr FROM user_polygon) AS foo
>>> USING UNIQUE SRID=3D31467'
>>>
>>> # DATA "the_geom FROM user_polygon"
>>>
>>>
>>> Best regards,
>>> Arnulf.
>>
>
>