[Mapserver-users] postgis data statement

[Eric Bridger]

Gernot,
I just want to confirm that I have experienced identical problems. I
can't offer much help because I never solved them but did not have the
time or knowledge to pursue it. 

Complicated subqueries in my DATA statements, or self-joins, sometimes
just adding a small table to an existing geometry view.  The SQL would
work fine from psql but take forever using the mapserver/postgis API. 
Postgres FETCH processes would remain hanging and would have to be
killed manually.

So if you figure it out please let us know.

My plan in the future is to upgrade all my software and try again.

On Fri, 2004-03-26 at 08:14, Gernot Katzlberger wrote:
> hi pericles, zamil & list,
> 
> my postgis data statement works now in Mapserver, but it needs ~5 min to
> render it. when I start it, 99% of my system cpu usage is occupied by one
> process:
> 
> postgres: {user} {database} 127.0.0.1 FETCH
> 
> 
> when I execute it in psql it works in milliseconds.
> mapserver and postgresql/postgis are on the same server. I also checked the
> user privileges.
> 
> many thanks for your help,
> 
> gernot
> 
> 
> 
> 
> -----Ursprüngliche Nachricht-----
> Von: mapserver-users-admin at lists.gis.umn.edu
> [mailto:mapserver-users-admin at lists.gis.umn.edu] Im Auftrag von Pericles
> Nacionales
> Gesendet: Donnerstag, 25. März 2004 16:52
> An: Zamil Murji
> Cc: knozi; mapserver-users at lists.gis.umn.edu
> Betreff: RE: [Mapserver-users] postgis data statement
> 
> Gernot,
> 
> Zamil is correct.  If the SQL statement worked in psql (or some other
> frontend), it should work in MapServer.  If not, the first place to check
> would be your PostgreSQL configuration and permissions.  If your database
> is running remotely you might want to check that you have configured
> postgres to accept remote connections.  If that is the case, try
> connecting as postgres (or whatever pg admin account you created).  If it
> works, read the PostgreSQL docs for adding users.  Sometimes it's just a
> matter of giving an existing database user select privileges on your
> postgis tables.  I've posted other messages to related questions in the
> past.  Have a look at the MapServer-users archive as I don't have a
> browser open.
> 
> Cheers!
> 
> Pericles S. Nacionales
> nacional at biosci.umn.edu
> 
> 
> On Thu, 25 Mar 2004, Zamil Murji wrote:
> 
> > hi,
> >
> > Maybe it isn't the data statement that is hooped. Maybe you don't have the
> proper permissions set for the database. Does the user/password that you
> supply to your connection string work? Is it a TRUSTed user? Maybe you need
> to give the box that mapserver is installed on TRUSTed access to that
> postgres db. Take a look at your pg_conf.hba file (i think that is what it
> is called).
> >
> > Hope this helps.
> > Zamil
> >
> > -----Original Message-----
> > From: mapserver-users-admin at lists.gis.umn.edu
> > [mailto:mapserver-users-admin at lists.gis.umn.edu]On Behalf Of knozi
> > Sent: Thursday, March 25, 2004 4:34 AM
> > To: mapserver-users at lists.gis.umn.edu
> > Subject: [Mapserver-users] postgis data statement
> >
> >
> > hi list,
> > i've some problems with a postgis data statement.
> >
> > DATA "the_geom from
> >     	(select gd_world_samplesite.the_geom,td_samplesite.maxdepth,case
> > 		when td_samplesite.maxdepth > 1000 then 1
> > 		when td_samplesite.maxdepth > 500 then 2
> > 		else 3
> > 		end as myclass
> > 	from gd_world_samplesite,td_samplesite where
> > gd_world_samplesite.ssid = td_samplesite.samplesiteid)
> > 	as foo using unique gd_world_samplesite.oid using SRID=4326"
> >
> > when I execute the statement in postgres it works fine, but mapserver
> cannot
> > handle it.
> > The funny thing is that a similar statement on the same server, but
> > accessing another database, works.
> >
> > I'm using linux, postgresql 7.3.4, postgis 8.1 and Mapserver 4.1.
> >
> > any hints?
> >
> > thanks,
> > gernot
> >
> >
> >
> >
> > _______________________________________________
> > Mapserver-users mailing list
> > Mapserver-users at lists.gis.umn.edu
> > http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> >
> > _______________________________________________
> > Mapserver-users mailing list
> > Mapserver-users at lists.gis.umn.edu
> > http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> >
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
> 
> 
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>