Random Spatial Search Procedure

I have had the need, over the years, to produce statistics that helped me decide how effective a change to a property of a spatial table or index might be. Back in the old days of the Quad Tree index, I used a procedure like the following, to work out the best properties for a (hybrid) index. Nowadays I use it to discover how much a change to coordinate precision or table compression might have on performance.

Anyway, here is the procedure (also available in the TOOLS package).

 define defaultSchema='&1'
 -- Create Required Window Type
 CREATE OR REPLACE TYPE WindowSetType IS TABLE OF NUMBER;
 -- Now create the function.
    /** @function    RandomSearchByExtent
    *   @description Procedure that can help for independent testing of the performance of a table/geometry column
    *                perhaps when spatially indexing, re-organising data, rounding ordinates etc.
    *   @param       p_schema          : varchar2 : Schema that owns the object to be searched.
    *   @param       p_table_name      : varchar2 : The object containing the spatal data for which we want to gather stats.
    *   @param       p_column_name     : varchar2 : The sdo_geometry column to be searched.
    *   @param       p_number_searches : number : Number of times to execute each search.
    *   @param       p_window_set      : WindowSetType : Set of search "windows"
    *   @param       p_no_zeros        : boolean : TRUE => zero features searches ignored
    *   @param       p_sdo_anyinteract : boolean : Use Sdo_AnyInteract rather than SDO_FILTER
    *   @param       p_count_vertices  : boolean : Force code to actually process geometry data.
    *   @param       p_debug_detail    : boolean : Don't bother displaying individual search stats
    *   @param       p_min_pixel_size  : number  : Include min_resolution=p_min_pixel_size in search (only when SDO_FILTERing)
    **/
 CREATE OR REPLACE
 PROCEDURE RandomSearchByExtent( p_schema          IN VarChar2,
                                 p_table_name      IN VarChar2,
                                 p_column_name     IN VarChar2,
                                 p_number_searches IN NUMBER  := 100,
                                 p_window_set      IN CODESYS.WindowSetType := CODESYS.WindowSetType(500,1000,2000,3000,4000,5000,10000,20000,50000),
                                 p_no_zeros        IN BOOLEAN := TRUE,
                                 p_sdo_anyinteract IN BOOLEAN := FALSE,
                                 p_count_vertices  IN BOOLEAN := FALSE,
                                 p_debug_detail    IN BOOLEAN := FALSE,
                                 p_min_pixel_size  IN NUMBER  := NULL )
 AUTHID CURRENT_USER
    IS
      v_srid              NUMBER := NULL;
      v_rand_x            NUMBER := 0; 
      v_rand_y            NUMBER := 0;
      v_searchWindowList  CODESYS.WindowSetType := p_window_set;
      v_searchWindowSize  NUMBER;
      v_search_geometry   varchar2(200) := 'MDSYS.SDO_GEOMETRY(2003,:1,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(:2,:3,:4,:5))';
      v_diminfo           mdsys.sdo_dim_array;
      v_lower_x           NUMBER;
      v_lower_y           NUMBER;
      v_upper_x           NUMBER;
      v_upper_y           NUMBER;
      v_range_x           NUMBER;
      v_range_y           NUMBER;
      v_ll_x              NUMBER;
      v_ll_y              NUMBER;
      v_ur_x              NUMBER;
      v_ur_y              NUMBER;
      v_Start_Time        NUMBER;
      v_End_Time          NUMBER;
      v_totalFeatures     NUMBER;
      v_totalVertices     NUMBER;
      v_totalSeconds      NUMBER;
      v_fcount            NUMBER;
      v_vcount            NUMBER;
      v_seconds           NUMBER;
      v_schema_table      varchar2(100);
      v_sql               varchar2(4000);
    BEGIN
      IF ( p_schema IS NULL ) THEN
         v_schema_table := UPPER(p_table_name);
      ELSE
         V_schema_table := UPPER(p_schema) || '.' || UPPER(p_table_name);
      END IF;
      DBMS_OUTPUT.ENABLE ( 1000000 );
      BEGIN
        SELECT diminfo, srid
          INTO v_diminfo, v_srid
          FROM all_sdo_geom_metadata
         WHERE TABLE_NAME  = UPPER(p_table_name)
           AND column_name = UPPER(p_column_name)
           AND owner       = UPPER(p_schema);
        v_lower_x := v_diminfo(1).SDO_LB;
        v_upper_x := v_diminfo(1).SDO_UB;
        v_lower_y := v_diminfo(2).SDO_LB;
        v_upper_y := v_diminfo(2).SDO_UB;
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
               IF ( p_debug_detail ) THEN
                  dbms_output.put_line('No metadata record found for '||v_schema_table||'.'||p_column_name || ' manually computing MBR and SRID');
               END IF;
               EXECUTE immediate 'select min(t.x),min(t.y), max(t.x),max(t.y)
                                   from table(sdo_util.getVertices((SELECT sdo_aggr_mbr(' || p_column_name || ') from ' || v_schema_table || '))) t'
                           INTO v_lower_x,v_lower_y,v_upper_x,v_upper_y;
               EXECUTE immediate 'select a.' || p_column_name || '.sdo_srid from ' || v_schema_table || ' a where a.' || p_column_name || ' is not null and rownum < 2'
                           INTO v_srid;
      END;
      v_range_x := v_upper_x - v_lower_x;
      v_range_y := v_upper_y - v_lower_y;
      IF ( p_count_vertices ) THEN
         v_sql := 'SELECT count(*), sum(mdsys.sdo_util.getNumVertices(a.' || p_column_name || ')) FROM '||v_schema_table||' A WHERE ';
      ELSE
         v_sql := 'SELECT count(*), 0 FROM '||v_schema_table||' A WHERE ';
      END IF;
      IF ( p_Sdo_AnyInteract ) THEN
        v_sql := v_sql || 'MDSYS.SDO_RELATE(A.'||p_column_name||',' || v_search_geometry || ',''mask=ANYINTERACT ';
      ELSE
        v_sql := v_sql || 'MDSYS.SDO_FILTER(A.'||p_column_name||',' || v_search_geometry || ',''';
      END IF;
      IF p_min_pixel_size IS NOT NULL THEN
        v_sql := v_sql || ' min_resolution=' || p_min_pixel_size ;
      END IF;
      v_sql := v_sql || ' querytype=WINDOW'') = ''TRUE''';
      IF ( p_debug_detail ) THEN
         dbms_output.put_line(SUBSTR('Search SQL = ' || v_Sql,1,255));
      END IF;
      dbms_output.put_line('SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond' || CASE WHEN p_count_vertices THEN ',TotalVertices' ELSE '' END);
      FOR searchSizeCounter IN v_searchWindowList.FIRST..v_searchWindowList.LAST LOOP
        v_totalFeatures := 0;
        v_totalVertices := 0;
        v_totalSeconds  := 0;
        v_searchWindowSize := v_searchWindowList(searchSizeCounter);
        FOR r IN 1..p_number_searches LOOP
          v_fcount := -1;
          -- Loop until we get a valid search
          IF ( p_debug_detail ) THEN
            dbms_output.put_line('RandX,RandY,Count,Seconds');
          END IF;
          WHILE ( v_fcount = -1 ) OR ( v_fcount = 0 AND p_no_zeros ) LOOP
            v_rand_x := dbms_random.VALUE(v_lower_x,v_upper_x);
            v_rand_y := dbms_random.VALUE(v_lower_y,v_upper_y);
            v_ll_x := v_rand_x - ( v_searchWindowSize / 2 );
            v_ll_y := v_rand_y - ( v_searchWindowSize / 2 );
            v_ur_x := v_rand_x + ( v_searchWindowSize / 2 );
            v_ur_y := v_rand_y + ( v_searchWindowSize / 2 );
            v_Start_Time := dbms_utility.get_time;
            BEGIN
                EXECUTE IMMEDIATE v_sql
                             INTO v_fcount, v_vcount
                            USING v_srid,v_ll_X,v_ll_y,v_ur_x,v_ur_y;
                EXCEPTION
                    WHEN OTHERS THEN
                         dbms_output.put_line('ERROR (' || SQLCODE ||') executing SQL ' || v_sql || ' with sdo_ordinate_array(' || v_ll_X||','||v_ll_y||','||v_ur_x||','||v_ur_y||')');
                         RETURN;
            END;
            v_End_Time := dbms_utility.get_time;
          END LOOP;
          v_totalFeatures := v_totalFeatures + v_fcount;
          v_totalVertices := v_totalVertices + v_vcount;
          v_seconds       := ( v_End_Time - v_Start_Time ) / 100;
          v_totalSeconds  := v_totalSeconds + v_seconds;
          IF ( p_debug_detail ) THEN
            dbms_output.put_line(round(v_rand_x,3)||','||round(v_rand_y,3)||','||round(v_fcount,1)||','||TO_CHAR(round(v_seconds,2),'FM99999.99'));
          END IF;
        END LOOP;
        dbms_output.put_line(v_schema_table || ',' ||
                             SUBSTR(v_searchWindowSize || ',' ||
                             p_number_Searches || ',' ||
                             TRIM(TO_CHAR(v_totalFeatures,'FM9999999999')) || ',' ||
                             round(v_Totalseconds,2) || ',' ||
                             TRIM(TO_CHAR(round(v_totalFeatures / v_TotalSeconds,1),'FM999999999.9')),1,255) ||
                             CASE WHEN p_count_vertices THEN ',' || TO_CHAR(v_totalVertices,'FM9999999999999') ELSE '' END );
      END LOOP;
    END RandomSearchByExtent;

