[gdal-dev] Ogr: ExecuteSQL with MSSQL and geometry column
Chaitanya kumar CH
chaitanya.ch at gmail.com
Wed Jul 18 09:50:39 PDT 2012
Benjamin,
First thing: You don't need to use a memory datasource here. OGR doesn't
make round trips to the database server every time you run
GetNextFeature(). Do that first. I'm sure it will make it less complicated.
I don't understand why you are getting the null. Hopefully we can figure
that out after you simplify your code by removing the memory driver stuff.
On Wed, Jul 18, 2012 at 8:52 PM, Benjamin LUX <Benjamin.lux at maxsea.fr>wrote:
> Hi again Chaitanya,****
>
> ** **
>
> If you look my previous mail you can see the code.****
>
> But in a compact way this is a copy/paste of my running code.****
>
> Note that RoleEnvironment.GetConfigurationSettingValue(“****”) just
> provide me parametrables strings (like argv[n]).****
>
> [code]****
>
> Ogr.RegisterAll();****
>
> string option = String.Format(
> "Tables=dbo.{0}(WKB_GEOMETRY);GeometryFormat=wkb;",****
>
> RoleEnvironment.GetConfigurationSettingValue(
> "CatalogueRclAllName")****
>
> );****
>
> RclDataSource = Ogr.Open(String.Format("MSSQL:{0}{1}", RoleEnvironment
> .GetConfigurationSettingValue("DataBaseConnectionString"), option), 0);***
> *
>
> string request = String.Format("SELECT * FROM {0} WHERE CHART_ID = '{1}'"
> ,****
>
> RoleEnvironment.GetConfigurationSettingValue("CatalogueRclAllName"),****
>
> "Z15F43AJ");****
>
> ** **
>
> Layer serverLayer = RclDataSource.ExecuteSQL(request, null, "");****
>
> ** **
>
> Driver memDriver = Ogr.GetDriverByName("Memory");****
>
> DataSource memDataSource = memDriver.CreateDataSource(GetWorkPath(
> "nomimprobale"), null);****
>
> Layer memLayer = memDataSource.CopyLayer(serverLayer,
> serverLayer.GetName(), null);****
>
> Feature memFeature = memLayer.GetNextFeature();****
>
> string[] namesColumnsArray = new string[5];****
>
> for (int i = 0; i < memFeature.GetFieldCount(); i++)****
>
> namesColumnsArray[i] = memFeature.GetFieldDefnRef(i).GetName();****
>
> Geometry memGeometry = memFeature.GetGeometryRef(); ****
>
> [/code]****
>
> ** **
>
> Results :****
>
> namesColumnsArray = { D , CHART_ID, WKB_GEOMETRY }****
>
> memGeometry = null****
>
> ** **
>
> I don’t see what I can add to this :/****
>
> If you want more details said me.****
>
> ** **
>
> Link to the thread :
> http://osgeo-org.1560.n6.nabble.com/gdal-dev-Ogr-ExecuteSQL-with-MSSQL-and-geometry-column-tp4988831.html
> ****
>
> ** **
>
> Best regards,****
>
> Benjamin****
>
> ** **
>
> *De :* Chaitanya kumar CH [mailto:chaitanya.ch at gmail.com]
> *Envoyé :* mercredi 18 juillet 2012 16:53
> *À :* Benjamin LUX
> *Cc :* gdal dev
> *Objet :* Re: Ogr: ExecuteSQL with MSSQL and geometry column****
>
> ** **
>
> 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
> >
>
>
>
> _______________________________________________
> 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/0fbe2a00/attachment-0001.html>
More information about the gdal-dev
mailing list