[postgis-users] Postgis Mapserver Query returns the same itemmultiple times.

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of James Martin
> Sent: 17 November 2004 16:17
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Postgis Mapserver Query returns the 
> same itemmultiple times. 
> 
> 
> Hello,
> 
> Any help is greatly appreciated.  
> 
> I'm trying to perform an nquery using the following
> DATA statement in my mapfile (formatted for e-mail):
> 
> 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"
> 
> Whenever I run this query on a map it works, that is
> to say there are no error messages and the information
> I'm expecting is returned.  
> 
> The problem is it returns the information 'N' times,
> where N is the number of rows in the place table.  


Hi James,

I think this may be caused by a PostgreSQL feature in that if you reference
a column name by itself and also by alias then PostgreSQL will go and
calculate the cross-product of the query instead.

Can you try the following below and see if this works for you?

 DATA = "the_geom from
 (SELECT the_geom,
		gid,
            name
            FROM place
            WHERE gid=9)
              AS foo USING UNIQUE gid
              USING SRID=-1"


If the result is no different, you may like to try increasing the logging on
your PostgreSQL server and then posting back to the list the SELECT
statement that Mapserver is generating to query your data. If running the
query in psql returns the result N times, then it is a problem with the
query and not with Mapserver.


Kind regards,

Mark.

------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk