ArcSDE vs. Oracle Spatial 9

1. R-tree spatial index

Geometric objects in nature are all strange, and managing such data is a headache. Therefore, GIS Engineers simplify the process. One way is to take out the boundary range of the geometric object as a basis for retrieval (the geometric object must be inside it ), this is the range of a rectangle, and it is much easier to manage it. In Oracle Spatial, the R-tree-based spatial index adopts this method (which is also the recommended Index Algorithm). This rectangle is called MBR: Minimum bounding rectangle.

Figure 4 MBR of geometric objects in Oracle Spatial

In Oracle Spatial, all geometric objects in a spatial table are managed through the R tree index of their MBR. It is easy to understand from the following figure, the root nodes of the MBR index include a and B. When a query can quickly find a, find the two smaller MBR, A and B, if a is found, then the minimum MBR 1 or 2 is found.

Figure 5 MBR of the R-tree Index

For the test table test_index that we created the index above, its MBR index information is stored in the user_sdo_index_metadata View:

SQL> select sdo_index_table, sdo_rtree_height, sdo_rtree_num_nodes from user_sdo_index_metadata where sdo_index_name = 'idx _ test_index_geom ';

Sdo_index_table sdo_rtree_height sdo_rtree_num_nodes

---------------------------------------------------------------------------------------

Mdrt_1350b $4 3336

If we open the mdrt_1350b $ table, you can see the information of each node in the r tree:

SQL> DESC mdrt_1350b $

Is the name empty? Type

---------------------------------------------------------------------------------------------------------------------

Node_id number

Node_level number

Info blob

SQL> select * From mdrt_1350b $ where node_level = 3 and rownum <3;

Node_id node_level

--------------------

Info

Bytes ------------------------------------------------------------------------------------------------------------------------

3336 3

Please refer to the following link for more information:

20171000041414154554d414147414141426f6f41

3337 3

Please refer to the following link for more information:

20171000041414154554d414147414141426f5241

2. Binary Tree spatial index

Oracle spabench does not encourage the use of the spatial index of the quad-tree, unless in some special scenarios and you confirm that the use of the quad-tree index will bring benefits [1]
. Oracle provides a table to compare the features of the R-tree spatial index and the quad-tree spatial index.

R Tree	Quad-tree
It cannot be a good approximation of geometric objects (MBr has a rough approximation of strange geometric objects)	Better approximation of geometric objects
Index creation and adjustment are relatively simple	Index adjustment is complex, and index parameters have a greater impact on performance.
Small storage space	Large Storage space
The sdo_nn operation is fast. You can also specify the sdo_batch_size parameter of the operation.	Opposite
Updating data in batches may reduce the access efficiency of Space tables.	Data Update is unrelated to access efficiency
You can index four dimensions.	Only two dimensions can be indexed
When the sdo_within_distance operation is used for data in the geographic coordinate system, we recommend that you use the r tree index.	Opposite
For whole-earth data [2] Only the r tree index can be used.	Opposite

The principle of spatial indexes of the quad-tree is like the principle of map slicing. 6. As you can imagine, the more levels the index has, the more accurate the description of the geometric objects.

Figure
6 Oracle Spatial quad-tree spatial index

Generally, Oracle Spatial uses a fixed slice range to create an index for the quad-tree space. This method is based on the current map range and sdo_level parameter settings. If sdo_level is 1, it is equivalent to a slice of the entire layer. If sdo_level is 2, the entire layer is divided into four parts, and so on. In addition, in the quad-tree index, each element of the geometric object is indexed, 7.

Figure
7. element of the geometric object in the spatial index of the Quadtree

You can create a fixed slice size and 8-Level Depth quad-tree spatial index by using the following method:

SQL> Create index idx_continent_geom on Spatial. continent (Geom) indextype is MDSYS. spatial_index parameters ('sdo _ Level = 8 ');

The index has been created.

Used time: 00: 05: 11.48

SQL> select sdo_index_type, sdo_index_table from user_sdo_index_metadata where sdo_index_name = 'idx _ continent_geom ';

Sdo_index_type

Bytes ------------------------------------------------------------------------------------------------

Sdo_index_table

Bytes ------------------------------------------------------------------------------------------------

Qtree

Mdqt_13537 $

The name of the table that stores the index data is mdqt_13537 $. You can check that the structure of this table is completely different from that of the r tree index table:

SQL> DESC mdqt_13537 $

Is the name empty? Type

---------------------------------------------------------------------------------------------------------------------

Sdo_code raw (16)

Sdo_rowid rowid

Sdo_status varchar2 (1)

SQL> select * From mdqt_13537 $ where rownum <10;

Sdo_code sdo_rowid sdo_status

-------------------------------------------------------------------------------------

Cbd9 aaatuwaagaaabngaaa B

CBDC aaatuwaagaaabngaaa B

Cbdd aaatuwaagaaabngaaa I

Cbde aaatuwaagaaabngaaa B

Cbdf aaatuwaagaaabngaaa B

Cc00 aaatuwaagaaabngaaa I

Cc01 aaatuwaagaaabngaaa I

Cc02 aaatuwaagaaabngaaa I

Cc03 aaatuwaagaaabngaaa I

If the value of sdo_status is B, it indicates that the geometric object and the current slice boundary are intersecting. If it is I, it indicates that the geometric object is within the current slice.

[1]
Oracle Spatial Quadtree Indexing: The use of spatial quadtree indexes is discouraged. you are stronugly encouraged to use R-tree Indexing for spatial indexes, unless you need to continue using quadtree indexes for special situations.

[2]
Oracle Spatial contains a whole-earth geometric model, which can be used to calculate the actual length and area based on the earth surface curvature and other parameters in the geographic coordinate system data. It is similar to the built-in projection algorithm.