[mapserver-users] Re: MS-SQL / OGR Query Length Problem

[Ben Madin]

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/********@hello</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

ben at remoteinformation.com.au



							Out here, it pays to know...