20.9 SDO_JOIN
Format
SDO_JOIN(table_name1, column_name1, table_name2, column_name2, params,preserve_join_order, table1_partition, table2_partition) RETURN SDO_ROWIDSET;
Description
Performs a spatial join based on one or more topological relationships.
Keywords and Parameters
| Value | Description |
|---|---|
|
table_name1 |
Name of the first table to be used in the spatial join operation. The table must have a column of type SDO_GEOMETRY. Data type is VARCHAR2. |
|
column_name1 |
Name of the spatial column of type SDO_GEOMETRY in |
|
table_name2 |
Name of the second table to be used in the spatial join operation. (It can be the same as or different from |
|
column_name2 |
Name of the spatial column of type SDO_GEOMETRY in |
|
params |
Optional parameter string of keywords and values; available only if |
|
preserve_join_order |
Optional parameter to specify if the join order is guaranteed to be preserved during processing of the operator. If the value is |
|
table1_partition |
Name of the table partition in |
|
table2_partition |
Name of the table partition in |
Returns
SDO_JOIN returns an object of SDO_ROWIDSET, which consists of a table of objects of SDO_ROWIDPAIR. Oracle Spatial defines the type SDO_ROWIDSET as:
CREATE TYPE sdo_rowidset as TABLE OF sdo_rowidpair;
Oracle Spatial defines the object type SDO_ROWIDPAIR as:
CREATE TYPE sdo_rowidpair AS OBJECT
(rowid1 VARCHAR2(24),
rowid2 VARCHAR2(24));
In the SDO_ROWIDPAIR definition, rowid1 refers to a rowid from table_name1, and rowid2 refers to a rowid from table_name2.
Usage Notes
SDO_JOIN is technically not an operator, but a table function. (For an explanation of table functions, see Oracle Database PL/SQL Language Reference.) However, it is presented in the chapter with spatial operators because its usage is similar to that of the operators, and because it is not part of a package with other functions and procedures.
This table function is recommended when you need to perform full table joins.
The geometries in column_name1 and column_name2 must have the same SRID (coordinate system) value and the same number of dimensions.
For best performance, use the /*+ ORDERED */ optimizer hint, and specify the SDO_JOIN table function first in the FROM clause.
If a table is version-enabled (using the Workspace Manager feature), you must specify the <table_name>_LT table created by Workspace Manager. For example, if the COLA_MARKETS table is version-enabled and you want to perform a spatial join operation on that table, specify COLA_MARKETS_LT (not COLA_MARKETS) with the SDO_JOIN table function. (However, for all other spatial functions, procedures, and operators, do not use the <table_name>_LT name.)
Table 20-3 shows the keywords for the params parameter.
Table 20-3 params Keywords for the SDO_JOIN Operator
| Keyword | Description |
|---|---|
|
mask |
The topological relationship of interest.Valid values are 'mask=<value>' where <value> is one or more of the mask values valid for the SDO_RELATE operator ( If this parameter is null or contains an empty string, |
|
distance |
Specifies a numeric distance value that is added to the tolerance value (explained in Tolerance) before the relationship checks are performed. For example, if the tolerance is 10 meters and you specify |
|
unit |
Specifies a unit of measurement to be associated with the distance value (for example, |
Before you call SDO_JOIN, you must commit any previous DML statements in your session. Otherwise, the following error will be returned: ORA-13236: internal error in R-tree processing: [SDO_Join in active txns not supported]
For information about 3D support with spatial operators (which operators do and do not consider all three dimensions in their computations), see Three-Dimensional Spatial Objects.
Optimizing Self-Joins
If you are performing a self-join (that is, if table_name1 and table_name2 specify the same table), you can improve the performance by optimizing the self-join.
If SDO_JOIN is called without a mask (for example, ANYINTERACT) or distance specification, it compares only the index structure of the two geometry columns being joined. This can quickly identify geometry pairs that are "likely" to interact. If SDO_JOIN is called with a mask or distance specification, after the index is used to identify geometry pairs that are likely to interact, geometry coordinates are also compared to see if the geometry pairs actually do interact. Coordinate comparison is the most expensive part of the SDO_JOIN operation.
In a self-join, where the same geometry column is compared to itself, each geometry pair is returned twice in the result set. For example:
-
For the geometry pair with ID values (1,2), the pair (2,1) is also returned. The undesired effect in SDO_JOIN is that the coordinates of the same geometry pair are compared twice, instead of once.
-
ID pairs that are equal are returned twice. For example, a table with 50,000 rows will return ID pair (1,1) twice, ID pair (2,2) twice, and so on. This is also an undesired effect.
When calling SDO_JOIN in a self-join scenario, you can eliminate the undesired effects by eliminating duplicate comparison of geometry pairs and all coordinate comparisons where the ID values of the pairs match. This optimization uses SDO_JOIN for the primary filter only, and calls the SDO_GEOM.RELATE function to compare geometry coordinates. The following statement accomplishes this optimization by adding "AND b.rowid < c.rowid" as a predicate to the WHERE clause.
SQL> set autotrace trace explain
SQL> SELECT /*+ ordered use_nl (a,b) use_nl (a,c) */ b.id, c.id
FROM TABLE(sdo_join('GEOD_STATES','GEOM','GEOD_STATES','GEOM')) a,
GEOD_STATES b,
GEOD_STATES c
WHERE a.rowid1 = b.rowid
AND a.rowid2 = c.rowid
AND b.rowid < c.rowid
AND SDO_GEOM.RELATE (b.geom, 'ANYINTERACT', c.geom, .05) = 'TRUE'
Execution Plan
----------------------------------------------------------
Plan hash value: 1412731386
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1124 | 12787 (1)| 00:02:34 |
| 1 | NESTED LOOPS | | 1 | 1124 | 12787 (1)| 00:02:34 |
| 2 | NESTED LOOPS | | 4574 | 2514K| 8206 (1)| 00:01:39 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| SDO_JOIN | | || |
|* 4 | TABLE ACCESS BY USER ROWID | GEOD_STATES | 1 | 561 |1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY USER ROWID | GEOD_STATES | 1 | 561 |1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(CHARTOROWID(VALUE(KOKBF$)))
5 - access(CHARTOROWID(VALUE(KOKBF$)))
filter("B".ROWID<"C".ROWID AND
"SDO_GEOM"."RELATE"("B"."GEOM",'ANYINTERACT',"C"."GEOM",.05)='TRUE')
SQL> set autotrace off
In the preceding example, It is very important that AND b.rowid < c.rowid be before the call to SDO_GEOM.RELATE in the WHERE clause. This will omit the undesired scenarios for the invocation of the SDO_GEOM.RELATE function. Also, note that the example uses the ORDERED and USE_NL hints, and that the execution plan does not contain TABLE ACCESS FULL or HASH JOIN.
Cross-Schema Invocation of SDO_JOIN
You can invoke the SDO_JOIN table function on an indexed table that is not in your schema, if you have been granted SELECT access to both the spatial table and to the index table for the spatial index that was created on the spatial table. To find the name of the index table for a spatial index, query the SDO_INDEX_TABLE column in the USER_SDO_INDEX_METADATA view. For example, the following statement returns the name of the index table for the COLA_MARKETS_IDX spatial index:
SELECT sdo_index_table FROM user_sdo_index_metadata WHERE sdo_index_name = 'COLA_SPATIAL_IDX';
Assume that user A owns spatial table T1 (with index table MDRT_F9AA$), and that user B owns spatial table T2 and wants to join geometries from both T1 and T2. Assume also that the geometry column in both tables is named GEOMETRY.
User A or a suitably privileged user must connect as user A and execute the following statements:
GRANT select on T1 to B; GRANT select on MDRT_F9AA$ to B;
User B can now connect and execute an SDO_JOIN query, such as the following:
SELECT COUNT(*) FROM
(SELECT * FROM
TABLE(SDO_JOIN('A.T1', 'GEOMETRY',
'B.T2', 'GEOMETRY',
'mask=anyinteract')) );Examples
The following example joins the COLA_MARKETS table with itself to find, for each geometry, all other geometries that have any spatial interaction with it. (The example uses the definitions and data from Simple Example: Inserting_ Indexing_ and Querying Spatial Data.) In this example, rowid1 and rowid2 correspond to the names of the attributes in the SDO_ROWIDPAIR type definition. Note that in the output, cola_d (the circle) interacts only with itself, and not with any of the other geometries.
SELECT /*+ ordered */ a.name, b.name
FROM TABLE(SDO_JOIN('COLA_MARKETS', 'SHAPE',
'COLA_MARKETS', 'SHAPE',
'mask=ANYINTERACT')) c,
cola_markets a,
cola_markets b
WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid
ORDER BY a.name;
NAME NAME
-------------------------------- --------------------------------
cola_a cola_c
cola_a cola_b
cola_a cola_a
cola_b cola_c
cola_b cola_b
cola_b cola_a
cola_c cola_c
cola_c cola_b
cola_c cola_a
cola_d cola_d
10 rows selected.Related Topics
Parent topic: Spatial Operators