Thanks Frank,
On 24/10/2008, at 11:47 AM, Frank Warmerdam wrote:
I assume you are placing the SQL query in the DATA statement in the
map file?
No... In the connection part as I wanted to avoid writing a text file
and then referencing it in the mapfile. This is a layer that is
working as anticipated - the SQL part is only 927 characters. (I
realise I probably should have included this before - the SQL is
gross, but returns just the resultvalue, lat and long.)
LAYER
CLASSITEM "outcome"
CONNECTION "<OGRVRTDataSource>
<OGRVRTLayer name='data'>
<SrcDataSource>ODBC:web_user/[EMAIL PROTECTED]</SrcDataSource>
<SrcSQL>select case when res1.resultvalue >= 1 then
'positive' when res1.resultvalue < 1 then 'negative' end as
outcome, s.latitude as latitude, s.longitude as longitude from reports
rep join results res1 on (res1.reportid = rep.id and res1.resulttypeid
= 59 and res1.del=0) join results ft_388 on (rep.id = ft_388.reportid
and ft_388.resulttypeid = 60 and ft_388.del=0) join sites s on
rep.site like s.sitecode and s.del = 0 and s.latitude is not null and
s.longitude is not null where rep.projectid = 41 and
ft_388.resultvalue in (400) and (((startdate is not null and enddate
is not null) and (startdate >= '2008-07-01' and enddate <=
'2008-09-30')) or ((startdate is not null and enddate is null) and
(startdate >= '2008-07-01' and startdate <= '2008-09-30')) or
((startdate is null and enddate is not null) and (enddate >=
'2008-07-01' and enddate <= '2008-09-30'))) and rep.del = 0 order
by outcome DESC</SrcSQL>
<GeometryType>wkbPoint</GeometryType>
<GeometryField encoding='PointFromColumns' x='longitude'
y='latitude'/>
</OGRVRTLayer>
</OGRVRTDataSource>"
CONNECTIONTYPE OGR
DATA "data"
GROUP "Sites"
METADATA
END
NAME "Changes"
SIZEUNITS PIXELS
STATUS DEFAULT
TOLERANCEUNITS PIXELS
TYPE POINT
UNITS METERS
CLASS
NAME "Positive"
EXPRESSION "positive"
LABEL
SIZE MEDIUM
TYPE BITMAP
BUFFER 0
COLOR 0 0 0
FORCE FALSE
MINDISTANCE -1
MINFEATURESIZE -1
OFFSET 0 0
PARTIALS FALSE
POSITION AUTO
END
METADATA
END
STYLE
ANGLE 360
COLOR 255 0 0
SIZE 10
SYMBOL 1
END
STYLE
ANGLE 360
COLOR 255 0 0
OUTLINECOLOR 255 255 255
SIZE 5
SYMBOL 1
END
END
CLASS
NAME "Negative"
EXPRESSION "negative"
LABEL
SIZE MEDIUM
TYPE BITMAP
BUFFER 0
COLOR 0 0 0
FORCE FALSE
MINDISTANCE -1
MINFEATURESIZE -1
OFFSET 0 0
PARTIALS FALSE
POSITION AUTO
END
METADATA
END
STYLE
ANGLE 360
COLOR 0 0 255
SIZE 10
SYMBOL 1
END
STYLE
ANGLE 360
COLOR 0 0 255
OUTLINECOLOR 255 255 255
SIZE 5
SYMBOL 1
END
END
END
I would suggest a few steps to try and isolate this:
o See if you can reproduce it against other ODBC datasources.
o See if you can reproduce it with mapserv instead of php_mapscript
o See if you can reproduce it with just OGR and no MapServer.
If you can reproduce the problem in a form that I can run at my end
without
undue complexity, I'd be willing to dig in somewhat deeper.
I will try the steps you have outlined above - it's fairly torturous
process to get things changed on the client server, so it might take
some time, I will keep you updated.
cheers
Ben
--
Ben Madin
REMOTE INFORMATION
t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome WA 6725
[EMAIL PROTECTED]
Out here, it pays to
know...
_______________________________________________
mapserver-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/mapserver-users
