ArcSDE vs. Oracle Spatial 18

Some additional test results:

1. Storage space of geometric objects

In order to test the size of the geometric object storage space of Oracle Spatial and ArcSDE in a relatively real environment, two line data and surface data with about 0.1 billion elements are used respectively. Line data only contains geometric information, and surface data also contains some attribute information. Most of the line data only contains two nodes, and the surface data is usually more than dozens of nodes.

Compare the two data buckets:

Data	Storage Type	Space Data Table storage size (GB)
Line Data	St_geometry	14.72
SDO _ Geometry	16.51
Surface Data	ST _ Geometry	49.93
SDO _ Geometry	87.87

It can be seen that with the increase in the number of geometric object nodes, the sdo_geometry storage consumes a lot of space.

 

· Spatial index performance
1. Use of spatial indexes

There are generally two phases for spatial queries in a spatial database. For Oracle Spatial, a spatial query is divided into two steps, one called primary filter ), the second is secondary filter ). The primary filter first filters out a small part of data that may meet the spatial query through the MBR intersection of the rectangle from massive data, then, we use the specific spatial relationship algorithm in the filtering to determine which of the small result sets meet the spatial relationship. 12. During primary filtering, spatial indexes are used.

Figure
12 process of spatial query in Oracle Spatial

For ArcSDE, the principle is similar. In ArcSDE, there is a function named st_envintersects. This is to filter a small part of the data within a rectangle from massive data through the coordinate of the four corners. For other general spatial operations, such as st_within, If you create a spatial index for your data, this operation will first find a small dataset through the spatial index (equivalent to the st_envintersects operation, or the primary filter in Oracle Spatial), and then use the specific "within" spatial relationship algorithm to query the accurate results (equivalent to the Secondary Filter in Oracle Spatial ). Of course, if data does not have a spatial index, no spatial index is available for the above operations. However, ArcSDE can also find the results, which is better than Oracle Spatial, which cannot be used for spatial queries without spatial indexes.

2. Comparison of primary filter/st_envintersects

Therefore, we can see from the previous section that the efficiency of spatial indexes can be obtained by comparing the "primary filter" in Oracle Spatial with the st_envintersects in ArcSDE.

The following describes the performance of SQL statement queries on the line data and area data of about 0.1 billion data records used above. The test code is as follows:

Public class performancetest {

Public static void main (string [] ARGs ){

Performancetest test = new performancetest ();

Test.exe cute ();

}

Private Static final string url = "JDBC: oracle: thin: @ 192.168.200.small: 1521: Test ";

Private Static final string user = "SDE ";

Private Static final string Password = "SDE ";

Public void execute (){

Connection conn = NULL;

Preparedstatement stmt = NULL;

Resultset rs = NULL;

Try {

Class. forname ("oracle. JDBC. Driver. oracledriver ");

Conn = drivermanager. getconnection (URL, user, password );

Conn. setautocommit (false );

String SQL = "select shape from sdo_test.streets where sdo_filter (shape, sdo_geometry (2003, null, null, sdo_elem_info_array (72.527, 41.869, 1), sdo_ordinate_array (-72.493, 41.869, -72.493, 41.891,-72.527, 41.891,-72.527, 41.869) = 'true '";

Long start = system. currenttimemillis ();

Stmt = conn. preparestatement (SQL );

Stmt. setfetchsize (1000 );

Int COUNT = 0;

Rs = stmt.exe cutequery ();

While (Rs. Next ()){

Object Shape = Rs. GetObject (1); // read the geometric object Value

Count ++;

}

Long end = system. currenttimemillis ();

System. Out. println ("query result:" + count );

System. Out. println ("query time:" + (end-Start) + "Ms ");

} Catch (exception ex ){

Ex. printstacktrace ();

} Finally {

Try {

Rs. Close ();

} Catch (sqlexception e ){

}

Try {

Stmt. Close ();

} Catch (sqlexception e ){

}

Try {

Conn. Close ();

} Catch (sqlexception e ){

}

}

}

}

Line data test results, measured in seconds. Each type corresponds to the first and second results, which indicate the results of no data block cache or cache in the database respectively:

Query range (actual number of results)	St_geometry	SDO _ Geometry
The range is about 10 elements (8)	0.357 0.156	0.218 0.203
The range is about 100 elements (114)	0.734 0.203	0.811 0.203
The range is about 1000 elements (1341)	0.920 0.250	0.951 0.234
The range is about 10000 elements (10414)	2.386 0.562	3.104 0.400

The test result of the surface data is measured in seconds. Each type corresponds to the first and second results respectively, which indicate the results of no data block cache or cache in the database:

Query range (actual number of results)	ST _ Geometry	SDO _ Geometry
Range: About 10 elements (16)	0.530 0.160	0.780 0.203
The range is about 100 elements (133)	2.636 0.343	1.841 0.220
The range is about 1000 elements (1104)	8.034 0.500	8.517 0.300
The range is about 10000 elements (10494)	29.952 1.310	43.010 0.950