<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:13383347;
mso-list-type:hybrid;
mso-list-template-ids:2067012408 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1
{mso-list-id:347560819;
mso-list-type:hybrid;
mso-list-template-ids:-1018135820 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l1:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi, Tamas,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Thanks for getting back to me so quickly. It works (with a pair of follow-up questions)!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Here’s what I used:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> CONNECTIONTYPE OGR<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> CONNECTION 'MSSQL:server=xxx;database=Navigation;uid=yyy;pwd=zzz’<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> PROCESSING 'CLOSE_CONNECTION=DEFER'<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> DATA "select ShapeGeometry, State_Abbr from NAV_STATE<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> where ShapeGeometry.STIntersects(<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> geometry::STGeomFromText(<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> 'POLYGON((-126 22.2048396890436, -66 22.2048396890436,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> -66 51.1696997878088, -126 51.1696997878088,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> -126 22.2048396890436))',4269)) = 1"<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> LABELITEM 'State_Abbr'<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">1. Note that I omitted the “tables=…” as it appears that naming a specific table would interfere with connection pooling if I’m using many layers drawing data
from different tables. Do I understand the role of the “tables=…” incorrectly?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">2. I added the explicit spatial filtering (STIntersects…). Is there a better way to do this?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Thanks again,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">- Phil<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Tamas Szekeres [mailto:szekerest@gmail.com]
<br>
<b>Sent:</b> Sunday, February 26, 2012 12:25 PM<br>
<b>To:</b> Anzel, Phil - NRCS, Fort Collins, CO<br>
<b>Cc:</b> mapserver-users@lists.osgeo.org<br>
<b>Subject:</b> Re: [mapserver-users] OGR and MSSQL non-specific error<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Phil,<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">You should not specify the data section in the OGR layer definition unless you would define and sql select statement preceding with the 'where' keyword. The terms 'using unique' and 'using srid' are not meaningful for the ogr driver these
parameters are automatically retrieved from the geometry_columns metadata table. You should however specify the table name (and probably the geometry column name) in the connection string, something like:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"> CONNECTION ' MSSQL:server=xxx;database=;uid=xxx;pwd=xxx;tables=NAV_STATE'<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">or<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"> CONNECTION ' MSSQL:server=xxx;database=;uid=xxx;pwd=xxx;tables=NAV_STATE(ShapeGeometry)'<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">The difference in performance may be investigated by reading large number of shapes at a given scale. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">I'm also planning to improve the mapserver mssql plugin to read native SqlGeometries, but I'm now working on a QGIS mssql driver right now.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Best regards,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Tamas<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"> <o:p></o:p></p>
<div>
<p class="MsoNormal">2012/2/26 Anzel, Phil - NRCS, Fort Collins, CO <<a href="mailto:Phil.Anzel@ftc.usda.gov">Phil.Anzel@ftc.usda.gov</a>><o:p></o:p></p>
<p class="MsoNormal">All,<br>
<br>
I'm a bit confused: does MapServer 6.0.1 and OGR "officially" work with Sql Server 2008? I can see how use of the native SqlGeometry objects might be a bonus and would like to put it to the test.<br>
<br>
MSSQL access via the plugin works with this LAYER:<br>
LAYER<br>
NAME 'States' STATUS ON<br>
PROJECTION 'proj=latlong' 'ellps=GRS80' 'datum=NAD83' END<br>
CONNECTIONTYPE PLUGIN<br>
PLUGIN ...\msplugin_mssql2008.dll'<br>
CONNECTION 'server=xxx;database=;uid=xxx;pwd=xxx'<br>
PROCESSING 'CLOSE_CONNECTION=DEFER'<br>
DATA "ShapeGeometry from NAV_STATE as base_table using unique OBJECTID using SRID=4269"<br>
TYPE POLYGON<br>
CLASS STYLE OUTLINECOLOR '#e1dd00' WIDTH 2 END END<br>
END<br>
<br>
This variant, changed to use OGR, fails:<br>
LAYER<br>
NAME 'States' STATUS ON<br>
PROJECTION 'proj=latlong' 'ellps=GRS80' 'datum=NAD83' END<br>
CONNECTIONTYPE OGR<br>
CONNECTION 'MSSQL:server=xxx;database=;uid=xxx;pwd=xxx'<br>
PROCESSING 'CLOSE_CONNECTION=DEFER'<br>
DATA "ShapeGeometry from NAV_STATE as base_table using unique OBJECTID using SRID=4269"<br>
TYPE POLYGON<br>
CLASS STYLE OUTLINECOLOR '#e1dd00' WIDTH 2 END END<br>
END<br>
<br>
Using shp2img (from MapServer 6.0.1 distribution for Win32/VS2010 retrieved 2011-07-18) with "-all_debug 5" yields:<br>
msOGRFileOpen(): OGR error.<br>
GetLayer(ShapeGeometry from NAV_STATE as base_table using unique OBJECTID using SRID=4269)<br>
failed for OGR connection `MSSQL:server=xxx;database=xxx;uid=xxx;pwd=xxx'.<br>
There is no further detail as to the failure.<br>
<br>
By way of reference,<br>
1. In mapserver-users Digest, Vol 49, Issue 58, Tamas Szekeres states "BTW: With the current approach, connecting to mssql via OGR provides better performance than using the mssql plugin."<br>
2. Tamas also indicates that OGR can talk to MSSQL in "[gdal-dev] OGR MS SQL Spatial driver has been added",
<a href="http://www.osgeo.org/pipermail/gdal-dev/2010-September/026055.html" target="_blank">
http://www.osgeo.org/pipermail/gdal-dev/2010-September/026055.html</a>.<br>
3. MapServer documentation at <a href="http://mapserver.org/input/vector/ogr.html" target="_blank">
http://mapserver.org/input/vector/ogr.html</a> does not list MSSQL as a data provider.<br>
4. MapServer documentation at <a href="http://mapserver.org/input/vector/mssql.html" target="_blank">
http://mapserver.org/input/vector/mssql.html</a> still shows use of the msplugin_mssql2008.dll plugin.<br>
<br>
I must also wonder: has a measurable improvement been demonstrated by eliminating the plugin and going to the direct OGR to MSSQL connection?<br>
<br>
Yours in confusion,<br>
<br>
- Phil Anzel<br>
Contractor for USDA/NRCS<br>
ITC Web Soil Survey Team Member<br>
<br>
This electronic message contains information generated by the USDA solely for the intended recipients. Any unauthorized interception of this message or the use or disclosure of the information it contains may violate the law and subject the violator to civil
or criminal penalties. If you believe you have received this message in error, please notify the sender and delete the email immediately.<br>
<br>
_______________________________________________<br>
mapserver-users mailing list<br>
<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a><o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</body>
</html>