<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-15">
<META content="MSHTML 6.00.2900.3243" name=GENERATOR></HEAD>
<BODY defanghtml_style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
<DIV>Hi Ben</DIV>
<DIV> </DIV>
<DIV>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:</DIV>
<DIV> </DIV>
<DIV><OGRVRTDataSource><BR> <OGRVRTLayer name="apiaries"><BR> <SrcDataSource>ODBC:user/passwd@APIARY</SrcDataSource><BR> <SrcLayer>vw_ogr_apiary_layer</SrcLayer><BR> <GeometryType>wkbPoint</GeometryType><BR> <LayerSRS>EPSG:27200</LayerSRS><BR> <GeometryField encoding="PointFromColumns" x="x_nzmg" y="y_nzmg"/><BR> <FID>apiary_id</FID><BR> </OGRVRTLayer><BR></OGRVRTDataSource></DIV>
<DIV> </DIV>
<DIV>In your Mapserver file, the layer definition:</DIV>
<DIV> </DIV>
<DIV> LAYER<BR> NAME "apiaries"<BR> TYPE POINT<BR> UNITS METERS<BR> CONNECTION "./apiaries.ovf"<BR> CONNECTIONTYPE OGR<BR> DATA "apiaries"<BR> FILTER "WHERE apiary_id = '%apiaryid%'"<BR> STATUS ON</DIV>
<DIV>...</DIV>
<DIV> </DIV>
<DIV>I pass an apiaryid is as a filter on my WFS call to this layer. Otherwise miss out the FILTER statement.</DIV>
<DIV> </DIV>
<DIV>Kind regards,</DIV>
<DIV> </DIV>
<DIV>Robert Sanson</DIV>
<DIV> </DIV>
<DIV>Robert Sanson, BVSc, MACVSc, PhD<BR>Geospatial Services<BR>AsureQuality Limited<BR>PO Box 585, Palmerston North<BR>NEW ZEALAND<BR><BR>Phone: +64 6 351-7990<BR>Fax: +64 6 351-7919<BR>Mobile: 021 448-472<BR>E-mail: sansonr@asurequality.com<BR><BR>>>> Ben Madin <ben@remoteinformation.com.au> 27/09/2008 8:07 a.m. >>><BR>To follow up on this :</DIV>
<DIV><BR></DIV>
<DIV>
<BLOCKQUOTE type="cite"><DEFANGHTML_SPAN class=Apple-style-span style="webkit-text-stroke-width: -1">The catch here is that to maintain compatibility with the rest of the system, I am using ms4w 1.5.5</DEFANGHTML_SPAN></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>This was the problem.</DIV><BR>
<BLOCKQUOTE type="cite"><DEFANGHTML_SPAN class=Apple-style-span style="webkit-text-stroke-width: -1">1. How can I find if I am truly getting no points at all, or they are not displaying.</DEFANGHTML_SPAN></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>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.</DIV>
<DIV><BR></DIV>
<BLOCKQUOTE type="cite">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.</BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>This was straight-forward enough once the SQL was working.</DIV>
<DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>On 26/09/2008, at 2:47 PM, <A href="mailto:mapserver-users-request@lists.osgeo.org">mapserver-users-request@lists.osgeo.org</A> wrote:</DIV><BR class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px;"><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'"><B>From: </B></DEFANGHTML_SPAN><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'">Ben Madin <<A href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</A>><BR></DEFANGHTML_SPAN></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px;"><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'"><B>Date: </B></DEFANGHTML_SPAN><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'">26 September 2008 12:16:46 PM<BR></DEFANGHTML_SPAN></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px;"><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'"><B>To: </B></DEFANGHTML_SPAN><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'"><A href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</A><BR></DEFANGHTML_SPAN></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px;"><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'"><B>Subject: </B></DEFANGHTML_SPAN><DEFANGHTML_SPAN style="FONT-SIZE: medium; FONT-FAMILY: 'Helvetica'"><B>Re: [mapserver-users] RE:mapserver + MS SQL</B><BR></DEFANGHTML_SPAN></DIV><BR>G'day all,<BR><BR>I have a similar problem to this, but slightly more basic, and slightly more outdated!<BR><BR>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.<BR><BR>I created the OGR connection :<BR><BR><OGRVRTDataSource><BR> <OGRVRTLayer name='mmapdata'><BR> <SrcDataSource>ODBC:mmap/private@mmap</SrcDataSource><BR> <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><BR> <GeometryType>wkbPoint</GeometryType><BR> <GeometryField encoding='PointFromColumns' x='longitude' y='latitude'/><BR> </OGRVRTLayer><BR></OGRVRTDataSource><BR><BR>and tested it :<BR><BR>C:\Documents and Settings\Administrator>C:\ms4w\tools\gdal-ogr\ogrinfo C:\ms4w\Apache\htdocs\mmap\mapserver\xot1.ovf -ro -al<BR>INFO: Open of `C:\ms4w\Apache\htdocs\mmap\mapserver\xot1.ovf'<BR>using driver `VRT' successful.<BR><BR>Layer name: mmapdata<BR>Geometry: Point<BR>Feature Count: 1978<BR>Extent: (0.000000, -43.173000) - (153.570000, 0.000000)<BR>Layer SRS WKT:<BR>(unknown)<BR>id: Integer (10.0)<BR>latitude: String (7.0)<BR>longitude: String (7.0)<BR>response: String (256.0)<BR>OGRFeature(mmapdata):0<BR> id (Integer) = 8887<BR> latitude (String) = -34.922<BR> longitude (String) = 138.599<BR> response (String) = Specimens sent to the Laboratory<BR> POINT (138.59899999999999 -34.921999999999997 0)<BR><BR>OGRFeature(mmapdata):1<BR> id (Integer) = 10128<BR> latitude (String) = -18.280<BR> longitude (String) = 143.530<BR> response (String) = Specimens sent to the Laboratory<BR> POINT (143.53 -18.28 0)<BR><BR>OGRFeature(mmapdata):2<BR> id (Integer) = 10129<BR> latitude (String) = -16.920<BR> longitude (String) = 145.770<BR> response (String) = Investigation<BR> POINT (145.77000000000001 -16.920000000000002 0)<BR><BR><BR>So far, so good.<BR><BR>Then I built it into a map file :<BR><BR>MAP<BR> EXTENT 110 -45 160 -8<BR> FONTSET "/ms4w/Apache/htdocs/mmap/mapserver//fonts/fontset.txt"<BR> IMAGECOLOR 203 230 255<BR> IMAGETYPE gif<BR> SYMBOLSET "/ms4w/Apache/htdocs/mmap/mapserver//symbols/colour.sym"<BR> SHAPEPATH "C:\ms4w\Apache\htdocs\mmap\mapserver"<BR> SIZE 600 600<BR> STATUS ON<BR> UNITS DD<BR> NAME "basemap"<BR><BR> OUTPUTFORMAT<BR> NAME "gif"<BR> MIMETYPE "image/gif"<BR> DRIVER "gd/gif"<BR> EXTENSION "gif"<BR> IMAGEMODE "PC256"<BR> TRANSPARENT FALSE<BR> END<BR><BR> LEGEND<BR> IMAGECOLOR 255 255 255<BR> KEYSIZE 20 8<BR> KEYSPACING 5 4<BR> LABEL<BR> ANGLE 0.000000<BR> ANTIALIAS TRUE<BR> FONT "lucida"<BR> MAXSIZE 256<BR> MINSIZE 4<BR> SIZE 7<BR> TYPE TRUETYPE<BR> BUFFER 2<BR> COLOR 0 0 0<BR> FORCE FALSE<BR> MINDISTANCE -1<BR> MINFEATURESIZE -1<BR> OFFSET 0 0<BR> OUTLINECOLOR 255 255 250<BR> PARTIALS TRUE<BR> POSITION UR<BR> SHADOWCOLOR 250 250 250<BR> SHADOWSIZE 2 2<BR> END<BR> POSITION LL<BR> STATUS EMBED<BR> TRANSPARENT TRUE<BR> END<BR><BR><BR> LAYER<BR> DATA "ausregion"<BR> GROUP "Australia"<BR> METADATA<BR> END<BR> NAME "ausregionlayer"<BR> SIZEUNITS DD<BR> STATUS DEFAULT<BR> TOLERANCEUNITS PIXELS<BR> TYPE POLYGON<BR> UNITS METERS<BR> CLASS<BR> METADATA<BR> END<BR> STYLE<BR> ANGLE 360<BR> COLOR 245 245 220<BR> SYMBOL 0<BR> END<BR> END<BR> END<BR><BR><BR> LAYER<BR> CONNECTION "<OGRVRTDataSource><BR> <OGRVRTLayer name='mmapdata'><BR> <SrcDataSource>ODBC:mmap/password@mmap</SrcDataSource><BR> <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><BR> <GeometryType>wkbPoint</GeometryType><BR> <GeometryField encoding='PointFromColumns' x='longitude' y='latitude'/><BR> </OGRVRTLayer><BR></OGRVRTDataSource>"<BR> CONNECTIONTYPE OGR<BR> DATA "mmapdata"<BR>CLASSITEM response<BR> METADATA<BR> END<BR> NAME ".xot1"<BR> SIZEUNITS PIXELS<BR> STATUS DEFAULT<BR> TOLERANCEUNITS PIXELS<BR> TYPE POINT<BR> UNITS METERS<BR> CLASS<BR> NAME "Field investigation"<BR> EXPRESSION "investigation"<BR> LABEL<BR> SIZE MEDIUM<BR> TYPE BITMAP<BR> BUFFER 0<BR> COLOR 0 0 0<BR> FORCE FALSE<BR> MINDISTANCE -1<BR> MINFEATURESIZE -1<BR> OFFSET 0 0<BR> PARTIALS FALSE<BR> POSITION AUTO<BR> END<BR> METADATA<BR> END<BR> STYLE<BR> ANGLE 360<BR> COLOR 166 206 227<BR> SIZE 10<BR> SYMBOL 1<BR> END<BR> END<BR><BR>etc<BR><BR>and try :<BR><BR>C:\Documents and Settings\Administrator>c:\ms4w\tools\mapserv\shp2img.exe -m C:\ms4w\Apache\htdocs\nahis\mapserver\tmp\NAHISdf7c8a22c2b16b33730aa228fac405bb.map<BR>-l .xot1 -o C:\test.gif<BR>scalefactor = 12<BR><BR>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.<BR></BLOCKQUOTE></DIV><BR>
<DIV defanghtml_apple-content-edited="true"><DEFANGHTML_SPAN class=Apple-style-span style="WORD-SPACING: 0px; FONT: 12px Helvetica; TEXT-TRANSFORM: none; COLOR: rgb(0,0,0); TEXT-INDENT: 0px; WHITE-SPACE: normal; LETTER-SPACING: normal; BORDER-COLLAPSE: separate; border-spacing: 0px 0px; khtml-text-decorations-in-effect: none; apple-text-size-adjust: auto; orphans: 2; widows: 2">
<DIV defanghtml_style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">-- </DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><BR></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Ben Madin</DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">REMOTE INFORMATION</DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><BR></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">t : +61 8 9192 5455</DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">f : +61 8 9192 5535</DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">m : 0448 887 220</DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Broome WA 6725</DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><BR></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><A href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</A></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><BR></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><BR></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><BR></DIV>
<DIV defanghtml_style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><DEFANGHTML_SPAN class=Apple-tab-span style="WHITE-SPACE: pre"><DEFANGHTML_SPAN class=Apple-style-span style="WHITE-SPACE: pre"></DEFANGHTML_SPAN></DEFANGHTML_SPAN>Out here, it pays to know...</DIV></DIV><BR class=Apple-interchange-newline></DEFANGHTML_SPAN></DIV><BR></DIV><BR><BR><FONT style="BACKGROUND-COLOR: #ffffff">
<P align=center><FONT style="BACKGROUND-COLOR: #ffffff">Click <A href="https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg==">here</A> to report this email as spam.</FONT></P></FONT><br><br><table bgcolor=white style="color:black"><tr><td><br>------------------------------------------------------------------<br>
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.<br>
------------------------------------------------------------------</td></tr></table><br><br>
<P align=center><FONT style="BACKGROUND-COLOR: #ffffff">This message has been scanned for malware by SurfControl plc. </FONT><A href="http://www.surfcontrol.com/"><FONT style="BACKGROUND-COLOR: #ffffff" color=#000000>www.surfcontrol.com</FONT></A></P>
</body></HTML>