<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 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@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;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
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;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></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=FR link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Hi again </span><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>Chaitanya,</span><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>If you look my previous mail you can see the code.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>But in a compact way this is a copy/paste of my running code.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Note that RoleEnvironment.GetConfigurationSettingValue(“****”) just provide me parametrables strings (like argv[n]).<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>[code]<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>Ogr</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'>.RegisterAll();<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:blue;mso-fareast-language:EN-US'>string</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> option = <span style='color:#2B91AF'>String</span>.Format(<span style='color:#A31515'>"Tables=dbo.{0}(WKB_GEOMETRY);GeometryFormat=wkb;"</span>,<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> <span style='color:#2B91AF'>RoleEnvironment</span>.GetConfigurationSettingValue(<span style='color:#A31515'>"CatalogueRclAllName"</span>)<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> );<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'>RclDataSource = <span style='color:#2B91AF'>Ogr</span>.Open(<span style='color:#2B91AF'>String</span>.Format(<span style='color:#A31515'>"MSSQL:{0}{1}"</span>, <span style='color:#2B91AF'>RoleEnvironment</span>.GetConfigurationSettingValue(<span style='color:#A31515'>"DataBaseConnectionString"</span>), option), 0);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:blue;mso-fareast-language:EN-US'>string</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> request = <span style='color:#2B91AF'>String</span>.Format(<span style='color:#A31515'>"SELECT * FROM {0} WHERE CHART_ID = '{1}'"</span>,<o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt;text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>RoleEnvironment</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'>.GetConfigurationSettingValue(<span style='color:#A31515'>"CatalogueRclAllName"</span>),<o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt;text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#A31515;mso-fareast-language:EN-US'>"Z15F43AJ"</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>Layer</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> serverLayer = RclDataSource.ExecuteSQL(request, <span style='color:blue'>null</span>, <span style='color:#A31515'>""</span>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>Driver</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> memDriver = <span style='color:#2B91AF'>Ogr</span>.GetDriverByName(<span style='color:#A31515'>"Memory"</span>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>DataSource</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> memDataSource = memDriver.CreateDataSource(GetWorkPath(<span style='color:#A31515'>"nomimprobale"</span>), <span style='color:blue'>null</span>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>Layer</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> memLayer = memDataSource.CopyLayer(serverLayer, serverLayer.GetName(), <span style='color:blue'>null</span>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>Feature</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> memFeature = memLayer.GetNextFeature();<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:blue;mso-fareast-language:EN-US'>string</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'>[] namesColumnsArray = <span style='color:blue'>new</span> <span style='color:blue'>string</span>[5];<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:blue;mso-fareast-language:EN-US'>for</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> (<span style='color:blue'>int</span> i = 0; i < memFeature.GetFieldCount(); i++)<o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt;text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'>namesColumnsArray[i] = memFeature.GetFieldDefnRef(i).GetName();<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:#2B91AF;mso-fareast-language:EN-US'>Geometry</span><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;mso-fareast-language:EN-US'> memGeometry = memFeature.GetGeometryRef(); <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>[/code]<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Results :<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> namesColumnsArray = { D , CHART_ID, WKB_GEOMETRY }<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'> memGeometry = null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>I don’t see what I can add to this :/<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>If you want more details said me.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Link to the thread : </span><a href="http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html" target="_blank"><span lang=EN-US>http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html</span></a><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Best regards,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif"'>Benjamin<span style='color:#1F497D'><o:p></o:p></span></span></p><p class=MsoNormal><span lang=EN-US style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>De :</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> Chaitanya kumar CH [mailto:chaitanya.ch@gmail.com] <br><b>Envoyé :</b> mercredi 18 juillet 2012 16:53<br><b>À :</b> Benjamin LUX<br><b>Cc :</b> gdal dev<br><b>Objet :</b> Re: Ogr: ExecuteSQL with MSSQL and geometry column<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-bottom:12.0pt'>Hi Benjamin,<br><br>Can you provide some more details?<br>How did you try to retrieve the geometry?<o:p></o:p></p><div><p class=MsoNormal>On Wed, Jul 18, 2012 at 8:02 PM, <<a href="mailto:benjamin.lux@maxsea.fr" target="_blank">benjamin.lux@maxsea.fr</a>> wrote:<o:p></o:p></p><p class=MsoNormal><quote author='Benjamin'><br>memFeature.GetGeometryRef(); // return null<br></quote><br><br>Sorry, but no.<br>After a call to SQLExecute(), Ogr framwork doesn't recognize any geometry in my feature.<br>If you have a clue why that ?<br><br><quote author='chaitanya_ch'><o:p></o:p></p><div><div><p class=MsoNormal>Benjamin,<br><br>The mssql driver already does the conversion from wkb in the database to<br>the native ogr geometry format. You don't have to create a temporary wkb<br>geometry and then the ogr geometry.<br><br>You can simply do this:<br>[code]<br>Geometry memGeom = memFeature.GetGeometryRef()<br>[/code]<br><br>On Wed, Jul 18, 2012 at 6:31 PM, Benjamin <<a href="mailto:benjamin.lux@maxsea.fr">benjamin.lux@maxsea.fr</a>> wrote:<br><br>> Sorry, I was not clear.<br>><br>> I resume from the beginning.<br>><br>> I work with a warper C# of Ogr.<br>> I have a table dbo.MY_TABLE with 3 fields (D, CHART_ID, WKB_GEOMETRY) in a<br>> SQL server Database (in SQL Azure exactly).<br>><br>> *My aim is to work with a copy* (in a<br>> <a href="http://www.gdal.org/ogr/drv_memory.html" target="_blank">http://www.gdal.org/ogr/drv_memory.html</a> Memory DataSource ) of a SQL<br>> statement result executed on a MSSQL DataSource.<br>><br>> Actually I can work with usuals methods with this MSSQL DataSource.<br>> Note that I have not geometry_column table in my SQL Serveur DataBase, I<br>> use<br>> <a href="http://www.gdal.org/ogr/drv_mssqlspatial.html" target="_blank">http://www.gdal.org/ogr/drv_mssqlspatial.html</a> driver options .<br>><br>> [code]<br>> string driverOptions =<br>> "Tables=dbo.MY_TABLE(WKB_GEOMETRY);GeometryFormat=wkb;"<br>> serverDataSource = Ogr.Open("MSSQL:" + connectionString + driverOptions),<br>> 0);<br>><br>> serveurDataSource.GetLayerCount(); // It works fine.<br>> [/code]<br>><br>> So I tried a call to<br>><br>> <a href="http://www.gdal.org/ogr/classOGRDataSource.html#aa6acc228db6513784a56ce12334a8c33" target="_blank">http://www.gdal.org/ogr/classOGRDataSource.html#aa6acc228db6513784a56ce12334a8c33</a><br>> SQLExecute() .<br>> [code]<br>> string request = "*SELECT * FROM MY_TABLE WHERE CHART_ID = 'ID'*"<br>> Layer serverLayer = serverDataSource.ExecuteSQL(request, null, "");<br>> [/code]<br>><br>> But unfortunately, I have not geometry field in the result.<br>> [code]<br>> serveurLayer.GetGeometryColumn(); //*return ""*<br>> serveurLayer.GetGeomType(); //*return wkbUnknown*<br>> [/code]<br>><br>> But I have my 3 fields in serveurLayer !<br>> [code]<br>> serverLayer.GetLayerDefn().GetFieldCount(); //return 3<br>> serverLayer.GetLayerDefn().GetFieldDefn(0).GetName(); //return "D"<br>> serverLayer.GetLayerDefn().GetFieldDefn(1).GetName(); //return "CHART_ID"<br>> serverLayer.GetLayerDefn().GetFieldDefn(2).GetName(); //*return<br>> "WKB_GEOMETRY"*<br>> [/code]<br>><br>> So I want to copy this layer in a Memory Layer to minimise acces to my SQL<br>> Server and work faster.<br>> [code]<br>> Driver memDriver = Ogr.GetDriverByName("Memory");<br>> DataSource memDataSource =<br>> memDriver.CreateDataSource(GetWorkPath("nomimprobale"), null);<br>> Layer memLayer = memDataSource.CopyLayer(serverLayer, layerName, null);<br>> [/code]<br>><br>><br>> Now,* I have to fetch my geometries*, so have thought to a trick like this<br>> :<br>> [code]<br>> byte[] memWkb =<br>><br>> System.Text.Encoding.UTF8.GetBytes(memFeature.GetFieldAsString("WKB_GEOMETRY"));<br>> Geometry memGeom = Geometry.CreateFromWkb(memWkb);<br>> [/code]<br>> Because I can't use memFeature.GetGeometryRef()<br>> [code]<br>> memFeature.GetLayerCount(); // return 39<br>> Feature memFeature = memLayer.GetNextFeature(); // don't return null<br>> memFeature.GetGeometryRef(); // *return null*<br>> [/code]<br>><br>> But I don't know if I have to use UTF8, ASCII, Unicode or other.<br>> If you know what encoding is use, or better an other trick to have a<br>> geometry in my memLayer or serverLayer I heard you with interest !<br>><br>><br>> Sorry for my spelling, English isn't my native tongue language.<br>><br>> Benjamin<br>><br>><br>> Ps : link to the thread:<br>><br>> <a href="http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html" target="_blank">http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html</a><br>><br>> <a href="http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html" target="_blank">http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html</a><br>><br>><br>><br>> Even Rouault wrote<br>> ><br>> > Selon Benjamin <benjamin.lux@>:<br>> ><br>> >> "You can access the geometry like with any other ogr feature."<br>> >><br>> ><br>> > Hopefully, memFeature.GetGeometryRef() should do it !<br>> > _______________________________________________<br>> > gdal-dev mailing list<br>> > <a href="mailto:gdal-dev@.osgeo">gdal-dev@.osgeo</a><br>> > <a href="http://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">http://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>> ><br>><br>><br>> --<br>> View this message in context:<br>> <a href="http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831p4989072.html" target="_blank">http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831p4989072.html</a><br>> Sent from the GDAL - Dev mailing list archive at Nabble.com.<br>> _______________________________________________<br>> gdal-dev mailing list<br>> <a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>> <a href="http://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">http://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>><br><br><br><br>--<br>Best regards,<br>Chaitanya kumar CH.<br><br><a href="tel:%2B91-9494447584">+91-9494447584</a><br>17.2416N 80.1426E<br><br>_______________________________________________<br>gdal-dev mailing list<br><a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">http://lists.osgeo.org/mailman/listinfo/gdal-dev</a><o:p></o:p></p></div></div><p class=MsoNormal></quote><br>Quoted from:<br><a href="http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831p4989092.html" target="_blank">http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831p4989092.html</a><o:p></o:p></p></div><p class=MsoNormal><br><br clear=all><br>-- <br>Best regards,<br>Chaitanya kumar CH.<br><br>+91-9494447584<br>17.2416N 80.1426E<o:p></o:p></p></div></body></html>