[Mapserver-users] postgis query fails with no errors when I use distinct subselect (MS4.0.2)

[Gregory S. Williamson]

Ethan,

 Does the query work in postgres (psql or pgAdmin, or whatever) ? Any errors in the postgres log ?

 I seem to recall using a form of "DISTINCT ON (colname)" but am not sure if the parenthesis are required (I don't have immediate access to a postgres server so I am not able to test this).

Greg Williamson
DBA
GlobeXplorer LLC


-----Original Message-----
From:	Ethan Alpert [mailto:ealpert at digitalglobe.com]
Sent:	Thu 5/6/2004 8:52 AM
To:	mapserver-users at lists.gis.umn.edu
Cc:	
Subject:	[Mapserver-users] postgis query fails with no errors when I use distinct subselect (MS4.0.2)
I've spent several hours on this trying to make sure my problem isn't
PEBKAC. I'm unfortunately working with a difficult database schema and
am trying to do a fairly complicated join. I have however narrowed down
my problem to a very simple example.
 
I have a table containing geometry information about satellite images
called "snaps". The problem is that the table can have multiple records
with the same catalogid for a number of reasons. What I want to do is to
do a "distinct" select on fields which I know are the same in multiple
records so my mapserver queries are only working with unique
catalogid's.
 
The basic problem is if I use distinct in my subselect, the query fails
with no errors. I know it fails because when I look at the HTML source
the footer is not present in the HTML file. This leads me to believe my
query has somehow borked MS. If I remove the "distinct" term in my query
everything works.
 
Here's my query DATA line:
 
 
 DATA "the_geom from (select distinct catalogid, bestimagequality,
bestcloudcover, offnadirangle, sunelevation,the_geom,at_float,oid from
snaps) as wdi using unique oid using srid=4326"
 
 
This isn't my whole query but this it is the minimal example that
demonstrates my problem. I have no problem entering the above subselect
section in psql. It returns exactly what I want.
 
Can anyone shed any light on why adding distinct to my subselect would
cause MS's query to fail? I should add that the map display appears fine
and I have polygons for my selections. I can find no errors in logs
anywhere.
 
Thanks,
 
-ethan