[gdal-dev] Ogr: ExecuteSQL with MSSQL and geometry column
Benjamin LUX
Benjamin.lux at maxsea.fr
Wed Jul 18 08:22:03 PDT 2012
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 <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
>
--
Best regards,
Chaitanya kumar CH.
+91-9494447584 <tel:%2B91-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/47c1852c/attachment-0001.html>
More information about the gdal-dev
mailing list