[mapserver-users] RE:mapserver + MS SQL

Ben Madin ben at remoteinformation.com.au
Fri Sep 26 16:07:44 EDT 2008


To follow up on this :

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

This was the problem.

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

I stumbled across an email reply from Frank W suggesting that the ogr  
link in mapserver didn't work properly for odbc in ogr 1.3.2, which is  
what was compiled into ms4w 1.5.5. Solution - try to upgrade.

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

This was straight-forward enough once the SQL was working.


On 26/09/2008, at 2:47 PM, mapserver-users-request at lists.osgeo.org  
wrote:

> From: Ben Madin <ben at remoteinformation.com.au>
> Date: 26 September 2008 12:16:46 PM
> To: mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] RE:mapserver + MS SQL
>
> 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.

-- 

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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080927/cff4ac7d/attachment.html


More information about the mapserver-users mailing list