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

[Abe Gillespie]

I'm not the one to talk to for performance ... however, I do know
labelling algorithms can be processor intensive.  Try tweaking the
various labelling options.  For instance, if you're not worried about
duplicates you can save computation by allowing duplicates.

-Abe

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