1 Reply Latest reply: Sep 19, 2012 4:22 AM by _jum RSS

    Tables with Spatial Columns sizing issues

    FlyingGuy
      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
          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.