ArcSDE vs. Oracle Spatial 11

1. Create and adjust grid Indexes

ArcSDE provides a maximum of three levels of spatial indexes. The performance of spatial data is related to how to select the level of spatial indexes and how many grid size settings are appropriate. Here we start with a simple polygon layer to explore the performance influencing factors of some grid space indexes.

First, we prepare a WGS84 surface layer in ArcSDE named "testgrid ". On this surface layer, we add two elements, a smaller one, and a larger one, as shown in 10. To provide some reference information, the WGS84 coordinate ranges are separated by 30 degrees, which are displayed under these two elements.

Figure
10 layer in ArcSDE

If we create a three-level index for the above data, we can position the smallest grid size to 30 for the sake of intuition, that is, the size of the square in figure 10. Therefore, you can select a size of 3rd x 270 for the maximum mesh of 270, so that the size of 2nd can be set to 90x90, and the size of 1st can be set to 30x30. Create a spatial index as follows:

SQL> Create index a14policix1 on testgrid (SHAPE)

Indextype is st_spatial_index

Parameters ('st _ grids = 30, 90, 270 st_srid = 2 ');

Odciindexcreate

Create Table SDE. s141_idx $ (Gx integer, GY integer, Minx integer, miny integer, Maxx integer, Maxy integer, sp_id

Rowid, constraint s141 $ _ ix1 primary key (GX, Gy, Maxx, Maxy, Minx, miny, sp_id) Organization index pctthreshold 5 pctfree 0

Initrans 4

The index has been created.

Let's see how the two elements in this dataset are indexed:

SQL> select * From s141_idx $;

GX Gy Minx miny Maxx Maxy sp_id

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

33554432 33554433 2.3376e + 11 3.3580e + 11 3.8784e + 11 4.8211e + 11 aaatb9aafaaaejiaaa

33554433 33554433 2.3376e + 11 3.3580e + 11 3.8784e + 11 4.8211e + 11 aaatb9aafaaaejiaaa

15 14 4.6127e + 11 4.3176e + 11 4.7062e + 11 4.3886e + 11 aaatb9aafaaaejiaab

SQL> select * From testgrid where rowid = 'aaatb9aafaaaejiaaa ';

Objectid

----------

SHAPE (entity, numpts, Minx, miny, Maxx, Maxy, minz, maxz, minm, maxm, area, Len, SRID, points)

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

1

St_geometry (8, 8,-166.23891,-64.197952,-12.163823, 82.105802, 12078.8885, null, 439.10602, 2, 'a001

Bytes

Comment ')

SQL> select * From testgrid where rowid = 'aaatb9aafaaaejiaab ';

Objectid

----------

SHAPE (entity, numpts, Minx, miny, Maxx, Maxy, minz, maxz, minm, maxm, area, Len, SRID, points)

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

2

St_geometry (8, 4, 63.8600683, 31.7610922, 70.6177474, 38.8566553, null, 22.6047828, 21.6768769, 2, '2a

Comment ')

Judging from the number of nodes, 'aaatb9aafaaaejiaaa' corresponds to a large polygon and 'aaatb9aafaaaejiaab' corresponds to a small polygon. First, let's look at the small polygon. GX and Gy are respectively 15 and 14. It can be seen that the small polygon is indexed at 1st of the mesh size of 30. The large polygon is indexed twice, it should be indexed at other levels, but the GX and Gy values cannot be explained by the preceding SIMPLE algorithm. Why?

In fact, this large polygon is indexed at level 3rd. That is to say, ArcSDE will determine the level at which a geometric object should be indexed, you can find it in the spx_util package of ArcSDE:

Procedure compute_feat_grid_envp (gsize1 in integer,

Gsize2 in integer,

Gsize3 in integer,

E_minx in integer,

E_miny in integer,

E_maxx in integer,

E_maxy in integer,

G_minx out integer,

G_miny out integer,

G_maxx out integer,

G_maxy out integer)

Is

Begin

G_minx: = trunc (e_minx/gsize1 );

G_miny: = trunc (e_miny/gsize1 );

G_maxx: = trunc (e_maxx/gsize1 );

G_maxy: = trunc (e_maxy/gsize1 );

If (g_maxx-g_minx + 1) * (g_maxy-g_miny + 1)> max_grids_per_level

And gsize2> 0) then

G_minx: = trunc (e_minx/gsize2 );

G_miny: = trunc (e_miny/gsize2 );

G_maxx: = trunc (e_maxx/gsize2 );

G_maxy: = trunc (e_maxy/gsize2 );

If (g_maxx-g_minx + 1) * (g_maxy-g_miny + 1)> max_grids_per_level

And gsize3> 0) then

G_minx: = trunc (e_minx/gsize3 );

G_miny: = trunc (e_miny/gsize3 );

G_maxx: = trunc (e_maxx/gsize3 );

G_maxy: = trunc (e_maxy/gsize3 );

G_minx: = g_minx + grid_level_mask_2;

G_miny: = g_miny + grid_level_mask_2;

G_maxx: = g_maxx + grid_level_mask_2;

G_maxy: = g_maxy + grid_level_mask_2;

Else

G_minx: = g_minx + grid_level_mask_1;

G_miny: = g_miny + grid_level_mask_1;

G_maxx: = g_maxx + grid_level_mask_1;

G_maxy: = g_maxy + grid_level_mask_1;

End if;

End if;

End compute_feat_grid_envp;

There are several constants, which can be found in the spx_util header:

Max_grids_per_level constant pls_integer: = 4;

Grid_level_mask_1 constant pls_integer: = 16777216;

Grid_level_mask_2 constant pls_integer: = 33554432;

That is to say, when ArcSDE indexes a geometric object, it starts to judge from the 1st-level grid. If a geometric object spans more than one mesh (or one mesh cannot contain a certain geometric object) when the size of the 2nd-level grid is greater than 0, the ArcSDE performs similar computing on the 2nd-level grid. Similarly, if there is a 3rd-level grid available, arcSDE may also index the geometric object to a 3rd-level grid. In general, ArcSDE tries to index geometric objects with the least number of grids.

In addition, there are two constants grid_level_mask_1 = 16777216 and grid_level_mask_2 = 33554432, which are used for the identification of grid numbers of level 2nd and level 3rd respectively, for grids of different levels, 0 (Level 1st), grid_level_mask_1 (Level 2nd), or grid_level_mask_2 (Level 3rd) are added based on the current grid level ). It can be seen that the GX and Gy of the two grids indexed by the large polygon should be reduced by 33554432, that is, GX = 0, GY = 1 and GX = 1, GY = 1, respectively, this is a 3rd-level index.

Based on the above principles, we can have some principled judgment on how to select the grid size of spatial indexes:

L if there is no special need, it is better to use a level-1 index if you can use a level-1 index, because if there is a multi-level index, all the index levels will be scanned during the query.

L multi-level indexes are required only when the size of geometric objects changes significantly and the number of objects in each magnitude is equivalent.

L for point data, you only need to set a level-1 index (the size of the geometric object has not changed), and set the grid to a large size (to avoid the case that there are only a few points in a grid)

L if the data size changes significantly, you may need to recalculate and adjust the grid size.

For the grid size, you can make an estimation by the average value of the data range. Generally, You can take 3 ~ 4 times. For example, you can use this command to estimate the surface layer:

SQL> select 4 * (AVG (T. Shape. maxx-t.shape.minx) + AVG (T. Shape. maxy-t.shape.maxy)/2 from testgrid T;

4 * (AVG (T.SHAPE.MAXX-T.SHAPE.MINX) + AVG (T.SHAPE.MAXY-T.SHAPE.MAXY)/2

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

160.832765

Of course, the simpler method is to calculate through arccatalog, 11.

Figure
11 re-calculate the appropriate grid size through arccatalog