getShape: problem with PostGIS table containing a non-text column. -> OK // php vs. C perf.

[Thierry Lombardot]

Thanks a lot Abe,
Adding postgres views in the map file seems to be the best way, since the
php/mapscript functions are quite generic. I'll work on it.
Besides, I'll also try my own Poatgres queries with DB.php using the oid
retrieved by MapServer's querybypoint().

Could you please comment on the C vs. php performances for MapServer? At
which number of layers/entries do I have to say goodbye to the
php/mapscripts? At the moment, I can generate a map within 1,5 sec with
2000 polygons in one table and <100 points/polygons in 6 other tables.
Strangely, if I add the labels of the large table, my performances are
ridiculous, e.g.  up to 3 sec for a map. Is the label positioning known to
be slow?

Best regards,

Thierry

> 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
>> >> >
>> >>
>> >
>> >
>>
>>
>