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

Thu May 6 08:52:05 PDT 2004

This is a multi-part message in MIME format.

------_=_NextPart_001_01C43382.1431D6CC
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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.
=20
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.
=20
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.
=20
Here's my query DATA line:
=20
=20
 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=3D4326"
=20
=20
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.
=20
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.
=20
Thanks,
=20
-ethan

------_=_NextPart_001_01C43382.1431D6CC
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D769333815-06052004>I've =
spent several=20
hours on this trying to make sure my problem isn't PEBKAC. I'm =
unfortunately=20
working with a difficult database schema and am trying to do a fairly=20
complicated join. I have however narrowed down my problem to a very =
simple=20
example.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D769333815-06052004>I have =
a table=20
containing geometry information about satellite images called "snaps". =
The=20
problem is that the table can have multiple records with the same =
catalogid for=20
a number of reasons. What I want to do is to do a "distinct" select on =
fields=20
which I know are the same in multiple records so my mapserver queries =
are only=20
working with unique catalogid's.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D769333815-06052004>The =
basic problem is=20
if I use distinct in my subselect, the query fails with no errors. I =
know it=20
fails because when I look at the HTML source the footer is not present =
in the=20
HTML file. This leads me to believe my query has somehow borked MS. If I =
remove=20
the "distinct" term in my query everything works.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D769333815-06052004>Here's =
my query DATA=20
line:</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D769333815-06052004> DATA "the_geom=20
from (select distinct catalogid, bestimagequality, bestcloudcover,=20
offnadirangle, sunelevation,the_geom,at_float,oid from snaps) as wdi =
using=20
unique oid using srid=3D4326"</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D769333815-06052004>This =
isn't my whole=20
query but this it is the minimal example that demonstrates my problem. I =
have no=20
problem entering the above subselect section in psql. It returns exactly =
what I=20
want.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D769333815-06052004>Can =
anyone shed any=20
light on why adding distinct to my subselect would cause MS's query to =
fail? I=20
should add that the map display appears fine and I have polygons for my=20
selections. I can find no errors in logs anywhere.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004>Thanks,</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D769333815-06052004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D769333815-06052004>-ethan</DIV>
<DIV><BR></DIV></SPAN></FONT></BODY></HTML>
=00
------_=_NextPart_001_01C43382.1431D6CC--