Geometries in Oracle

Topics: Algorithms, Data Access, General Topics, SharpMap Project, SharpMap v0.9 / v1.x
Oct 17, 2011 at 6:37 PM

Hi again,

I have a question to better understand how works a spatial query in oracle. 

I have to return some geometries with a query, create a GeometryProvider Layer, then load the List<Geometry> result of my query in that layer.

My db is pretty big, but I created all the indexes needed by the query, the spatial index and ran that SDO_MIGRATE.TO_CURRENT function. 

My average query has to return something like 500 geometries, and following the examples, I structured the query like this:

select g.geometry_column.Get_WKB() from table g where Column1=Value1 

and in the code I act like this:
while(DataReader.Read())

     ListGeometries.Add(GeometryFromWKB((byte[])DataReader[0]);

 

The time for the operations is (for 500 geometries)

Read from database (without the GeometryFromWKB function): 4 sec

Read from database AND do the GeometryFromWKB: 9 sec

 

Now, what I don't understand is: why I have to convert the geometries first in WKB and then re-convert the data into geometries? Am I wrong?

Coordinator
Oct 18, 2011 at 6:33 AM

Because the internal representation of the oracle geometry object does not match that of sharpmap.
There are issues related to this.

If you want to avoid that, you need to write your own geometry converter. There is a patch that uses UDT.
It was developed using Oracle XE and never really tested. If it works for you (it may need some modification) we can apply it (or the modified version).

Hth FObermaier

Oct 19, 2011 at 7:07 AM

Ok I will try this and let you know!

Nov 21, 2011 at 8:17 AM
Edited Nov 21, 2011 at 8:17 AM

Here I am. I found a very interesting thing: http://code.google.com/p/tf-net/downloads/detail?name=NetSdoGeometry.zip&can=2&q=

NetSdoGeometry can read the geometry field (without that .Get_WKB() function) and do the conversion!

It works in .Net Framework 3.5-4.0 only, and with Oracle 11 client only... I tried this in an example project, and the performance are great, 8000-10000 geometries per second

Simply add SDOPOINT.cs, sdogeometry.cs, OracleCustomTypeBase.cs and OracleArrayTypeFactory.cs to the project. Here is an example:

string oracleConnection = "data source=<server>; user id=<user>; password=<password>";

using (Oracle.DataAccess.Client.OracleConnection cnn = new Oracle.DataAccess.Client.OracleConnection(oracleConnection))
{
    cnn.Open();
    string sql = "SELECT <Geometry Field>FROM <Geometry Table>";
    Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(sql, cnn);
    cmd.CommandType = System.Data.CommandType.Text;
    Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader();
    while(reader.Read())
    {
        if (!reader.IsDBNull(1))
       {
            sdogeometry geometry = reader["<Geometry Field>"] as sdogeometry;
       }
    }

}

Coordinator
Nov 21, 2011 at 9:20 AM

That is basically what the mentioned patch was all about :).

Glad that you found a way. Will you be so kind as to wrap it up and post a patchfile...

Cheers FObermaier

Nov 21, 2011 at 9:27 AM

I'm re-writing the Oracle.cs class, but I'm sorry I don't know how to do a .patch file :D

Coordinator
Nov 21, 2011 at 9:51 AM

That is fairly simple if you use TortoiseSVN as client to the source code: Just right-click on the folder you made your changes and choose create patch file.

If you don't use that, just post (or send me) all the files you changed.

Hth FObermaier

Nov 21, 2011 at 5:32 PM

These are the files:

http://www.megaupload.com/?d=44HLYQFK

Note that in Oracle.SdoToSharpMap (my function) I didn't implemented all geometry types

If you want explainations about the code or if you want some support in oracle queries / implementations ask me :)

Nov 23, 2011 at 10:38 AM

I'm sorry, I just realized you have already done the class for the conversion.. (OraReader.cs)  I'm re-uploading the correct files :) 

http://www.megaupload.com/?d=AXJG0N7H

 

Coordinator
Nov 23, 2011 at 1:43 PM

I hate megaupload.com. Could you please post files by either

  • raising an issue (http://sharpmap.codeplex.com/WorkItem/Create)
  • submitting the files in the patches sections (http://sharpmap.codeplex.com/SourceControl/list/patches/upload)

thanks

FObermaier

Nov 23, 2011 at 2:17 PM

Done :)

Coordinator
Nov 25, 2011 at 9:56 AM

I have no means to check that your patches are working. If you say they do, I'll commit :)

Nov 25, 2011 at 1:34 PM

I tried all functions I wrote and they are working for me