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

[Chaitanya kumar CH]

Hi Benjamin,

Can you provide some more details?
How did you try to retrieve the geometry?

On Wed, Jul 18, 2012 at 8:02 PM, <benjamin.lux at maxsea.fr> wrote:

> <quote author='Benjamin'>
> memFeature.GetGeometryRef();          // return null
> </quote>
>
> Sorry, but no.
> After a call to SQLExecute(), Ogr framwork doesn't recognize any geometry
> in my feature.
> If you have a clue why that ?
>
> <quote author='chaitanya_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
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev
> </quote>
> Quoted from:
>
> http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831p4989092.html
>



-- 
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/24c017d9/attachment.html>