[gdal-dev] Ogr: ExecuteSQL with MSSQL and geometry column
Benjamin
benjamin.lux at maxsea.fr
Fri Jul 20 05:34:02 PDT 2012
If you want I can remove the MemoryDriver, but it is still the same thing.
[code]
string request = "SELECT * FROM MY_TABLE WHERE CHART_ID = 'Z15F43AJ'";
Layer serverLayer = RclDataSource.ExecuteSQL(request, null, "");
int nbFeature = serverLayer.GetFeatureCount(1);
Feature serverFeature = serverLayer.GetNextFeature();
string[] namesColumnsArray = new string[3];
for (int i = 0; i < serverFeature.GetFieldCount(); i++)
namesColumnsArray[i] = serverFeature.GetFieldDefnRef(i).GetName();
Geometry serverGeometry = serverFeature.GetGeometryRef();
[/code]
Results:
nbFeature = 39
namesColumnsArray = { D , CHART_ID, WKB_GEOMETRY }
serverGeometry = null
For me the fact is that the result of the SQL request is a layer without
geometry field, just 3 usuals fields.
I suppose that is normal because if you ask for a request like :
"Select column_geo1, column_geo2
>From Table1, Table2"
Unless I am wrong, you can't have 2 geometrics fields in a layer.
For me this result is normal (but I am a beginner with ogr so maybe not).
Reminder : I just want fetch my geometry.
In any cases, thank for all you reply Chaitanya_ch.
Benjamin.
chaitanya_ch wrote
>
> 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@>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@]
>> *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@> wrote:****
>>
>>
> Benjamin wrote
>>
>>> memFeature.GetGeometryRef(); // return null
>>>
>>
>> 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 ?
>>
>>
> chaitanya_ch wrote
>> ****
>>>
>>> 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@> 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 .osgeo
>>> > http://lists.osgeo.org/mailman/listinfo/gdal-dev
>>> >
>>>
>>>
>>>
>>> _______________________________________________
>>> gdal-dev mailing list
>>> gdal-dev at .osgeo
>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev****
>>>
>>>
>> 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
>
> _______________________________________________
> 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-tp4988831p4989636.html
Sent from the GDAL - Dev mailing list archive at Nabble.com.
More information about the gdal-dev
mailing list