Oracle - spatial join

Regarding spatial operations like SDO_ANYINTERACT and other you should remember: only one parameter can be a layer (spatially indexed geometry column), the other must represent one spatial object.

Let's try:

I want to know which objects from LAYER_1 has "anyinteract" realationship with the object 103 from LAYER_2.

select id

from layer_1

where SDO_ANYINTERACT(

        geom

        ,

        (select geom from layer_2 where id = 103)

        ) = 'TRUE'

;

        ID

----------

         1

         3

As soon as the second parameter represents one object we'll get the result. But if there are more objects (in this case we'll take two: object 102 and 103) – we won't. Let's try:

select id

from layer_1

where SDO_ANYINTERACT(

        geom

        ,

        (select geom from layer_2 where id in (103,102))

        ) = 'TRUE'

;

ORA-29902: error in executing ODCIIndexStart() routine

ORA-01427: single-row subquery returns more than one row

29902. 00000 -  "error in executing ODCIIndexStart() routine"

*Cause:    The execution of ODCIIndexStart routine caused an error.

*Action:   Examine the error messages produced by the indextype code and

           take appropriate action.

Proper way to do it is the spatial join as follows:

select x.id

from layer_1 x

  join layer_2 y on (SDO_ANYINTERACT(x.geom,y.geom) = 'TRUE')

where y.id in (103,102);

        ID

----------

         1

         3

         5

All the 'anyinteract' relationships we obtain by the following SQL:

select x.id LYR_1, y.id LYR_2

from layer_1 x

  join layer_2 y on (SDO_ANYINTERACT(x.geom,y.geom) = 'TRUE')

;

     LYR_1      LYR_2

---------- ----------

         1        103

         2        101

         3        103

         5        102

We can also display what exact type of relationship is this:

select

    x.id LYR_1

  , y.id LYR_2

  ,SDO_GEOM.RELATE(x.geom, 'determine', y.geom, 1e-2) as REL

from layer_1 x

  join layer_2 y on (SDO_ANYINTERACT(x.geom,y.geom) = 'TRUE')

;

    LYR_1      LYR_2         REL                      

---------- ----------------- -------------------------

         1        103        OVERLAPBDYINTERSECT 

         2        101        CONTAINS                

         3        103        OVERLAPBDYINTERSECT     

         5        102        OVERLAPBDYINTERSECT   

DatabaseError: Invalid handle! while reading LOB in Python/cx_Oracle

I've spend much time on this:

orcl = cx_Oracle.connect(usrpass+'@'+dbase)
c = orcl.cursor()
c.execute(sq)
dane =  c.fetchall()

orcl.close() # before reading LOB to str

wkt = dane[0][0].read()

And I get: DatabaseError: Invalid handle!

But the following code works, because connection is closed after reading LOB to STR:

orcl = cx_Oracle.connect(usrpass+'@'+dbase)
c = orcl.cursor()
c.execute(sq)
dane =  c.fetchall()

wkt = dane[0][0].read()

orcl.close() # after reading LOB to str

[http://stackoverflow.com/questions/8646968/how-do-i-read-cx-oracle-lob-data-in-python/12590977#12590977]

How to count size od tables in Oracle Spatial?

Generally:

SELECT round((bytes/1024/1024/1024),1) size_GB
FROM dba_segments
WHERE owner = <OWNER_NAME> and segment_name = <TABLE_NAME>;

Use the above select for:

1) Table itself.

2) Indexes for the table (put them as <TABLE_NAME> in the above statement). You can retrieve them as follows:

2.1) Indexes:

SELECT index_name

FROM all_indexes

WHERE table_owner = <OWNER_NAME>and table_name = <TABLE_NAME>;

2.2) Spatial indexes:

SELECT sdo_index_table

FROM all_sdo_index_info

WHERE table_owner = <OWNER_NAME>and table_name = <TABLE_NAME>;

How to select value of SDO_GEOMETRY Object

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_objrelschema.htm#i1004087

States:

"Oracle Spatial defines the object type SDO_GEOMETRY as:

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER,
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES SDO_ORDINATE_ARRAY); "

Let's assume we have a "TABLE" with SDO_GEOMETRY Type field named "GEOM".
If you want to get the values of certain SDO_GEOMETRY Type field and make query like this:

select geom.SDO_GTYPE from table;

You'll get ORA-00904 Error: "invalid identifier".

Try with table name alias:

select t.geom.SDO_GTYPE from table t;

GEOM.SDO_GTYPE   
----------------------
2003      

cx_Oracle - how to get SDO_GEOMETRY data

#Let's connect to the database

import cx_Oracle

orcl = cx_Oracle.connect ('scott/tiger@orcl')

c = orcl.cursor()

#we need some spatial data

sql = "SELECT geom FROM geometry_tab WHERE (...)"

res = c.execute(sql)

obj = res.fetchone()[0]

#ok, what do we have?

obj

<cx_Oracle.OBJECT object at 0x0158A4C0>

#if you type dir(obj) you will not get the attributes names - try..
# you MUST know (or read here) the you can use attributes from SDO_GEOMETRY object in ORCL, like:
#'SDO_GTYPE','SDO_SRID','SDO_POINT','SDO_ELEM_INFO','SDO_ORDINATES'

obj.SDO_GTYPE

2003.0

obj.SDO_ELEM_INFO

[1.0, 1003.0, 1.0]

obj.SDO_ORDINATES

[20.623358189, 52.88806347, 20.623203441, 52.887897231000004, 20.623488043000002, 52.887861954, (...) , 52.888026894, 20.623358189, 52.88806347]