[postgis-users] Multiple table join - mapserver

[Patricio Cifuentes Ithal]

hi...
add suplex query with extension....

example

WHERE SetSRID('BOX3D(".$ext[0]." ".$ext[1].",".$ext[2]."
".$ext[3].")'::box3d,-1) && the_geom ) as foo using unique gid using SRID=-1

only query result data viewer...

-- 

Patricio Cifuentes Ithal
Ingeniero en Informática

Informática
SIIGSA
(56-2) 204 60 22
----- Original Message ----- 
From: "Paul Ramsey" <pramsey at refractions.net>
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Sent: Friday, May 19, 2006 8:01 PM
Subject: Re: [postgis-users] Multiple table join - mapserver


> I am surprised it is eating up memory, but not surprised it is slow,
> it is possible you are getting a bad plan... make sure you have run
> VACUUM ANALYZE.
>
> You could try:
>
> DATA "the_geom FROM (SELECT akmon_pt.the_geom AS the_geom ,akmon_pt.maid
> AS maid,akmon_pt.locid AS locid FROM akmon_pt,i5dbxma , i5maxat where
> akmon.the_geom && !BOX! and akmon_pt.maid=i5dbxma.maid and
> i5maxat.maid=i5dbxma.maid and i5maxat.atid=1 and i5dbxma.fyear>=1992
> and i5dbxma.lyear<=2005 and
> i5dbxma.spp=2 and i5dbxma.esu=-363155345) AS foo USING UNIQUE locid
> USING SRID=-1"
>
> I am not sure how robust the DATA parsing method is, but the !BOX!
> hack exists as a way of ensuring Mapserver creates a SQL query
> exactly the way you expect, rather than tacking the box on the end
> and hoping the PgSQL planner can resolve it into something efficient
> (usually, it can).
>
> Paul
>
>
>
> On May 19, 2006, at 1:49 PM, Greg Robillard wrote:
>
> > I'm using a subselect in my mapfile that uses three tables:
> > DATA "the_geom FROM (SELECT akmon_pt.the_geom AS
> > the_geom ,akmon_pt.maid
> > AS maid,akmon_pt.locid AS locid FROM akmon_pt,i5dbxma , i5maxat where
> > akmon_pt.maid=i5dbxma.maid and i5maxat.maid=i5dbxma.maid and
> > i5maxat.atid=1 and i5dbxma.fyear>=1992 and i5dbxma.lyear<=2005 and
> > i5dbxma.spp=2 and i5dbxma.esu=-363155345) AS foo USING UNIQUE locid
> > USING SRID=-1"
> >
> > It causes postgres to eat up all available memory on the machine. If I
> > execute the above in psql, the record returns instantly. If I
> > attempt to
> > join only two tables, Mapserver works fine.
> >
> > Is this a known limitation?
> >
> > Best,
> >
> > Greg Robillard
> > Database Developer
> > State of the Salmon
> > greg at ecotrust.org
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users