3 Replies Latest reply: Jan 20, 2014 9:30 AM by Bruno_Fonts RSS

    SDO Operations and Spatial Index

    Bruno_Fonts

      Hi everyone,

       

      I'm new on dealing with geometries in Oracle so I'm looking for some advice on how to proceed in a certain situation:

       

      The scenario is: I have a huge table with over 180M vehicles positions and I need to look into this table which coordinates are inside a given radius of another coordinate.

      Because I have lat/long columns I create the SDO_Geometry inside my query. The problem is that the SDO operations ask for a spatial index, throwing me the ORA-13226

      What is driving me nuts is that I don't understand how can I create indexes if I don't even have a table to do so since my geometries are being created on execution time.

       

      The only operation I could do was SDO_RELATE, but I'd like to try another ones since this is taking too much time to run when comparing lots of coordinates.

       

      As I wrote above, I'm new on this so if anything else I'm doing wrong please don't hesitate to tell me.

       

      --This is the table:  
      CREATE TABLE VEHICLE_LOCATIONS   
         ("ID" NUMBER(*,0) NOT NULL ENABLE,   
        "DATE_HOUR" DATE NOT NULL ENABLE,   
        "VEHICLE_ID" NUMBER(*,0) NOT NULL ENABLE,  
        "LATITUDE" VARCHAR2(15 BYTE) NOT NULL ENABLE,   
        "LONGITUDE" VARCHAR2(15 BYTE) NOT NULL ENABLE,   
        CONSTRAINT "PK_VEHICLE_LOCATIONS" PRIMARY KEY ("ID"));  
      CREATE INDEX VEHICLE_LOCATIONS_IDX1 ON VEHICLE_LOCATIONS (VEHICLE_ID, DATE_HOUR);  
      --These are que queries I'm trying to run:  
      SELECT * from  
        (select *   
        from VEHICLE_LOCATIONS   
        where VEHICLE_ID in (select vehicle_id from vehicles where customer_id = 312) and vehicle_status = 1)  
        and DATE_HOUR between '07/01/2014 00:00:00' and '08/01/2014 00:00:00') tg  
      where  
        SDO_GEOM.RELATE(  
          sdo_geom.sdo_buffer(mdsys.SDO_GEOMETRY(2001, 4326,mdsys.sdo_point_type(-51.214500, -30.043887, null), null, null),10, 0.5, 'arc_tolerance=0.1 unit=km'), 'anyinteract',   
                              mdsys.SDO_GEOMETRY(2001, 4326,mdsys.sdo_point_type( to_number(REPLACE(tg.LONGITUDE, '.', ',')), to_number(REPLACE(tg.LATITUDE, '.', ',')), null), null, null ), 0.01) = 'TRUE';  
                                
                                
      SELECT * from  
        (select *   
        from VEHICLE_LOCATIONS   
        where VEHICLE_ID in (select vehicle_id from vehicles where customer_id = 312) and vehicle_status = 1)  
        and DATE_HOUR between '06/01/2014 00:00:00' and '07/01/2014 00:00:00') tg  
      where  
        SDO_FILTER(  
          sdo_geom.sdo_buffer(mdsys.SDO_GEOMETRY(2001, 4326,mdsys.sdo_point_type(-51.214500, -30.043887, null), null, null),10, 0.5, 'arc_tolerance=0.1 unit=km'),  
                                                   mdsys.SDO_GEOMETRY(2001, 4326,mdsys.sdo_point_type( to_number(REPLACE(tg.LONGITUDE, '.', ',')), to_number(REPLACE(tg.LATITUDE, '.', ',')), null), null, null )) = 'TRUE';  
                                
                                
      SELECT * from  
        (select *   
        from VEHICLE_LOCATIONS   
        where VEHICLE_ID in (select vehicle_id from vehicles where customer_id = 312) and vehicle_status = 1)  
        and DATE_HOUR between '05/01/2014 00:00:00' and '06/01/2014 00:00:00') tg  
      where  
        SDO_WITHIN_DISTANCE(  
          mdsys.SDO_GEOMETRY(2001, 4326,mdsys.sdo_point_type( to_number(REPLACE(tg.LONGITUDE, '.', ',')), to_number(REPLACE(tg.LATITUDE, '.', ',')), null), null, null),  
          mdsys.SDO_GEOMETRY(2001, 4326,mdsys.sdo_point_type(-51.214500, -30.043887, null), null, null),  
          'distance=10 unit=KM') = 'TRUE';
      

       

      Any help will be very appreciated.

       

      Best wishes,

       

      Bruno.

        • 1. Re: SDO Operations and Spatial Index
          Ying Hu-Oracle

          Two options: option 1: you can create a function-based  spatial index. Please see the following

          example:

          Extending Spatial Indexing Capabilities

          Option 2: if your db is 12.1, you can try the new table function: SDO_POINTINPOLYGON.

          Please see the following example:

          Spatial Operators

          • 2. Re: SDO Operations and Spatial Index
            Bruno_Fonts

            Hi yhu,

             

            Thanks for the response!

            My db is 11 yet, so I I'll stick to the other option.

             

            I'll try that solution of function-based spatial index as suggested tonight and post the results over here tomorrow.

             

            Rgds.

            • 3. Re: SDO Operations and Spatial Index
              Bruno_Fonts

              Hi all,

               

              So, as Yhu suggested I created (or at least tried to) the spatial index. Because it took too long (more than 5 hours), I had to stop the index creation and look for some other alternative.

               

              The solution that I found that worked best for my case was:

              - The usage os another spatial operation sdo_geom.sdo_distance(), which gave me a performance increase of 65~70%.

              - The usage of a simple mathematical coordinate restriction. Restricting the location from the whole world to somewhere in an area of some kilometers gave me another boost of 80% over the previous results.

               

              At the end, I had a super optimized query (comparing the execution time of each one only) going from the original 10~13min to 10~20sec of querying.

               

              Hope some of this help someone else as it helped me.

               

              Rgds.