[gdal-dev] Ogr: ExecuteSQL with MSSQL and geometry column

[Chaitanya kumar CH]

Benjamin,

The mssql driver already does the conversion from wkb in the database to
the native ogr geometry format. You don't have to create a temporary wkb
geometry and then the ogr geometry.

You can simply do this:
[code]
Geometry memGeom = memFeature.GetGeometryRef()
[/code]

On Wed, Jul 18, 2012 at 6:31 PM, Benjamin <benjamin.lux at maxsea.fr> wrote:

> Sorry, I was not clear.
>
> I resume from the beginning.
>
> I work with a warper C# of Ogr.
> I have a table dbo.MY_TABLE with 3 fields (D, CHART_ID, WKB_GEOMETRY) in a
> SQL server Database (in SQL Azure exactly).
>
> *My aim is to work with a copy* (in a
> http://www.gdal.org/ogr/drv_memory.html Memory DataSource ) of a SQL
> statement result executed on a MSSQL DataSource.
>
> Actually I can work with usuals methods with this MSSQL DataSource.
> Note that I have not geometry_column table in my SQL Serveur DataBase, I
> use
> http://www.gdal.org/ogr/drv_mssqlspatial.html driver options .
>
> [code]
> string driverOptions =
> "Tables=dbo.MY_TABLE(WKB_GEOMETRY);GeometryFormat=wkb;"
> serverDataSource = Ogr.Open("MSSQL:" + connectionString + driverOptions),
> 0);
>
> serveurDataSource.GetLayerCount(); // It works fine.
> [/code]
>
> So I tried a call to
>
> http://www.gdal.org/ogr/classOGRDataSource.html#aa6acc228db6513784a56ce12334a8c33
> SQLExecute() .
> [code]
> string request = "*SELECT * FROM MY_TABLE WHERE CHART_ID = 'ID'*"
> Layer serverLayer = serverDataSource.ExecuteSQL(request, null, "");
> [/code]
>
> But unfortunately, I have not geometry field in the result.
> [code]
> serveurLayer.GetGeometryColumn();  //*return ""*
> serveurLayer.GetGeomType();          //*return wkbUnknown*
> [/code]
>
> But I have my 3 fields in serveurLayer !
> [code]
> serverLayer.GetLayerDefn().GetFieldCount();                //return 3
> serverLayer.GetLayerDefn().GetFieldDefn(0).GetName(); //return "D"
> serverLayer.GetLayerDefn().GetFieldDefn(1).GetName(); //return "CHART_ID"
> serverLayer.GetLayerDefn().GetFieldDefn(2).GetName(); //*return
> "WKB_GEOMETRY"*
> [/code]
>
> So I want to copy this layer in a Memory Layer to minimise acces to my SQL
> Server and work faster.
> [code]
> Driver memDriver = Ogr.GetDriverByName("Memory");
> DataSource memDataSource =
> memDriver.CreateDataSource(GetWorkPath("nomimprobale"), null);
> Layer memLayer = memDataSource.CopyLayer(serverLayer, layerName, null);
> [/code]
>
>
> Now,* I have to fetch my geometries*, so have thought to a trick like this
> :
> [code]
> byte[] memWkb =
>
> System.Text.Encoding.UTF8.GetBytes(memFeature.GetFieldAsString("WKB_GEOMETRY"));
> Geometry memGeom = Geometry.CreateFromWkb(memWkb);
> [/code]
> Because I can't use memFeature.GetGeometryRef()
> [code]
> memFeature.GetLayerCount();                                // return 39
> Feature memFeature = memLayer.GetNextFeature();  // don't return null
> memFeature.GetGeometryRef();                             // *return null*
> [/code]
>
> But I don't know if I have to use UTF8, ASCII, Unicode or other.
> If you know what encoding is use, or better an other trick to have a
> geometry in my memLayer or serverLayer I heard you with interest !
>
>
> Sorry for my spelling, English isn't my native tongue language.
>
> Benjamin
>
>
> Ps : link to the thread:
>
> http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html
>
> http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html
>
>
>
> Even Rouault wrote
> >
> > Selon Benjamin <benjamin.lux@>:
> >
> >> "You can access the geometry like with any other ogr feature."
> >>
> >
> > Hopefully, memFeature.GetGeometryRef() should do it !
> > _______________________________________________
> > gdal-dev mailing list
> > gdal-dev at .osgeo
> > http://lists.osgeo.org/mailman/listinfo/gdal-dev
> >
>
>
> --
> View this message in context:
> http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831p4989072.html
> Sent from the GDAL - Dev mailing list archive at Nabble.com.
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>



-- 
Best regards,
Chaitanya kumar CH.

+91-9494447584
17.2416N 80.1426E
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20120718/83e60ab1/attachment-0001.html>