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/[EMAIL PROTECTED]</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: [EMAIL PROTECTED]
>>> Ben Madin <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] wrote:
From: Ben Madin <[EMAIL PROTECTED]>
Date: 26 September 2008 12:16:46 PM
To: mapserver-users@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/[EMAIL PROTECTED]</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/[EMAIL PROTECTED]</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
[EMAIL PROTECTED]
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
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
