[mapserver-users] RE:mapserver + MS SQL

Robert Sanson SansonR at asurequality.com
Sun Sep 28 13:26:59 PDT 2008


Hi Ben
 
If you can set up a view in SQL-Server with just the fields you need, then it simplifies things. Set up the OGR datasource config file just specifying the primary key and lat/long fields eg:
 
<OGRVRTDataSource>
        <OGRVRTLayer name="apiaries">
        <SrcDataSource>ODBC:user/passwd at APIARY</SrcDataSource>
        <SrcLayer>vw_ogr_apiary_layer</SrcLayer>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>EPSG:27200</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="x_nzmg" y="y_nzmg"/>
        <FID>apiary_id</FID>
        </OGRVRTLayer>
</OGRVRTDataSource>
 
In your Mapserver file, the layer definition:
 
 LAYER
  NAME "apiaries"
  TYPE POINT
  UNITS METERS
  CONNECTION "./apiaries.ovf"
  CONNECTIONTYPE OGR
  DATA "apiaries"
  FILTER "WHERE apiary_id = '%apiaryid%'"
  STATUS ON
...
 
I pass an apiaryid is as a filter on my WFS call to this layer. Otherwise miss out the FILTER statement.
 
Kind regards,
 
Robert Sanson
 
Robert Sanson, BVSc, MACVSc, PhD
Geospatial Services
AsureQuality Limited
PO Box 585, Palmerston North
NEW ZEALAND

Phone: +64 6 351-7990
Fax: +64 6 351-7919
Mobile: 021 448-472
E-mail: sansonr at asurequality.com 

>>> Ben Madin <ben at remoteinformation.com.au> 27/09/2008 8:07 a.m. >>>
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...





Click here ( https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg== ) to report this email as spam.
------------------------------------------------------------------
The contents of this email are confidential to AsureQuality. If you have received this communication in error please notify the sender immediately and delete the message and any attachments. The opinions expressed in this email are not necessarily those of AsureQuality. This message has been scanned for known viruses before delivery. AsureQuality supports the Unsolicited Electronic Messages Act 2007. If you do not wish to receive similar communications in future, please notify the sender of this message.
------------------------------------------------------------------


This message has been scanned for malware by SurfControl plc. www.surfcontrol.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080929/2017a7d8/attachment.htm>


More information about the MapServer-users mailing list