Updated:

  • Changed handling of null p_schema.
  • Changed method of providing SDO_GEOMETRY to search function. Now only parameters are the 4 ordinate values that define the rectangle.

Here is an example of how to use the procedure and the results of an invocation.

 SET serveroutput ON SIZE unlimited
 BEGIN
   RandomSearchByExtent(p_schema          => 'GIS',
                        p_table_name      => 'ADMINISTRATION_B',
                        p_column_name     => 'GEOMETRY',
                        p_number_searches => 100,
                        p_window_set      => &&defaultSchema..WindowSetType(2500,5000,10000,20000,50000),
                        p_no_zeros        => TRUE,
                        p_sdo_anyinteract => TRUE,
                        p_count_vertices  => TRUE,
                        p_debug_detail    => FALSE,
                        p_min_pixel_size  => NULL );
 END;
 /
 SearchWindow,Searches,TotalFeatures,TotalSeconds,FeaturesPerSecond,AverageVertices
 2500,100,704,1.12,628.6,538.5
 5000,100,1978,1.6,1236.3,331.6
 10000,100,5731,2.43,2358.4,243.8
 20000,100,17801,6.85,2598.7,259.2
 50000,100,72346,13.36,5415.1,215.3

I hope this is useful to someone.

Leave a Reply

Your email address will not be published. Required fields are marked *