[Mapserver-users] postgis data statement

[Pericles Nacionales]

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
>