[mapserver-users] Re: [postgis-users] sql works, but not as l ayer?

[Twan Kogels]

I tryed your suggestion immediately and the error "Attribute 'oid' not 
found" disappeared. I also found 
<http://mapserver.gis.umn.edu/wilma/mapserver-users/0110/msg00283.html> 
which explains sub-select feature.

Now all errors are disappeared, but now postgis is not returning any 
points. For simplicity i now use a simple test query:
DATA "the_geom from (SELECT oid, * from pc6punten) as foo"

When i execute this query in my postgresql client (psql) all points in 
tabel pc6punten are returned. But when i try it as layer definition in 
mapserver nothing is returned (not even a error).

My layer definition is:

LAYER
   CONNECTIONTYPE postgis
   NAME "poi_layer"

   CONNECTION "user=postgres dbname=food host=10.0.0.2"
   DATA "the_geom from (SELECT oid, * from pc6punten) as foo"
   STATUS ON
   TYPE POINT
   CLASSITEM "pc6"
   CLASS
     TEMPLATE "ttt_query.html"
     SYMBOL 2
     SIZE 8

     COLOR 0 0 0
   END
   TOLERANCE 5
END

When i replace the DATA field to:
DATA "the_geom from pc6punten"
all correct points are returned and displayed.

I hope this is the last time, but does anybody know what i'm doing wrong?

Best regards,
-twan

At 17:36 4-6-2002 -0400, you wrote:
>I think maybe the problem is you have two oids (so it is unclear which is
>which) in your select.  Try setting your select statement such that you only
>have one oid (the oid from the spatial table).
>
>Something like
>(SELECT o.oid, o.the_geom, p.myfield1, p.myfield2, etc. from
>objecteigenschappen o left join
>pc6punten p on p.pc6=o.eigenschap_3) as foo"
>
>
>
>-----Original Message-----
>From: Twan Kogels [mailto:twan at twansoft.com]
>Sent: Tuesday, June 04, 2002 3:57 PM
>To: Dave Blasby
>Cc: postgis-users at postgis.refractions.net;
>mapserver-users at lists.gis.umn.edu
>Subject: Re: [mapserver-users] Re: [postgis-users] sql works, but not as
>layer?
>
>
>I'm sorry, but after tons of trying and recreating tables it still doesn't
>work. I've simplyfied the query:
>
>CONNECTION "user=postgres dbname=food host=10.0.0.2"
>DATA "the_geom from (SELECT * from objecteigenschappen o left join
>pc6punten p on p.pc6=o.eigenschap_3) as foo"
>FILTER "pc6 like '1797%'"
>
>I still get the postgresql error "ERROR:  Attribute 'oid' not found", but i
>have defined a index for oid for both (pc6punten en objecteigenschappen)
>tables. Also geometry_columns table is correctly setup.
>
>Does anybody knows what is going wrong?
>
>Best regards,
>-twan
>
>At 09:43 4-6-2002 -0700, you wrote:
> > > CONNECTION "user=postgres dbname=food host=10.0.0.2"
> > > DATA "the_geom from objecteigenschappen o left join pc6punten p on
> > > p.pc6=o.eigenschap_3"
> > > FILTER "(the_geom && GeometryFromText('BOX3D(109258 559953,115258
> > > 565953)'::box3d,-1) and distance(the_geom,
>GeometryFromText('POINT(112258
> > > 562953)',-1)) < 3000)"
> >
> >
> >You're using the DATA parameter incorrectly - its a bit confusing.
> >
> >The generic templates are:
> >
> >DATA "<geom column name> from <table name>"
> >         or
> >DATA "<geom column name> from (<sql statement>) as foo"
> >
> >In your case, use:
> >
> >DATA "the_geom from (SELECT * from objecteigenschappen o left join
> >pc6punten p on p.pc6=o.eigenschap_3) as foo"
> >
> >hope that works,
> >
> >dave
>
>--
>http://www.twansoft.com
>
>
>_______________________________________________
>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

-- 
http://www.twansoft.com