<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style>
<!--
@font-face
{font-family:Calibri}
@font-face
{font-family:Tahoma}
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
{color:blue;
text-decoration:underline}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline}
span.EmailStyle17
{font-family:"Calibri","sans-serif";
color:#1F497D}
.MsoChpDefault
{}
@page WordSection1
{margin:1.0in 1.0in 1.0in 1.0in}
div.WordSection1
{}
-->
</style>
</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,</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D">I believe there is at least one bug in the OGR code: the SRID is not properly retrieved from the underlying Sql Server 2008 database or its value is ignored,
and therefore zero records are returned.</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D">Consider this Layer definition:</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> LAYER</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> NAME 'States' STATUS ON</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> PROJECTION 'proj=latlong' 'ellps=GRS80' 'datum=NAD83' END</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> CONNECTIONTYPE OGR</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;tables=NAV_STATE(ShapeGeometry)'</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> PROCESSING 'CLOSE_CONNECTION=DEFER'</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> DATA 'NAV_STATE'</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> TYPE POLYGON</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> CLASS</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> STYLE</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> OUTLINECOLOR '#e1dd00'</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> WIDTH 2</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> END</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> END</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> END</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D">The ShapeGeometry has a non-zero SRID, as evidenced by a query executed against the database via Sql Server Management Studio (SSMS):</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> select top 1 ShapeGeometry.STSrid from dbo.NAV_STATE
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> -- that returns 4269</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D">Using SSMS’ Activity Monitor, executing SHP2IMG with a mapfile containing the above LAYER causes the following query to be issued to Sql Server:</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> select
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [OBJECTID], [ShapeGeometry], [AREA],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [STATE_NAME], [STATE_FIPS], [SUB_REGION], [STATE_ABBR],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [POP1990], [POP1997], [POP90_SQMI], [HOUSEHOLDS], [MALES],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [FEMALES], [WHITE], [BLACK], [AMERI_ES], [ASIAN_PI], [OTHER],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [HISPANIC], [AGE_UNDER5], [AGE_5_17], [AGE_18_29], [AGE_30_49],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [AGE_50_64], [AGE_65_UP], [NEVERMARRY], [MARRIED], [SEPARATED],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [WIDOWED], [DIVORCED], [HSEHLD_1_M], [HSEHLD_1_F], [MARHH_CHD],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [MARHH_NO_C], [MHH_CHILD], [FHH_CHILD], [HSE_UNITS], [VACANT],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [OWNER_OCC], [RENTER_OCC], [MEDIAN_VAL], [MEDIANRENT], [UNITS_1DET],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [UNITS_1ATT], [UNITS2], [UNITS3_9], [UNITS10_49], [UNITS50_UP],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [MOBILEHOME], [NO_FARMS87], [AVG_SIZE87], [CROP_ACR87],
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> [AVG_SALE87]
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> from dbo.NAV_STATE
</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> where [ShapeGeometry].STIntersects(</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> geometry::STGeomFromText(</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> 'POLYGON((-126 22.1993540334789,</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> -66 22.1993540334789,</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> -66 51.1734146762311,</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> -126 51.1734146762311,-126 22.1993540334789))',0)) = 1</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D">Note that the STIntersects function is given an SRID argument of 0. This results in a return of zero records.</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D">I also note that all columns are returned. That is unfortunate, as that could potentially represent quite a bit of network traffic,</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D">- Phil</span></p>
<p class="MsoNormal"><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </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> Monday, February 27, 2012 2:59 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</span></p>
</div>
<p class="MsoNormal"> </p>
<p class="MsoNormal" style="margin-bottom:12.0pt"> </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>></p>
<div>
<div>
<p class="MsoNormal" style=""> </p>
<p class="MsoNormal" style=""><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?</span></p>
<p class="MsoNormal" style=""><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
</div>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Yes, it may be considered as being a different connection if the tables section is used.</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<blockquote style="border:none; border-left:solid #CCCCCC 1.0pt; padding:0in 0in 0in 6.0pt; margin-left:4.8pt; margin-right:0in">
<div>
<div>
<p class="MsoNormal" style=""><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?</span></p>
<p class="MsoNormal" style=""><span style="font-size:11.0pt; font-family:"Calibri","sans-serif"; color:#1F497D"> </span></p>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">It seems to be a bit complicated this way. I think you should try simply setting the layer name in the DATA section. In this case the spatial filter is added automatically by the MapServer OGR driver.</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Best regards,</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
<div>
<p class="MsoNormal">Tamas</p>
</div>
<div>
<p class="MsoNormal"> </p>
</div>
</div>
</div>
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.
</body>
</html>