getShape: problem with PostGIS table containing a non-text column.

[Thierry Lombardot]

Dear MapServer users community,

I have a problem with the getShape function in php/MapScript when using
a PostGIS database.
One of my table contains a column/field of a special type which can't be
converted to text.
Unfortunately, the getShape function wants to convert all the fields to
text and gives an error status.

Did someone fixed this problem already in a custom way?

Perry Casson had the same problem one year ago with a field of type
"bit" (see the attached mails).

I do not want to change my data type, as suggested by David. Is there a
way to force getShape to select only a subset of the PostGIS fields for
example? Or simply to ignore fields which are not castable to text? This
would be a simple solution.

In the long run, as PostGIS becomes more and more popular, is it planned
to add some PostGIS-specific functions to the php/mapscript API? And is
it already possible to use msPOSTGISLayerGetShape() directly instead of
getShape(), for example?

Thank you for your support, and thanks to the refractions.net team for
their great work on php/mapscript!

Best regards

Thierry

My error message (one column is of type simpleparam, a custom type):
########################
msPOSTGISLayerGetShape(): Error executing POSTGIS SQL statement
[...]
cannot cast type simpleparam to text
##########################

Previous discussion:

 >>Perry Casson (perry.casson at waypointinfo.com) wrote:
 >>
 >>[Mapserver-users] problem with PostGIS layers with a bit() column
 >>
 >>I'm quite new to all this MapServer stuff but there appears to be a >>bug
 >>when calling layer->getShape() on a PostGIS layer that contains >>columns
 >>that are of of type bit().  I see the code in mappostgis.c that builds
 >>this query but I'm not up to speed enough to know if the problem >>should
 >>be fixed there or farther up stream.   I'm running 4.0.2 (Linux)
 >>
 >>Here's the error:
 >>Fatal error: [MapServer Error]: msPOSTGISLayerGetShape(): Error
 >>executing POSTGIS SQL statement (in FETCH ALL): DECLARE mycursor2
 >>BINARY
 >>CURSOR FOR SELECT
 >>esn::text,gmuid::text,messageid::text,timestamp::text,recievedat::text,devicetype::text,identifier::text,isgps::text,longitude::text,latitude::text,gpsvalid::text,gpstime::text,v1::text,v2::text,v3::text,max1::text,max2::text,max3::text,actuator_previous::text,actuator_current::text,userdata::text,battery::text,asbinary(force_collection(force_2d(geo)),'NDR')
 >>from wp_point WHERE OID = 27497 -ERROR: Cannot cast type bit to text
 >>
 >>and here how the table being queried is created:
 >>CREATE TABLE wp_point(ESN varchar(12), gmUID varchar(40), messageID
 >>varchar(40), TimeStamp timestamp, RecieveDat timestamp, DeviceType
 >>varchar(32), Identifier varchar(32), IsGPS varchar(2), Longitude
 >>float8,
 >>Latitude float8, GPSValid varchar(2), GPSTime timestamp, V1 int2, V2
 >>int2, V3 int2, Max1 int2, Max2 int2, Max3 int2, Actuator_Previous
 >>bit(8)
 >>, Actuator_Current bit(8) ,UserData varchar(64), Battery varchar(8));
 >>SELECT AddGeometryColumn('sentinel', 'wp_point', 'geo', 4326,
 >>'GEOMETRY', 2);
 >>
 >>Perry Casson
 >
 > David Blasby (dblasby at refractions.net) wrote:
 >
 >As the error says, it cannot convert the bit type to text.
 >
 >You can fix this by either:
 >
 >1. adding a conversion and CAST function to convert bit to text
 >2. change your column type from bit to char.  You can also add a
 >constraint on the the table that says (<column> ='1' OR <column> ='0').
 >
 >dave
 >