AW: [Mapserver-users] postgis data statement

Mon Mar 29 11:39:30 PST 2004

Gernot,

Can you check your apache error log?  It should tell you what's going
wrong.  Sorry I can't be much help here but your apache log should give
you a pretty good idea of what's wrong.

Good luck!
-Perry

On Mon, 29 Mar 2004, Gernot Katzlberger wrote:

> 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
> >
>
>
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>