Hi,

I have a component that uses the OCI library to do spatial index
queries. The component is badly written and is based on examples. To
state it shortly, our company has very little experience with oracle
and OCI, and the maintenance on the component is a mess. Is it possible
to rewrite these queries using some flavour of SQL?

If I can do this, I can connect directly to oracle using some ODBC
driver and can write my queries in a more maintainable language (like
c#) with normal sql techniques.

I have oracle 8i.

Thanks,
Pieter Breed

Pieter Breed schrieb:
If you look through your code very carefully, you'll probably come
across a bunch of sprintf calls that build the selects and inserts
and so on.

things are more indirect than ODBC.

it too now) but use a more modern driver. Oracle offers the OLE DB and ADO
ODP.NET) driver and if you just want small scripts, OO4O is a nice COM
interface for the OLE DB driver, usable from VBScript and JScript.

But another thing is that oci still offers some capabilities, the
other APIs don't. Especially the direct path load API. If your legacy
app uses that, for instance for non-spatial data, you may lose some
speed.

Also, since you appear not to have much experience in oracle programming,
here are the usual performance hints:
- for loading data use array binding (factor 10 plus much
decreased load on the database, definitely a must)
- for spatial data, disable indexes during load (factor 2-3, may
not be an option for you)
That way you *may* reach the speed of the original application.
Also, seriously consider sql*loader instead of a homegrown solution.

that difficult.

Also, in case you run any benchmarks with OLE DB and ADO with regards
to spatial object creation and initialization in the bind arrays I would
be very interested in any results.

In a few months we will probably move away from sql*loader because we
want to use temporary tables for our data. We are not sure which
API is better for this. I *do* know that OO4O is not really fast when
it comes to objects and I'm curious whether this is purely COM overhead
and also if the ODB.NET uses the .NET memory management. That *might*
offset the speed disadvantage of C#'s managed code.


Lots of Greetings!
Volker

Hi Volker,

Thanks for your response. My scenario is like this: We do not control
the database at all. I can say that is part of a legacy system, but
that is not the whole story either. We provide an application that has
to make use of this database though. It was written by an amalgamation
of developers that did not really understand C++ or oracle, but knew
enough to get something that appears to be working. Now that the hard
problems are cropping up (memory leaks and the like) I am tearing my
hair out from frustration.

I saw from the online docs that you can query the "normal" columns from
the database, and I've seen the sql. One of them might look like this:

SELECT clast_id, geometry FROM map_clasts T1 WHERE MINE = 'MPONENG
MINE' and SDO_RELATE(GEOMETRY,
mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_i nfo_array(1,1003,3),mdsys.sdo_ordinate_array(-100,-100,100,100)),'mask=ANYINTERACT
querytype=WINDOW') = 'TRUE';

pretty standard stuff, actually.

My problem here is that I want to get to the geometry information, like
the types, and the ordinates and the like. So what my question should
really have been is; Is there a way to query the information that is
contained in the geometry type so that the results come in a table?

Regards,
Pieter

pieter schrieb:


'Create the OO4O component
set orasession=createobject("OracleInProcServer.XOraSe ssion")
'log on
set Usersession=orasession.opendatabase("mydatabase"," myusername/mypassword",0)
Usersession.Autocommit=false 'something to keep in mind for dml stuff

'select data
set Rows=usersession.createdynaset("select mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_i nfo_array(1,1003,3),mdsys.sdo_ordinate_array(-100,-100,100,100)) my_geometry from dual",0)
'process result
while not rows.eof
set Geom=Rows("my_geometry").Value
msgBox(Geom.sdo_gtype)
set InfoArray=Geom.sdo_elem_info 'Yes, that step is necessary for nested objects.
msgBox(InfoArray(1)) 'And oracle collections start with 1!
Rows.MoveNext
wend
Rows.Close

'log off
usersession.Close
'clean up
set orasession = nothing

Those collections probably have more methods and I suspect that ADO
access data differently, but this should give you a rough idea what
to expect.


Lots of Greetings!
Volker

Thanks Volker,

I will try to get a small working example running (in vb6 or something
similar), and then do the full rewrite if I am confident enough

Regards,
Pieter

Just to be complete, this is what I came up with. It is VB6 code, and
it works

Volker, it is mostly based on what you said. As you might see, I get
the sdo_elem_info collection, but what does these values mean? One of
my results is (1, 2, 1)... Anyway. Thanks for your help

Pieter

Private Sub Command_Click()
Log.Clear
Set mGeometries = New Collector

' connect to oracle
Dim oraSession As New OracleInProcServer.OraSessionClass
Dim userSession As OracleInProcServer.OraDatabase
Set userSession = oraSession.OpenDatabase("aubis", "aubis/aubis",
0)
userSession.AutoCommit = False
'MsgBox "Connected: " & vbNewLine _
' & userSession.Connect & "@" & userSession.DatabaseName _
' & vbNewLine & "0040VER=" & oraSession.OipVersionNumber _
' & vbNewLine & "Oracle Version: " & vbNewLine &
userSession.RDBMSVersion


' do the query
Dim dynaSet As OracleInProcServer.OraDynaset
Set dynaSet = userSession.CreateDynaset("SELECT clast_id, geometry
FROM map_clasts", 0&)


' vars
Dim result As String
Dim counter As Integer

Dim ob As OracleInProcServer.OraObject
Dim info As OracleInProcServer.OraCollection
Dim Oords As OracleInProcServer.OraCollection

Dim theGeometry As geometry
Dim theCoordinate As coordinate


Do While (dynaSet.EOF = False)
'get at the geometry
Set ob = dynaSet.Fields("geometry").value
Set info = ob.sdo_elem_info
Set Oords = ob.sdo_ordinates

' make the geometry
Set theGeometry =
mGeometries.AddNewGeometry(CLng(dynaSet("clast_id" )))

' populate its ordinates
For counter = 1 To Oords.Size Step 3
Set theCoordinate = New coordinate
theCoordinate.X = Oords(counter)
theCoordinate.Y = Oords(counter + 1)
theCoordinate.Z = Oords(counter + 2)

theGeometry.AddCoordinate theCoordinate
Next counter

' make result string
result = vbNullString
'result = result & ", " & dynaSet.Fields("geometry")

' add to listbox
Log.AddItem result

'move cursor one on
dynaSet.MoveNext
Loop

' report success
MsgBox "Received " & Log.ListCount & " result rows"

' close all of this stuff
dynaSet.Close
userSession.Close
Set oraSession = Nothing

End Sub

pieter schrieb:
type then my strong advise is to spend a day or two reading this up
in the documentation.
The elem_info array basically tells oracle what to do with the coordinates,
like connecting them with straight lines or arcs or where an outline
ends and a hole starts, all sorts of things basically.
With an otn account (free of charge) you can read
http://download-east.oracle.com/docs...b14255/toc.htm .
Or get at the pdf and print out the relevant sections.
If this is a longer term project, try to get http://tinyurl.com/mvbvb .

Working with spatial geometries isn't trivial.

On the other hand, most users are content with creating them. The only
reason to actually parse them is if you want to display them or feed them
into some other system.
In all other cases, you simply enter the data, query for matching sets and
display the names or other properties of the matching objects, like
"TV station X overlaps with TV station Y".

Here's another article (geared towards DML) that shows a bit about how
to work with geometries:
http://forums.oracle.com/forums/thre...tstart=0#38575

Lots of Greetings!
Volker