AW: [Mapserver-users] postgis data statement

[Gernot Katzlberger]

hi perry & list,

thanks for your help, a part of my problem is now fixed. I added a primary
key to the postgis tables and it worked. now a discovered another problem. 


this statement using 3 classes works prefect:

>DATA "the_geom from (select
>gd_world_samplesite.the_geom,gd_world_samplesite.oid,td_samplesite.coordina
>tesaccuracy,case
>when td_samplesite.coordinatesaccuracy = 'A' then 0
>when td_samplesite.coordinatesaccuracy = 'P' then 1
>when td_samplesite.coordinatesaccuracy = 'S' then 2
>end as myclass
>from gd_world_samplesite,td_samplesite where gd_world_samplesite.ssid =
>td_samplesite.samplesiteid)
>as foo USING SRID=4326"


when I'm adding only one more class I get an "Internal Server Error" the
apache error message says:"Premature end of script headers:
/srv/www/cgi-bin/php". 

>DATA "the_geom from (select
>gd_world_samplesite.the_geom,gd_world_samplesite.oid,td_samplesite.coordina
>tesaccuracy,case
>when td_samplesite.coordinatesaccuracy = 'A' then 0
>when td_samplesite.coordinatesaccuracy = 'P' then 1
>when td_samplesite.coordinatesaccuracy = 'S' then 2
>when td_samplesite.coordinatesaccuracy = 'T' then 3
>end as myclass
>from gd_world_samplesite,td_samplesite where gd_world_samplesite.ssid =
>td_samplesite.samplesiteid)
>as foo USING SRID=4326"

I' using php mapscript on a suse linux and postgres 7.3.4/postgis 0.8.1
(PHP MapScript Version  ($Revision: 1.190 $ $Date: 2004/01/30 17:01:12 $))

MapServer version 4.1 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP
OUTPUT=PDF OUTPUT=SWF SUPPORTS=PROJ SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER
SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT INPUT=EPPL7
INPUT=SDE INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE  

the statements are working in psql so I don't think it's a postgis problem.
perhaps it is more on the mapscript side?

many thanks,
 gernot


-----Ursprüngliche Nachricht-----
Von: Pericles Nacionales [mailto:nacional at cbs.umn.edu] 
Gesendet: Freitag, 26. März 2004 15:14
An: Gernot Katzlberger
Cc: mapserver-users at lists.gis.umn.edu; Zamil.Murji at divestco.com
Betreff: RE: [Mapserver-users] postgis data statement

The first thing that comes to mind is... indices!  But then I can't know
what's going on with your database without any info.  A little more
background on what you have (hardware, database, mapserver config, sql
query) would be helpful.

Are you running the exact same query on psql and MapServer when you
compared the execution time?

Have you created gist indices on your postgis tables?  If not, visit
postgis's documentation and follow the example on creating a gist index.

Good luck!
-Perry

On Fri, 26 Mar 2004, 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
>