[mapserver-users] RE:mapserver + MS SQL

[Ben Madin]

G'day all,

I have a similar problem to this, but slightly more basic, and  
slightly more outdated!

I have a MS SQL table with investigations in, and the latitude and  
longitude of said investigations. I wanted to be able to make a map,  
using OGR / ODBC to connect to MS SQL, and display the points classed  
by the level of investigation.

I created the OGR connection :

<OGRVRTDataSource>
     <OGRVRTLayer name='mmapdata'>
         <SrcDataSource>ODBC:mmap/private at mmap</SrcDataSource>
         <SrcSQL>select rep.id, rep.latitude as latitude,  
rep.longitude as longitude, upper(left(rlu1.lookupfull,1)) 
+substring(rlu1.lookupfull,2,len(rlu1.lookupfull)) as response from  
reports rep  join results res1 on (res1.reportid = rep.id and  
res1.resulttypeid = 17 and res1.del=0) join resultlookup rlu1 on  
(rlu1.resulttypesid = 17 and rlu1.id = res1.resultvalue)  where  
projectid = 30</SrcSQL>
         <GeometryType>wkbPoint</GeometryType>
         <GeometryField encoding='PointFromColumns' x='longitude'  
y='latitude'/>
     </OGRVRTLayer>
</OGRVRTDataSource>

and tested it :

C:\Documents and Settings\Administrator>C:\ms4w\tools\gdal-ogr\ogrinfo  
C:\ms4w\Apache\htdocs\mmap\mapserver\xot1.ovf -ro -al
INFO: Open of `C:\ms4w\Apache\htdocs\mmap\mapserver\xot1.ovf'
using driver `VRT' successful.

Layer name: mmapdata
Geometry: Point
Feature Count: 1978
Extent: (0.000000, -43.173000) - (153.570000, 0.000000)
Layer SRS WKT:
(unknown)
id: Integer (10.0)
latitude: String (7.0)
longitude: String (7.0)
response: String (256.0)
OGRFeature(mmapdata):0
   id (Integer) = 8887
   latitude (String) = -34.922
   longitude (String) = 138.599
   response (String) = Specimens sent to the Laboratory
   POINT (138.59899999999999 -34.921999999999997 0)

OGRFeature(mmapdata):1
   id (Integer) = 10128
   latitude (String) = -18.280
   longitude (String) = 143.530
   response (String) = Specimens sent to the Laboratory
   POINT (143.53 -18.28 0)

OGRFeature(mmapdata):2
   id (Integer) = 10129
   latitude (String) = -16.920
   longitude (String) = 145.770
   response (String) = Investigation
   POINT (145.77000000000001 -16.920000000000002 0)


So far, so good.

Then I built it into a map file :

MAP
   EXTENT 110 -45 160 -8
   FONTSET "/ms4w/Apache/htdocs/mmap/mapserver//fonts/fontset.txt"
   IMAGECOLOR 203 230 255
   IMAGETYPE gif
   SYMBOLSET "/ms4w/Apache/htdocs/mmap/mapserver//symbols/colour.sym"
   SHAPEPATH "C:\ms4w\Apache\htdocs\mmap\mapserver"
   SIZE 600 600
   STATUS ON
   UNITS DD
   NAME "basemap"

   OUTPUTFORMAT
     NAME "gif"
     MIMETYPE "image/gif"
     DRIVER "gd/gif"
     EXTENSION "gif"
     IMAGEMODE "PC256"
     TRANSPARENT FALSE
   END

   LEGEND
     IMAGECOLOR 255 255 255
     KEYSIZE 20 8
     KEYSPACING 5 4
     LABEL
       ANGLE 0.000000
       ANTIALIAS TRUE
       FONT "lucida"
       MAXSIZE 256
       MINSIZE 4
       SIZE 7
       TYPE TRUETYPE
       BUFFER 2
       COLOR 0 0 0
       FORCE FALSE
       MINDISTANCE -1
       MINFEATURESIZE -1
       OFFSET 0 0
       OUTLINECOLOR 255 255 250
       PARTIALS TRUE
       POSITION UR
       SHADOWCOLOR 250 250 250
       SHADOWSIZE 2 2
     END
     POSITION LL
     STATUS EMBED
     TRANSPARENT TRUE
   END


   LAYER
     DATA "ausregion"
     GROUP "Australia"
       METADATA
       END
     NAME "ausregionlayer"
     SIZEUNITS DD
     STATUS DEFAULT
     TOLERANCEUNITS PIXELS
     TYPE POLYGON
     UNITS METERS
     CLASS
       METADATA
       END
       STYLE
         ANGLE 360
         COLOR 245 245 220
         SYMBOL 0
       END
     END
   END


   LAYER
     CONNECTION "<OGRVRTDataSource>
     <OGRVRTLayer name='mmapdata'>
         <SrcDataSource>ODBC:mmap/password at mmap</SrcDataSource>
         <SrcSQL>select rep.latitude as latitude, rep.longitude as  
longitude, upper(left(rlu1.lookupfull,1))+substring(rlu1.lookupfull, 
2,len(rlu1.lookupfull)) as response from reports rep  join results  
res1 on (res1.reportid = rep.id and res1.resulttypeid = 17 and  
res1.del=0) join resultlookup rlu1 on (rlu1.resulttypesid = 17 and  
rlu1.id = res1.resultvalue)  where projectid = 30</SrcSQL>
         <GeometryType>wkbPoint</GeometryType>
         <GeometryField encoding='PointFromColumns' x='longitude'  
y='latitude'/>
     </OGRVRTLayer>
</OGRVRTDataSource>"
     CONNECTIONTYPE OGR
     DATA "mmapdata"
CLASSITEM response
       METADATA
       END
     NAME ".xot1"
     SIZEUNITS PIXELS
     STATUS DEFAULT
     TOLERANCEUNITS PIXELS
     TYPE POINT
     UNITS METERS
     CLASS
       NAME "Field investigation"
       EXPRESSION "investigation"
       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 166 206 227
         SIZE 10
         SYMBOL 1
       END
     END

etc

and try :

C:\Documents and Settings\Administrator>c:\ms4w\tools\mapserv 
\shp2img.exe -m C:\ms4w\Apache\htdocs\nahis\mapserver\tmp 
\NAHISdf7c8a22c2b16b33730aa228fac405bb.map
  -l .xot1 -o C:\test.gif
scalefactor = 12

and I get the map with the borders etc, but no points (the legend show  
up OK). Even if I remove the classitem and expressions, I still don't  
get any points.

My two questions therefore are :

1. How can I find if I am truly getting no points at all, or they are  
not displaying.

2. How do I set up expressions to specify that the third column in my  
sql  statement (response) is the classitem and to class based on it.

The catch here is that to maintain compatibility with the rest of the  
system, I am using ms4w 1.5.5

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...