[mapserver-users] RE:mapserver + MS SQL
Ben Madin
ben at remoteinformation.com.au
Fri Sep 26 13:07:44 PDT 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.htm>
More information about the MapServer-users
mailing list