[gdal-dev] Ogr: ExecuteSQL with MSSQL and geometry column
Benjamin
benjamin.lux at maxsea.fr
Mon Jul 23 09:01:26 PDT 2012
Now, I am able to answer to me.
Correct me if I am wrong but :
With the MSSQL Driver ,when you execute a statment with SQLExecute() the
result is a table without geometry field.
Then the in the layer give by "SELECT * FROM my_table WHERE CHART_ID =
'my_id'" the geometry column is consider as a simple field like another.
In fact, you can see this field by .GetField(int i), and you can't see a
geometric field like that.
So to have a geometric field I have to use :
DataSource.GetLayerByName("my_table").SetAttributFilter("CHART_ID =
'my_id'")
I don't know if it is as efficient as a SELECT statment, but it work fine !
Thank to all your answers !
Best regards,
Benjamin.
Benjamin wrote
>
> 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-tp4988831p4990130.html
Sent from the GDAL - Dev mailing list archive at Nabble.com.
More information about the gdal-dev
mailing list