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

[Ethan Alpert]

No change with MS4.2b2. still does not appear to parse and display the
template unless distinct is taken out of the query and the query
defnitely returns what I want when run in psql.

-e

-----Original Message-----
From: mapserver-users-admin at lists.gis.umn.edu
[mailto:mapserver-users-admin at lists.gis.umn.edu] On Behalf Of Ethan
Alpert
Sent: Thursday, May 06, 2004 2:14 PM
To: Gregory S. Williamson; mapserver-users at lists.gis.umn.edu
Subject: RE: [Mapserver-users] postgis query fails with no errors when I
use distinct subselect (MS4.0.2)






Thanks for getting back to me. Yes my queries work in psql. I infact get
a map of the geometries selected by the query. The problem comes up when
I try to query the map. Everything works fine without the the distinct
(my template is parsed and a web page displayed). It fails when I add
the distinct clause ( I get the header only). Something is causing MS to
bork when parsing the template for the query.

I tried the DISTINCT ON query and it behaves similarly except it takes
MUCH longer than just using distinct.

I am fairly certain this is a problem in MS. I'm going to build 4.2
beta2 and see what happens.

-e



-----Original Message-----
From: Gregory S. Williamson [mailto:gsw at globexplorer.com] 
Sent: Thursday, May 06, 2004 1:49 PM
To: Ethan Alpert; mapserver-users at lists.gis.umn.edu
Subject: RE: [Mapserver-users] postgis query fails with no errors when I
use distinct subselect (MS4.0.2)


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





_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users