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

[Abe Gillespie]

In your map file you've got something like this for a PostGIS layer:

LAYER
...
DATA "the_geom from table"
...
END

Now just create a view of the fields you want, including the_geom of
course, and replace "table" with the view's name.  Such as:

DATA "the_geom from my_view_with_nice_fields"

Does this work?

Yes I do PHP scripting, though I'm beginning to favor the C# stuff.  I
have been working more with PostGIS than anything lately, though, as
I'm working on an ArcGIS -> PostGIS connector.

-Abe

On 4/26/05, Thierry Lombardot <tlombard at mpi-bremen.de> wrote:
> 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
> >> >
> >>
> >
> >
> 
>