Postgis nquery returns the same result multiple times

[Ken Lord]

Hi James,

I think it is just a pesky syntax problem.

Here's two DATA strings I use that may be of help.

This example is the standard data string I use to draw all features in a table.

  DATA "the_geom from (select * from bh_tp_p) as foo using unique gid
using SRID=2"

This one, similar to yours, makes MapServer draw Only the features
that pass the test. (In this case, draw only the drill holes that fall
within a specific mineral claim)

DATA "the_geom from (select * from bh_tp_p where
within(bh_tp_p.the_geom,( select the_geom from mineral_tenure_a where
mineral_tenure_a.claim='ML-5518' ))=true ) as foo using unique gid
using SRID=2"

In this case I had to include the ' =true ' term ... draw the features
where the 'within' test returns true, a detail that took awhile for me
to get right.

You may want to simplify your query by using the ' * ' wildcard, the
specific fields you need will still be in the recordset returned.

Or, maybe there's a conflict where you define the alias ' place.gid AS
gid ' but carry on to use the terms ' place.gid ' instead of its
alias.

Or maybe there's a conflict by testing ' WHERE place.gid=9 ', and then
using the term ' USING UNIQUE place.gid ' instead of using the term '
USING UNIQUE gid '

Anyways, there's a couple ideas for you, good luck!

Ken Lord


On Wed, 17 Nov 2004 11:26:47 -0600, James Martin <martin426 at yahoo.com> wrote:
> Hello,
>
> I'm trying to perform a nquery on a postgis layer and am recieving a weird
> result.  If my DATA = "the_geom from place" the query works fine.  However,
> when trying:
>
> DATA = "the_geom from
> (SELECT place.the_geom AS the_geom,
>             place.gid AS gid,
>             place.name AS name
>             FROM place
>             WHERE place.gid=9)
>             AS foo USING UNIQUE place.gid
>             USING SRID=-1"
>
> And then bound a point with &imgbox the query returns the correct record,
> but returns it 'N' times, where N is the number of rows in the table.  The
> map itself shows only the one point specified in the WHERE clause. If I
> remove the WHERE clause so I can see the other points and bound two of them,
> the query returns the first record 2 * N times.
>
> If I do just a query with &imgxy it returns just the one result.
>
> Any help is greatly appreciated!
>
> -James
>


==============================
Ken Lord B.Sc., A.Dipl.T.H.
Burnaby BC, V5E 4L1
kenlord (at) gmail.com
klord (at) bgcengineering.com