Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.