This discussion is archived
1 Reply Latest reply: Sep 19, 2012 2:22 AM by _jum RSS

Tables with Spatial Columns sizing issues

FlyingGuy Explorer
Currently Being Moderated
Oracle EE latest everything

So I have a table of link data as in:
describe links ;
 Name                            Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                  NUMBER
 NETWORK_ID                             NUMBER
 BEG_NODE_ID                             NUMBER
 END_NODE_ID                             NUMBER
 GEOM                                  MDSYS.SDO_GEOMETRY
 LENGTH                              NUMBER(9,3)
 SPEED_LIMIT                             NUMBER(3)
 DETAIL_LEVEL                             NUMBER(3)
 IN_SYNCH                             NUMBER(1)
 CREATED                             DATE
 MODIFIED                             DATE
 CREATED_BY                             VARCHAR2(30)
 MODIFIED_BY                             VARCHAR2(30)
 MODSTAMP                             TIMESTAMP(6)
Table is not partitioned and has at the moment about 69 million rows. ID, Network_id have your basic normal indexes on them.
geom has a spatial index on it. So I guess my question is am I starting to push limits here for spatial index performance?
In another thread here I read something that oracle will not use a spatial index for certain kinds of operations?

The geometry that is recorded in each row is G_TYPE 2002 (line or curve) and as an example:
SDO_GEOMETRY(2002, 8307, NULL, 
SDO_ELEM_INFO_ARRAY(1, 2, 1), 
SDO_ORDINATE_ARRAY(
-97.35263, 
32.70169, 
-97.35277, 
32.70178, 
-97.35301, 
32.70185, 
-97.35319, 32.70186,
-97.35347, 32.70184)
)
So John gave me this really great reply for how to look for a point in a bounding box using the:
 
sdo_inside(geom, [create a 2003 geometry to describe a rectangle]) 
function. Now since this line is a series of points, can one still use the sdo_inside or would:
 sdo_anyinteract(geom,[create a 2003 geometry to describe a rectangle]) 
function be better?

Thanks in advance.
  • 1. Re: Tables with Spatial Columns sizing issues
    _jum Journeyer
    Currently Being Moderated
    Her a little example. That I had not to build your tables and sdo_geom_metadata and spatial index, I used the SDO_GEOM.RELATE function instead of the spatial operator. The result is the same:
    --test relation line with point (second vertex of line)
    --  INSIDE
    SELECT SDO_GEOM.RELATE 
     (SDO_GEOMETRY(2002, 8307, NULL, 
        SDO_ELEM_INFO_ARRAY(1, 2, 1), 
        SDO_ORDINATE_ARRAY(-97.35263,32.70169,-97.35277,32.70178,-97.35301,32.70185,-97.35319, 32.70186,-97.35347, 32.70184)),
      'INSIDE',
      SDO_GEOMETRY(2001, 8307,  
        SDO_POINT_TYPE(-97.35277,32.70178,0), NULL, NULL),
      0.01
     ) rel FROM dual;  
    
    REL                                                                             
    --------------------------------------------------------------------------------
    FALSE                                                                           
    1 row selected.
    
    --  TOUCH
    SELECT SDO_GEOM.RELATE 
     (SDO_GEOMETRY(2002, 8307, NULL, 
        SDO_ELEM_INFO_ARRAY(1, 2, 1), 
        SDO_ORDINATE_ARRAY(-97.35263,32.70169,-97.35277,32.70178,-97.35301,32.70185,-97.35319, 32.70186,-97.35347, 32.70184)),
      'TOUCH',
      SDO_GEOMETRY(2001, 8307,  
        SDO_POINT_TYPE(-97.35277,32.70178,0), NULL, NULL),
      0.01
     ) rel FROM dual;  
    
    REL                                                                             
    --------------------------------------------------------------------------------
    FALSE                                                                           
    1 row selected.
    
    --  ANYINTERACT
    SELECT SDO_GEOM.RELATE 
     (SDO_GEOMETRY(2002, 8307, NULL, 
        SDO_ELEM_INFO_ARRAY(1, 2, 1), 
        SDO_ORDINATE_ARRAY(-97.35263,32.70169,-97.35277,32.70178,-97.35301,32.70185,-97.35319, 32.70186,-97.35347, 32.70184)),
      'ANYINTERACT',
      SDO_GEOMETRY(2001, 8307,  
        SDO_POINT_TYPE(-97.35277,32.70178,0), NULL, NULL),
      0.01
     ) rel FROM dual;  
    
    REL                                                                             
    --------------------------------------------------------------------------------
    TRUE                                                                            
    1 row selected.
    You'll find the theory in the fine manua l.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points