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

Fri May 7 13:34:27 PDT 2004

I haven't actually tried, but I wonder if you could use a view ? Hide the DISTINCT inside the view, and simply call that from MapServer ?

And yes, this problem seems like an unhappy thing to come across...

G

-----Original Message-----
From: Ethan Alpert [mailto:ealpert at digitalglobe.com]
Sent: Friday, May 07, 2004 12:22 PM
To: 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)

Man this is a real bummer I can't get MS to work with the distinct
query. I'm having to create my own shape file :(

-----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:55 PM
To: 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)

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

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

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