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

[Thierry Lombardot]

Hi Abe,

Yes this would be nice, but the documentation of the getShape methode
doesn't give any hint in this direction.
And the resulting select at the postgres level is doing a "select *"
with text casting. So, no way to select only the field you need...

Is it possible to use the msPOSTGISLayerGetShape() directly? I'll have a
look at the source code o f php/mapscript I think..

Are you using php/mapscript with PostGIS yourself? If so, what is your
strategy to do specific postgres select operation?

Thanks and best regards,

Thierry

Abe Gillespie wrote:
> Just define your query such that it only selects the fields you want.
> Or create a view in the PostgreSQL database.
>
> -Abe
>
> On 4/26/05, Thierry Lombardot <tlombard at mpi-bremen.de> wrote:
>
>>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
>> >
>>
>
>