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

Benjamin benjamin.lux at maxsea.fr
Wed Jul 18 06:01:04 PDT 2012


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.


More information about the gdal-dev mailing list