Oracle Spatial 中的距离操作及效率

博客首页 » Oracle Spatial 中的距离操作及效率

发布于 22 Aug 2013 23:38
标签 blog lbs oracle spatial
query_window.gif
Oracle Spatial主要通过元数据表、空间数据字段(即sdo_Geometry字段)和空间索引来管理空间数据,并在此基础上提供一系列空间查询和空间分析的程序包,让用户进行更深层次的GIS应用开发。Oracle Spatial使用空间字段sdo_Geometry存储空间数据,用元数据表来管理具有sdo_Geometry字段的空间数据表,并采用R树索引和四叉树索引技术来提高空间查询和空间分析的速度。
通过空间索引元数据视图(USER_SDO_INDEX_METADATA)可以查到每个空间图层的空间索引名、空间索引数据表名、R-tree索引的根节点ROWID,R-tree的分支因子(又叫扇出fanout,即R-tree节点的最大子节点数)及其它相关信息。

http://nlslzf.iteye.com/blog/368598

Oracle Spatial基本操作

http://www.cnblogs.com/ora-fans/articles/1273872.html

Oracle Spatial主要通过元数据表、空间数据字段(即sdo_Geometry字段)和空间索引来管理空间数据,并在此基础上提供一系列空间查询和空间分析的程序包,让用户进行更深层次的GIS应用开发。Oracle Spatial使用空间字段sdo_Geometry存储空间数据,用元数据表来管理具有sdo_Geometry字段的空间数据表,并采用R树索引和四叉树索引技术来提高空间查询和空间分析的速度。

  1、元数据表说明

  Oracle Spatial的元数据表存储了有空间数据的数据表名称、空间字段名称、空间数据的坐标范围、坐标系以及坐标维数说明等信息。用户必须通过元数据表才能知道Oracle数据库中是否有Oracle Spatial的空间数据信息。通过元数据视图(USER_SDO_GEOM_METADATA)访问元数据表。元数据视图的基本定义为:

r_47b57f7a398961eb2e73b396.gif       

  2、空间字段解析

  Oracle Spatial的空间数据都存储在空间字段sdo_Geometry中,理解sdo_Geometry是编写Oracle Spatial程序的关键。sdo_Geometry是按照Open GIS规范定义的一个对象,其原始的创建方式如下所示。

r_2a69fe459097a920cffca397.gif         

  ① sdo_Gtype

  是一个NUMBER型的数值,用来定义存储几何对象的类型。sdo_Gtype是一个4个数字的整数,其格式为dltt,其中d表示几何对象的维数;l表示三维线性参考系统中的线性参考值,当d为3维或者4维时需要设置该值,一般情况下为空;tt为几何对象的类型,Oracle Spatial定义了7种类型的几何类型,目前,tt使用了00到07,其中08到99是Oracle Spatial保留的数字,以备将来几何对象扩展所用。

r_6d0267ddee8c57df8d102990.gif        

   ② sdo_Srid

  sdo_Srid也是一个NUMBER型的数值,它用于标识与几何对象相关的空间坐标系。如果sdo_Srid为空(null),则表示没有坐标系与该几何对象相关;如果该值不为空,则该值必须为MDSYS.CS_SRS表中SRID字段的一个值,在创建含有几何对象的表时,这个值必须加入到描述空间数据表元数据的USER_SDO_GEOM_METADATA视图的SRID字段中。对于我们通常使用国际标准的Longitude/Latitude(8307),Oracle Spatial规定,一个几何字段中的所有几何对象都必须为相同的sdo_Srid值。

  ③ sdo_Point

   sdo_Point是一个包含三维坐标X,Y,Z数值信息的对象,用于表示几何类型为点的几何对象。如果sdo_Elem_Info和SDO_ORDINATES数组都为空,则sdo_Point中的X,Y,Z为点对象的坐标值,否则,sdo_Point的值将被忽略(用NULL表示)。Oracle Spatial强烈要求用sdo_Point存储空间实体为点类型空间数据,这样可以极大的优化Oracle Spatial的存储性能和查询效率。

  ④ sdo_Elem_Info

  sdo_Elem_Info是一个可变长度的数组,每3个数作为一个元素单位,用于表示坐标是如何存储在SDO_ORDINATES数组中的。本文把组成一个元素的3个数称为3元组。一个3元组包含以下3部分的内容:
  
  ◇ SDO_STARTING_OFFSET

  SDO_STARTING_OFFSET 表明每个几何元素的第一个坐标在SDO_ORDINATES数组中的存储位置。它的值从1开始,逐渐增加。

  ◇ SDO_ETYPE

  SDO_ETYPE 用于表示几何对象中每个组成元素的几何类型。当它的值为1, 2, 1003和2003时,表明这个几何元素为简单元素。如果SDO_ETYPE为1003,表明该多边形为外环(第一个数为1表示外环),坐标值以逆时针存储;如果SDO_ETYPE为2003,表明该多边形为内环(第一个数为2表示内环),坐标值以顺时针存储。当SDO_ETYPE为4, 1005和2005时,表明这个几何元素为复杂元素。它至少包含一个3元组用以说明该复杂元素具有多少个几何简单元素。同样,1005表示多边形为外环,坐标值以逆时针存储;2005表示多边形为内环,坐标值以顺时针存储。

  ◇ SDO_INTERPRETATION

  SDO_INTERPRETATION具有两层含义,具体的作用由SDO_ETYPE是否为复杂元素决定。如果SDO_ETYPE是复杂元素(4, 1005和2005),则SDO_INTERPRETATION表示它后面有几个子3元组属于这个复杂元素。如果SDO_ETYPE是简单元素(1, 2, 1003和2003),则SDO_INTERPRETATION表示该元素的坐标值在SDO_ORDINATES中是如何排列的。

  需要注意的是,对于复杂元素来说,组成它的子元素是连续的,一个子元素的最后一个点是下一个子元素的起点。最后一个子元素的最后一个坐标要么与下一个元素的SDO_STARTING_OFFSET值减1所对应的坐标相同,要么是整个SDO_ORDINATES数组的最后一个坐标。

2006120212053442921.gif        

  ⑤ sdo_Ordinates

  SDO_ORDINATES是一个可变长度的数组,用于存储几何对象的实际坐标,是一个最大长度为1048576,类型为Number的数组。
 
  SDO_ORDINATES必须与sdo_Elem_Info数组配合使用,才具有实际意义。SDO_ORDINATES的坐标存储方式由几何对象的维数决定,如果几何对象为二维,则SDO_ORDINATES的坐标以{ x1, y1, x2, y2, …}顺序排列,如果几何对象为三维,则SDO_ORDINATES的坐标以{x1, y1, z1, x2, y2, z2, …}的顺序排列。

  3、空间索引技术:

  Oracle Spatial提供R树索引和四叉树索引两种索引机制来提高空间查询和空间分析的速度。用户需要根据不同空间数据类型创建不同的索引,当空间数据类型比较复杂时,如果选择索引类型不当,将使Oracle Spatial创建索引的过程变得非常慢。

三、将经纬度转化成地名

  目前各类位置服务LBS最终返回的都是误差允许范围内的经纬度,如GPS车载终端,手机定位等移动设备,系统通过一定的技术算法可以将其转化成具体的地名或附近的地标。或根据需要返回当前位置用户关心的周边信息:如医院、宾馆、加油站、公交车站等内容。

   1、空间数据到Oracle Spatial的导入

  当前专题空间数据库建立的过程包括技术设计、资料准备、数据获取和数据入库等内容。数据的获取常可利用现有的GIS 专业软件如GeoStar、MAPGIS、SUPERMAP、ARCGIS 等来实现,获得的数据通过某一空间数据引擎(如Easyloader)上载到Oracle 数据库中,实现利用Oracle Spatial 存储、管理空间数据。MAPORA 引擎是把MAPGIS 的明码格式通过编程实现空间数据上载Oracle Spatial 的一种方法。

  2、定位服务的整个流程

2006120212061743899.gif        

  3、Oracle Spatial关联用到的程序包:

  ◆ sdo_Geom.Relate(sdo_Geometry1, ‘MASK’, sod_Geometry2, tolerance ):用于判断一个几何体与另一个几何体的关系,我们用于判断当前点是否在某一个面(省份面、县市面、乡镇面)上。

  ◇ sdo_Geometry1,sdo_Geometry2为空间数据对应的几何对象。

  ◇ Tolerance: 容许的精度范围;

  ◇ MASK=Anyinteract/Contains/Coveredby/Covers/Disjoint/

    ○ Anyinteract: sdo_Geometry2落在sdo_Geometry1面上包括在边上。

    ○ Contains: sdo_Geometry2完全包含在sdo_Geometry1几何对象中,并且两个几何对象的边没有交叉。

    ○ Coveredby: sdo_Geometry1完全包含在sdo_Geometry2中,并且这两个几何对象的边有一个或多个点相互重叠。

    ○ Covers: sdo_Geometry2完全包含在sdo_Geometry1中,并且这两个几何对象的边有一个或多个点相互重叠。

    ○ Disjoint: 两个几何没有重叠交叉点,也没有共同的边。

    ○ Equal: 两个几何是相等的。

    ○ Inside: sdo_Geometry1完全包含在sdo_Geometry2几何对象中,并且两个几何对象的边没有交叉。

    ○ On: sdo_Geometry1的边和内部的线完全在sdo_Geometry2上。

    ○ Overlapbdydisjoint: 两个几何对象交迭,但是边没有交叉。

    ○ Overlapbdyintersect: 两个几何对象交迭,并且边有部分交叉。

    ○ Touch: 两个几何对象有共同的边,但没有交叉。

  ◆ sdo_nn( sdo_Geometry1, sdo_Geometry2, ‘sdo_num_res’, Tolerance ):用于返回几何体sdo_Geometry2周边附近有什么其他几何体集。

  ◇ sdo_Geometry1,sdo_Geometry2为空间数据对应的几何对象。

  ◇ Tolerance: 容许的精度范围;

  ◇ sdo_num_res=n: 表示返回n个几何体,=1表示只返回一个。

  ◆ sdo_Geom.Within_Distance(sdo_Geometry1, Distance, sdo_Geometry2, Tolerance, 'unit' )用于判断几何体sdo_Geometry2在指定的距离Distance内周边附近有什么其他几何体集。

  ◇ sdo_Geometry1,sdo_Geometry2为空间数据对应的几何对象。

  ◇ Tolerance: 容许的精度范围;

  ◇ Distance: 指定的距离;

  ◇ Unit: 用于表示距离的单位,可能是Unit=M/ Unit=KM等长度单位,但必须是SDO_DIST_UNITS表中列举出来的单位之一。

  4、返回的信息:

  返回的信息根据业务需要可以返回空间信息关联到的所有信息,然后用三段论方式组合成一段话,通过SMS等方式返回到移动终端。

  ◆ 可以是当前位置的地名、城市地标、自定义地标、道路名称等位置信息;

  ◆ 可以是当前位置的周边信息:医院、宾馆、加油站、公交车站等;

四、结束语

   采用Oracle Spatial 存储、管理空间数据,易于解决数据共享、分布式处理、网络通信、开放式开发、并发控制、网络化集成、跨平台运行及数据安全恢复机制等方面的难题。

  可以直接通过Oracle Spatial 具有强大空间分析 进行空间分析:最短路径分析、连通性分析、交通系统中最少换乘算法及其实现等。

  LBS业务具有广阔的发展前景,目前商用的LBS业争也达到了相当程度的渗透率。LBS业务涉及的领域很多,需要移动通信领域与GIS领域相结合来共同实现。我国目前很重视GIS领域的发展,国内已经有相当多具有一定实力和发展潜力的GIS厂商。相信随着今后LBS业务需求的不断增长,以及我国GIS领域的不断发展完善,LBS业务将得到更广泛的应用。

Indexing and Querying Spatial Data

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_index_query.htm

After you have loaded spatial data (discussed in Chapter 3), you should create a spatial index on it to enable efficient query performance using the data. This chapter describes how to:

Create a spatial index (see Section 4.1)

Query spatial data efficiently, based on an understanding of the Oracle Spatial query model and primary and secondary filtering (see Section 4.2)

4.1 Creating a Spatial Index

Once data has been loaded into the spatial tables through either bulk or transactional loading, a spatial index (that is, a spatial R-tree index) must be created on the tables for efficient access to the data. For example, the following statement creates a spatial index named territory_idx using default values for all parameters:

CREATE INDEX territory_idx ON territories (territory_geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

For detailed information about options for creating a spatial index, see the documentation for the CREATE INDEX statement in Chapter 10.

If the index creation does not complete for any reason, the index is invalid and must be deleted with the DROP INDEX <index_name> [FORCE] statement.

Spatial indexes can be built on two, three, or four dimensions of data. The default number of dimensions is two, but if the data has more than two dimensions, you can use the sdo_indx_dims parameter keyword to specify the number of dimensions on which to build the index. However, if a spatial index has been built on more than two dimensions of a layer, the only spatial operator that can be used against that layer is SDO_FILTER (the primary filter or index-only query), which considers all dimensions. The SDO_RELATE, SDO_NN, and SDO_WITHIN_DISTANCE operators are disabled if the index has been built on more than two dimensions.

If the rollback segment is not large enough, an attempt to create a spatial index will fail. The rollback segment should be 100*n bytes, where n is the number of rows of data to be indexed. For example, if the table contains 1 million (1,000,000) rows, the rollback segment size should be 100,000,000 (100 million) bytes.

To ensure an adequate rollback segment, or if you have tried to create a spatial index and received an error that a rollback segment cannot be extended, review (or have a DBA review) the size and structure of the rollback segments. Create a public rollback segment of the appropriate size, and place that rollback segment online. In addition, ensure that any small inappropriate rollback segments are placed offline during large spatial index operations. For information about performing these operations on a rollback segment, see Oracle Database Administrator's Guide.

The system parameter SORT_AREA_SIZE affects the amount of time required to create the index. The SORT_AREA_SIZE value is the maximum amount, in bytes, of memory to use for a sort operation. The optimal value depends on the database size, but a good guideline is to make it at least 1 million bytes when you create a spatial index. To change the SORT_AREA_SIZE value, use the ALTER SESSION statement. For example, to change the value to 20 million bytes:

ALTER SESSION SET SORT_AREA_SIZE = 20000000;

The tablespace specified with the tablespace keyword in the CREATE INDEX statement (or the default tablespace if the tablespace keyword is not specified) is used to hold both the index data table and some transient tables that are created for internal computations. If you specify WORK_TABLESPACE as the tablespace, the transient tables are stored in the work tablespace.

For large tables (over 1 million rows), a temporary tablespace may be needed to perform internal sorting operations. The recommended size for this temporary tablespace is 100*n bytes, where n is the number of rows in the table, up to a maximum requirement of 1 gigabyte of temporary tablespace.

To estimate the space that will be needed to create a spatial index, use the SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE function, described in Chapter 19.

4.1.1 Indexing Geodetic Data

To take full advantage of Spatial features, you must index geodetic data using a geodetic R-tree index. Geodetic data consists of geometries that have geodetic SDO_SRID values, reflecting the fact that they are based on a geodetic coordinate system (such as using longitude and latitude) as opposed to a flat or projected plane coordinate system. (Chapter 6 explains coordinate systems and related concepts.) A geodetic index is one that provides the full range of Spatial features with geodetic data. Thus, it is highly recommended that you use a geodetic index with geodetic data.

Only R-tree indexes can be geodetic indexes. Quadtree indexes cannot be geodetic indexes. If you create an R-tree or quadtree index and specify 'geodetic=false' in the CREATE INDEX statement, the index is non-geodetic. The following notes and restrictions apply to non-geodetic indexes:

If you create a non-geodetic index on geodetic data, you cannot use the unit parameter with the SDO_WITHIN_DISTANCE operator or the SDO_NN_DISTANCE ancillary operator with the SDO_NN operator.

If you create a non-geodetic index on projected data that has a projected SDO_SRID value, you can use the full range of Spatial features.

If you create a non-geodetic index on projected data that has a null SDO_SRID value, you cannot use the unit parameter with the SDO_WITHIN_DISTANCE operator or the SDO_NN_DISTANCE ancillary operator with the SDO_NN operator.

For additional information, see the Usage Notes about the geodetic parameter for the CREATE INDEX statement in Chapter 10.

4.1.2 Constraining Data to a Geometry Type

When you create or rebuild a spatial index, you can ensure that all geometries that are in the table or that are inserted later are of a specified geometry type. To constrain the data to a geometry type in this way, use the layer_gtype keyword in the PARAMETERS clause of the CREATE INDEX or ALTER INDEX REBUILD statement, and specify a value from the Geometry Type column of Table 2-1 in Section 2.2.1. For example, to constrain spatial data in a layer to polygons:

CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('layer_gtype=POLYGON');

The geometry types in Table 2-1 are considered as a hierarchy when data is checked:

The MULTI forms include the regular form also. For example, specifying 'layer_gtype=MULTIPOINT' allows the layer to include both POINT and MULTIPOINT geometries.

COLLECTION allows the layer to include all types of geometries.

4.1.3 Creating a Cross-Schema Index

You can create a spatial index on a table that is not in your schema. Assume that user B wants to create a spatial index on column GEOMETRY in table T1 under user A's schema. Follow these steps:

Connect to the database as a privileged user (for example, as SYSTEM), and execute the following statement:

GRANT create table, create sequence to B;

Connect as a privileged user or as user A (or have user A connect), and execute the following statement:

GRANT select, index on A.T1 to B;

Connect as user B and execute a statement such as the following:

CREATE INDEX t1_spatial_idx on A.T1(geometry)
INDEXTYPE IS mdsys.spatial_index;
4.1.4 Using Partitioned Spatial Indexes

You can create a partitioned spatial index on a partitioned table. This section describes usage considerations specific to Oracle Spatial. For a detailed explanation of partitioned tables and partitioned indexes, see Oracle Database Administrator's Guide.

A partitioned spatial index can provide the following benefits:

Reduced response times for long-running queries, because partitioning reduces disk I/O operations

Reduced response times for concurrent queries, because I/O operations run concurrently on each partition

Easier index maintenance, because of partition-level create and rebuild operations

Indexes on partitions can be rebuilt without affecting the queries on other partitions, and storage parameters for each local index can be changed independent of other partitions.

Parallel query on multiple partition searching

The degree of parallelism is the value from the DEGREE column in the row for the index in the USER_INDEXES view (that is, the value specified or defaulted for the PARALLEL keyword with the CREATE INDEX, ALTER INDEX, or ALTER INDEX REBUILD statement).

Improved query processing in multiprocessor system environments

In a multiprocessor system environment, if a spatial operator is invoked on a table with partitioned spatial index and if multiple partitions are involved in the query, multiple processors can be used to evaluate the query. The number of processors used is determined by the degree of parallelism and the number of partitions used in evaluating the query.

The following restrictions apply to spatial index partitioning:

The partition key for spatial tables must be a scalar value, and must not be a spatial column.

Only range partitioning is supported on the underlying table. Hash and composite partitioning are not currently supported for partitioned spatial indexes.

To create a partitioned spatial index, you must specify the LOCAL keyword. For example:

CREATE INDEX counties_idx ON counties(geometry)
INDEXTYPE IS MDSYS.SPATIAL_INDEX LOCAL;

In this example, the default values are used for the number and placement of index partitions, namely:

Index partitioning is based on the underlying table partitioning. For each table partition, a corresponding index partition is created.

Each index partition is placed in the default tablespace.

If you do specify parameters for individual partitions, the following considerations apply:

The storage characteristics for each partition can be the same or different for each partition. If they are different, it may enable parallel I/O (if the tablespaces are on different disks) and may improve performance.

The sdo_indx_dims value must be the same for all partitions.

The layer_gtype parameter value (see Section 4.1.2) used for each partition may be different.

To override the default partitioning values, use a CREATE INDEX statement with the following general format:

CREATE INDEX <indexname> ON <table>(<column>)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
[PARAMETERS ('<spatial-params>, <storage-params>')] LOCAL
[( PARTITION <index_partition>
PARAMETERS ('<spatial-params>, <storage-params>')
[, PARTITION <index_partition>
PARAMETERS ('<spatial-params>, <storage-params>')]
)]

Queries can operate on partitioned tables to perform the query on only one partition. For example:

SELECT * FROM counties PARTITION(p1)
WHERE …<some-spatial-predicate>;

Querying on a selected partition may speed up the query and also improve overall throughput when multiple queries operate on different partitions concurrently.

When queries use a partitioned spatial index, the semantics (meaning or behavior) of spatial operators and functions is the same with partitioned and nonpartitioned indexes, except in the case of SDO_NN (nearest neighbor). With SDO_NN, the requested number of geometries is returned for each partition that is affected by the query. For example, if you request the 5 closest restaurants to a point and the spatial index has 4 partitions, SDO_NN returns up to 20 (5*4) geometries. In this case, you must use the ROWNUM pseudocolumn (here, WHERE ROWNUM <=5) to return the 5 closest restaurants. See the description of the SDO_NN operator in Chapter 11 for more information.

4.1.5 Exchanging Partitions Including Indexes

You can use the ALTER TABLE statement with the EXCHANGE PARTITION … INCLUDING INDEXES clause to exchange a spatial table partition and its index partition with a corresponding table and its index. For information about exchanging partitions, see the description of the ALTER TABLE statement in Oracle Database SQL Reference.

This feature can help you to operate more efficiently in a number of situations, such as:

Bringing data into a partitioned table and avoiding the cost of index re-creation.

Managing and creating partitioned indexes. For example, the data could be divided into multiple tables. The index for each table could be built one after the other to minimize the memory and tablespace resources needed during index creation. Alternately, the indexes could be created in parallel in multiple sessions. The tables (along with the indexes) could then be exchanged with the partitions of the original data table.

Managing offline insert operations. New data can be stored in a temporary table and periodically exchanged with a new partition (for example, in a database with historical data).

To exchange partitions including indexes with spatial data and indexes, the two spatial indexes (one on the partition, the other on the table) must be of compatible types. Specifically:

Both indexes must have the same dimensionality (sdo_indx_dims value).

Both indexes must be either geodetic or non-geodetic. (Geodetic and non-geodetic indexes are explained in Section 4.1.1.)

If the indexes are not compatible, an error is raised. The table data is exchanged, but the indexes are not exchanged and the indexes are marked as failed. To use the indexes, you must rebuild them.

4.1.6 Export and Import Considerations with Spatial Indexes and Data

If you use the Export utility to export tables with spatial data, the behavior of the operation depends on whether or not the spatial data has been spatially indexed:

If the spatial data has not been spatially indexed, the table data is exported. However, you must update the USER_SDO_GEOM_METADATA view with the appropriate information on the target system.

If the spatial data has been spatially indexed, the table data is exported, the appropriate information is inserted into the USER_SDO_GEOM_METADATA view on the target system, and the spatial index is built on the target system. However, if the insertion into the USER_SDO_GEOM_METADATA view fails (for example, if there is already a USER_SDO_GEOM_METADATA entry for the spatial layer), the spatial index is not built.

If you use the Import utility to import data that has been spatially indexed, the following considerations apply:

If the index on the exported data was created with a TABLESPACE clause and if the specified tablespace does not exist in the database at import time, the index is not built. (This is different from the behavior with other Oracle indexes, where the index is created in the user's default tablespace if the tablespace specified for the original index does not exist at import time.)

If the import operation must be done by a privileged database user, and if the FROMUSER and TOUSER format is used, the TOUSER user must be granted the CREATE TABLE and CREATE SEQUENCE privileges before the import operation, as shown in the following example:

sqlplus system/<password>
SQL> grant CREATE TABLE, CREATE SEQUENCE to CHRIS;
SQL> exit;
imp system/<password> file=spatl_data.dmp fromuser=SCOTT touser=CHRIS

For information about using the Export and Import utilities, see Oracle Database Utilities.

4.1.7 Distributed Transactions and Spatial Index Consistency

In a distributed transaction, different branches of the transaction can execute in different sessions. The branches can detach from their current session and migrate to another within the transaction scope. To maintain the consistency of Spatial indexes in distributed transactions, you must follow the usage guidelines in this section.

When the first insert, update, or delete operation on a spatial table (one with a spatial index) is performed in a distributed transaction, all subsequent insert, update, or delete operations on the table, as well as any prepare to commit operation (the first branch to prepare a commit), in the transaction should happen in the same session as the first operation. The branches performing these subsequent operations will first have to connect to the session in which the first operation was performed.

For more information about distributed transactions, see Oracle Database Administrator's Guide.

4.2 Querying Spatial Data

This section describes how the structures of a Spatial layer are used to resolve spatial queries and spatial joins.

Spatial uses a two-tier query model with primary and secondary filter operations to resolve spatial queries and spatial joins, as explained in Section 1.6. The term two-tier indicates that two distinct operations are performed to resolve queries. If both operations are performed, the exact result set is returned.

You cannot append a database link (dblink) name to the name of a spatial table in a query if a spatial index is defined on that table.

If a spatial index is created in a database that was created using the UTF8 character set, spatial queries that use the spatial index will fail if the system parameter NLS_LENGTH_SEMANTICS is set to CHAR. For spatial queries to succeed in this case, the NLS_LENGTH_SEMANTICS parameter must be set to BYTE (its default value).

4.2.1 Spatial Query

In a spatial R-tree index, each geometry is represented by its minimum bounding rectangle (MBR), as explained in Section 1.7.1. Consider the following layer containing several objects in Figure 4-1. Each object is labeled with its geometry name (geom_1 for the line string, geom_2 for the four-sided polygon, geom_3 for the triangular polygon, and geom_4 for the ellipse), and the MBR around each object is represented by a dashed line.

Figure 4-1 Geometries with MBRs
query_mbrs.gif
Description of Figure 4-1 follows
Description of "Figure 4-1 Geometries with MBRs"

A typical spatial query is to request all objects that lie within a query window, that is, a defined fence or window. A dynamic query window refers to a rectangular area that is not defined in the database, but that must be defined before it is used. Figure 4-2 shows the same geometries as in Figure 4-1, but adds a query window represented by the heavy dotted-line box.

Figure 4-2 Layer with a Query Window
query_window.gif
Description of Figure 4-2 follows
Description of "Figure 4-2 Layer with a Query Window"

In Figure 4-2, the query window covers parts of geometries geom_1 and geom_2, as well as part of the MBR for geom_3 but none of the actual geom_3 geometry. The query window does not cover any part of the geom_4 geometry or its MBR.

4.2.1.1 Primary Filter Operator

The SDO_FILTER operator, described in Chapter 11, implements the primary filter portion of the two-step process involved in the Oracle Spatial query processing model. The primary filter uses the index data to determine only if a set of candidate object pairs may interact. Specifically, the primary filter checks to see if the MBRs of the candidate objects interact, not whether the objects themselves interact. The SDO_FILTER operator syntax is as follows:

SDO_FILTER(geometry1 SDO_GEOMETRY, geometry2 SDO_GEOMETRY, param VARCHAR2)

In the preceding syntax:

geometry1 is a column of type SDO_GEOMETRY in a table. This column must be spatially indexed.

geometry2 is an object of type SDO_GEOMETRY. This object may or may not come from a table. If it comes from a table, it may or may not be spatially indexed.

param is an optional string of type VARCHAR2. It can specify either or both of the min_resolution and max_resolution keywords.

The following examples perform a primary filter operation only (with no secondary filter operation). They will return all the geometries shown in Figure 4-2 that have an MBR that interacts with the query window. The result of the following examples are geometries geom_1, geom_2, and geom_3.

Example 4-1 performs a primary filter operation without inserting the query window into a table. The window will be indexed in memory and performance will be very good.

Example 4-1 Primary Filter with a Temporary Query Window

SELECT A.Feature_ID FROM TARGET A WHERE sdo_filter(A.shape, SDO_geometry(2003,NULL,NULL,
SDO_elem_info_array(1,1003,3),
SDO_ordinate_array(x1,y1, x2,y2))
) = 'TRUE';
In Example 4-1, (x1,y1) and (x2,y2) are the lower-left and upper-right corners of the query window.

In Example 4-2, a transient instance of type SDO_GEOMETRY was constructed for the query window instead of specifying the window parameters in the query itself.

Example 4-2 Primary Filter with a Transient Instance of the Query Window

SELECT A.Feature_ID FROM TARGET A WHERE sdo_filter(A.shape, :theWindow) = 'TRUE';

Example 4-3 assumes the query window was inserted into a table called WINDOWS, with an ID of WINS_1.

Example 4-3 Primary Filter with a Stored Query Window

SELECT A.Feature_ID FROM TARGET A, WINDOWS B WHERE B.ID = 'WINS_1' AND sdo_filter(A.shape, B.shape) = 'TRUE';
If the B.SHAPE column is not spatially indexed, the SDO_FILTER operator indexes the query window in memory and performance is very good.

4.2.1.2 Primary and Secondary Filter Operator

The SDO_RELATE operator, described in Chapter 11, performs both the primary and secondary filter stages when processing a query. The secondary filter ensures that only candidate objects that actually interact are selected. This operator can be used only if a spatial index has been created on two dimensions of data. The syntax of the SDO_RELATE operator is as follows:

SDO_RELATE(geometry1 SDO_GEOMETRY,
geometry2 SDO_GEOMETRY,
param VARCHAR2)

In the preceding syntax:

geometry1 is a column of type SDO_GEOMETRY in a table. This column must be spatially indexed.

geometry2 is an object of type SDO_GEOMETRY. This object may or may not come from a table. If it comes from a table, it may or may not be spatially indexed.

param is a quoted string with the mask keyword and a valid mask value, and optionally either or both of the min_resolution and max_resolution keywords, as explained in the documentation for the SDO_RELATE operator in Chapter 11.

The following examples perform both primary and secondary filter operations. They return all the geometries in Figure 4-2 that lie within or overlap the query window. The result of these examples is objects geom_1 and geom_2.

Example 4-4 performs both primary and secondary filter operations without inserting the query window into a table. The window will be indexed in memory and performance will be very good.

Example 4-4 Secondary Filter Using a Temporary Query Window

SELECT A.Feature_ID FROM TARGET A
WHERE sdo_relate(A.shape, SDO_geometry(2003,NULL,NULL,
SDO_elem_info_array(1,1003,3),
SDO_ordinate_array(x1,y1, x2,y2)), 'mask=anyinteract') = 'TRUE';

In Example 4-4, (x1,y1) and (x2,y2) are the lower-left and upper-right corners of the query window.

Example 4-5 assumes the query window was inserted into a table called WINDOWS, with an ID value of WINS_1.

Example 4-5 Secondary Filter Using a Stored Query Window

SELECT A.Feature_ID FROM TARGET A, WINDOWS B WHERE B.ID = 'WINS_1' AND sdo_relate(A.shape, B.shape,
'mask=anyinteract') = 'TRUE';
If the B.SHAPE column is not spatially indexed, the SDO_RELATE operator indexes the query window in memory and performance is very good.

4.2.1.3 Within-Distance Operator

The SDO_WITHIN_DISTANCE operator, described in Chapter 11, is used to determine the set of objects in a table that are within n distance units from a reference object. This operator can be used only if a spatial index has been created on two dimensions of data. The reference object may be a transient or persistent instance of SDO_GEOMETRY (such as a temporary query window or a permanent geometry stored in the database). The syntax of the operator is as follows:

SDO_WITHIN_DISTANCE(geometry1 SDO_GEOMETRY,
aGeom SDO_GEOMETRY,
params VARCHAR2);

In the preceding syntax:

geometry1 is a column of type SDO_GEOMETRY in a table. This column must be spatially indexed.

aGeom is an instance of type SDO_GEOMETRY.

params is a quoted string of keyword value pairs that determines the behavior of the operator. See the SDO_WITHIN_DISTANCE operator in Chapter 11 for a list of parameters.

The following example selects any objects within 1.35 distance units from the query window:

SELECT A.Feature_ID
FROM TARGET A
WHERE SDO_WITHIN_DISTANCE( A.shape, :theWindow, 'distance=1.35') = 'TRUE';

The distance units are based on the geometry coordinate system in use. If you are using a geodetic coordinate system, the units are meters. If no coordinate system is used, the units are the same as for the stored data.

The SDO_WITHIN_DISTANCE operator is not suitable for performing spatial joins. That is, a query such as Find all parks that are within 10 distance units from coastlines will not be processed as an index-based spatial join of the COASTLINES and PARKS tables. Instead, it will be processed as a nested loop query in which each COASTLINES instance is in turn a reference object that is buffered, indexed, and evaluated against the PARKS table. Thus, the SDO_WITHIN_DISTANCE operation is performed n times if there are n rows in the COASTLINES table.

For non-geodetic data, there is an efficient way to accomplish a spatial join that involves buffering all geometries of a layer. This method does not use the SDO_WITHIN_DISTANCE operator. First, create a new table COSINE_BUFS as follows:

CREATE TABLE cosine_bufs UNRECOVERABLE AS
SELECT SDO_BUFFER (A.SHAPE, B.DIMINFO, 1.35)
FROM COSINE A, USER_SDO_GEOM_METADATA B
WHERE TABLE_NAME='COSINES' AND COLUMN_NAME='SHAPE';
Next, create a spatial index on the SHAPE column of COSINE_BUFS. Then you can perform the following query:

SELECT /*+ ordered */ a.gid, b.gid
FROM TABLE(SDO_JOIN('PARKS', 'SHAPE',
'COSINE_BUFS', 'SHAPE',
'mask=ANYINTERACT')) c,
parks a,
cosine_bufs b
WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid;
4.2.1.4 Nearest Neighbor Operator

The SDO_NN operator, described in Chapter 11, is used to identify the nearest neighbors for a geometry. This operator can be used only if a spatial index has been created on two dimensions of data. The syntax of the operator is as follows:

SDO_NN(geometry1 SDO_GEOMETRY,
geometry2 SDO_GEOMETRY,
param VARCHAR2
[, number NUMBER]);

In the preceding syntax:

geometry1 is a column of type SDO_GEOMETRY in a table. This column must be spatially indexed.

geometry2 is an instance of type SDO_GEOMETRY.

param is a quoted string of keyword-value pairs that can determine the behavior of the operator, such as how many nearest neighbor geometries are returned. See the SDO_NN operator in Chapter 11 for information about this parameter.

number is the same number used in the call to SDO_NN_DISTANCE. Use this only if the SDO_NN_DISTANCE ancillary operator is included in the call to SDO_NN. See the SDO_NN operator in Chapter 11 for information about this parameter.

The following example finds the two objects from the SHAPE column in the COLA_MARKETS table that are closest to a specified point (10,7). (Note the use of the optimizer hint in the SELECT statement, as explained in the Usage Notes for the SDO_NN operator in Chapter 11.)

SELECT /*+ INDEX(cola_markets cola_spatial_idx) */
c.mkt_id, c.name FROM cola_markets c WHERE SDO_NN(c.shape,
SDO_geometry(2001, NULL, SDO_point_type(10,7,NULL), NULL,
NULL), 'sdo_num_res=2') = 'TRUE';
4.2.1.5 Spatial Functions

Spatial also supplies functions for determining relationships between geometries, finding information about single geometries, changing geometries, and combining geometries. These functions all take into account two dimensions of source data. If the output value of these functions is a geometry, the resulting geometry will have the same dimensionality as the input geometry, but only the first two dimensions will accurately reflect the result of the operation.

4.2.2 Spatial Join

A spatial join is the same as a regular join except that the predicate involves a spatial operator. In Spatial, a spatial join takes place when you compare all geometries of one layer to all geometries of another layer. This is unlike a query window, which compares a single geometry to all geometries of a layer.

Spatial joins can be used to answer questions such as Which highways cross national parks?

The following table structures illustrate how the join would be accomplished for this example:

PARKS( GID VARCHAR2(32), SHAPE SDO_GEOMETRY)
HIGHWAYS( GID VARCHAR2(32), SHAPE SDO_GEOMETRY)

To perform a spatial join, use the SDO_JOIN operator, which is described in Chapter 11. The following spatial join query, to list the GID column values of highways and parks where a highway interacts with a park, performs a primary filter operation only ('mask=FILTER'), and thus it returns only approximate results:

SELECT /*+ ordered */ a.gid, b.gid
FROM TABLE(SDO_JOIN('PARKS', 'SHAPE',
'HIGHWAYS', 'SHAPE',
'mask=FILTER')) c,
parks a,
highways b
WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid;

The following spatial join query requests the same information as in the preceding example, but it performs both primary and secondary filter operations ('mask=ANYINTERACT'), and thus it returns exact results:

SELECT /*+ ordered */ a.gid, b.gid
FROM TABLE(SDO_JOIN('PARKS', 'SHAPE',
'HIGHWAYS', 'SHAPE',
'mask=ANYINTERACT')) c,
parks a,
highways b
WHERE c.rowid1 = a.rowid AND c.rowid2 = b.rowid;
4.2.3 Cross-Schema Operator Invocation

You can invoke spatial operators on an indexed table that is not in your schema. Assume that user A has a spatial table T1 (with index table IDX_TAB1) with a spatial index defined, that user B has a spatial table T2 (with index table IDX_TAB2) with a spatial index defined, and that user C wants to invoke operators on tables in one or both of the other schemas.

If user C wants to invoke an operator only on T1, user C must perform the following steps:

Connect as user A and execute the following statements:

GRANT select on T1 to C;
GRANT select on idx_tab1 to C;

Connect as user C and execute a statement such as the following:

SELECT a.gid
FROM T1 a
WHERE sdo_filter(a.geometry, :theGeometry) = 'TRUE';

If user C wants to invoke an operator on both T1 and T2, user C must perform the following steps:

Connect as user A and execute the following statements:

GRANT select on T1 to C;
GRANT select on idx_tab1 to C;

Connect as user B and execute the following statements:

GRANT select on T2 to C;
GRANT select on idx_tab2 to C;

Connect as user C and execute a statement such as the following:

SELECT a.gid
FROM T1 a, T2 b
WHERE b.gid = 5 AND
sdo_filter(a.geometry, b.geometry) = 'TRUE';

Oracle Spatial中的空间索引

http://cryolite.iteye.com/blog/491453

博客分类: 我的备忘录
Oracle工作
Oracle Spatial空间索引

Oracle Spatial可对空间数据进行R-tree索引,每个空间图层(Spatial Layer)的空间索引元信息都可以在USER_SDO_INDEX_METADATA视图中找到。
具体的索引数据保存在MDRT字段开头的表中, 每个空间图层都会对应一个索引表(表的格式是MDRT_[…]$),空间索引表中的主要数据是MBR

通过空间索引元数据视图(USER_SDO_INDEX_METADATA)可以查到每个空间图层的空间索引名、空间索引数据表名、R-tree索引的根节点ROWID,R-tree的分支因子(又叫扇出fanout,即R-tree节点的最大子节点数)及其它相关信息。

索引的创建:
1. R-tree索引:
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

2. 四叉树索引:
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('SDO_LEVEL=8');

在Oracle Spatial中,四叉树索引不如R-tree索引,因为:
1. 只能对二维非geodesic数据创建四叉树索引;
2. 用户自己要对四叉树索引的参数进行调整,而R-tree索引的参数比较好调,也更自动。

关于四叉树索引可以参考文档: Oracle Spatial Quadtree Indexing

索引的效率:
1. 如果表空间指定为ASSM表空间(user_tablespaces表中segment_space_management为AUTO即是),索引中的LOB数据将是SECUREFILE LOB的,这比一般的BASIC LOB快。

2. 空间索引创建过程中会临时产生许多工作表,创建完后会删除这些工作表,这一过程中(大量不同大小的表的创建和删除,其数据量大约是要索引的表的200-300倍)会使得表空间(tablespace)产生许多碎片从而影响表空间效率,可以为这些工作表指定独立的表空间(即指定WORK_TABLESPACE)避免这一效率损失:
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('WORK_TABLESPACE=SYSAUX');
注意工作表空间不得使用temporacy表空间。如果不指定工作表空间,缺省同要索引之表的表空间。

3. 要索引的geometry列如果都是相同形状的几何体(例如都是点),在创建索引时指定要索引空间图层中的几何体类型会提高查询速度:
Plsql代码 收藏代码
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('LAYER_GTYPE=POINT');

4. 如果需要在事务中进行大量的数据删除/插入时可以考虑为空间索引设置SDO_DML_BATCH_SIZE参数,在事务中删除/插入数据后并不会马上就更新索引,而是在事务提交时、或者删除/插入的数量达到某个批量值时统一索引更新,这个值(即SDO_DML_BATCH_SIZE)缺省为1000,对于大多数操作这个值足够了。但是如果你的表在工作中会有大量删除/插入操作,那么可以考虑将这个值设得更大以提高效率,代价是更多的内存和系统资源消耗:
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('SDO_DML_BATCH_SIZE=5000');

这个值最好设在5000-10000之间。

另一个提高大量插入删除效率的方法是在大批量操作之前删除索引,之后再重建索引

5. 对于一个有N个记录的表创建空间索引:
1). R-tree的空间索引表大概需要100×3N个字节的存储空间;
2). 在R-tree空间索引创建过程中,在临时的数据表空间中需要200×3N到300×3N字节的额外存储空间。
可以通过下列语句估算为一个空间图层创建R-tree索引需要的存储空间大小:
SELECT sdo_tune.estimate_rtree_index_size('SPATIAL', 'CUSTOMERS', 'LOCATION') sz FROM dual;
sz
---
1

结果为1,表示
1). 索引数据需要1M字节的存储空间,这是索引数据本身所需的存储空间,此外在索引创建过程中2到3倍这个值的存储空间;
2). 当创建空间索引时,指定session参数SORT_AREA_SIZE为这个值(1MB)会优化索引创建过程。

6. 如果使用SDO_NN空间操作符的效率问题
1) SDO_NN空间操作时,空间索引会遮蔽其它索引,因此不要期望使用SDO_NN空间操作符时在WHERE语句中加入更多的限制条件会加快查询速度,这时对SDO_BATCH_SIZE参数进行微调有可能会提高查询效率。
SELECT ct.id, ct.name, ct.customer_grade
FROM competitors comp, customers ct
WHERE comp.id=1
AND ct.customer_grade='GOLD'
AND SDO_NN(ct.location, comp.location)='TRUE'
AND ROWNUM<=5
ORDER BY ct.id;
尽管customer_grade字段有索引,但是这不会加快空间查询的速度,执行时可能先找出10条最近的记录,看是否是'GOLD'的,如果不是则找出接下来10条最近的记录,。。。。。直到所有'GOLD'的用户有5条为止。

如果预计5条'GOLD'用户肯定在前100个最近的记录里,则通过设置SDO_BATCH_SIZE参数为100可以加快查询速度:
SELECT ct.id, ct.name, ct.customer_grade
FROM competitors comp, customers ct
WHERE comp.id=1
AND ct.customer_grade='GOLD'
AND SDO_NN(ct.location, comp.location, 'SDO_BATCH_SIZE=100' )='TRUE'
AND ROWNUM<=5
ORDER BY ct.id;

如果你不知道SDO_BATCH_SIZE该设为多少,就设为0,索引会在使用合适的内部值。

2) 限定SDO_NN返回的记录数量会加快查询的速度,这通过调整SDO_NUM_RES设置:
SELECT ct.id, ct.name, ct.customer_grade
FROM competitors comp, customers ct
WHERE comp.id=1
AND SDO_NN(ct.location, comp.location, 'SDO_NUM_RES=5')='TRUE' ;
效果与这个相同:
SELECT ct.id, ct.name, ct.customer_grade
FROM competitors comp, customers ct
WHERE comp.id=1
AND SDO_NN(ct.location, comp.location)='TRUE'
AND ROWNUM<=5
ORDER BY ct.id;

空间索引与分区
上面建立的索引是全表范围内的,是“全局”索引,在对数据库表进行分区后,可以在每个分区上建立一个“本地(Local)”索引。
进行带空间操作符的空间查询时,Oracle会在每个分区的索引上进行查询,然后将各个分区上的查询结果汇集,最后将结果返回给用户。因此,分区索引并不总能加快查询速度。
分区也会影响查询返回的结果:
例如SDO_NN操作符中的SDO_NUM_RES参数会指定符合条件的结果数量,但是如果是分区索引的话,则在每个分区上都得满足此参数,如果有3个分区,那么最终返回的结果数量将是SDO_NUM_RES×3,而不是SDO_NUM_RES。

注:以上讨论的分区是由限制的,只能是range分区,list分区和哈希分区都不行

空间索引与并行
创建索引时可以指定索引并行,例如:
Plsql代码 收藏代码
CREATE INDEX customers_sidx ON customers(location)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARALLEL [parallel_degree];

并行参数parallel_degree是可选的,它定义了并行度,如果大于1,则索引创建时会并行进行。

但查询时无法指定并行,查询的并行实际上与数据库表的分区有关,也就是说查询会在每个分区上并行的进行。因此设定表的并行度并分区会提高使用空间索引的空间分析操作的性能。
Plsql代码 收藏代码
ALTER TABLE customers PARALLEL 2 ;

空间索引的重建
在对表进行大量(大约30%)删除后,对空间索引进行重建可以提高未来数据的查询效率:
Plsql代码 收藏代码
ALTER INDEX customers_sidx REBUILD ;

重建时也可以指定参数:
Plsql代码 收藏代码
ALTER INDEX customers_sidx REBUILD
PARAMETERS ('layer_gtype=POINT');

注意:
1. ALTER INDEX是一个DDL语句,因此会导致当前事务的提交;
2. 空间索引重建是个耗时操作,它以排他锁的方式阻止了在空间索引上的其它操作,从而造成相应空间查询操作的阻塞,不过可以指定ONLINE关键字避免这种堵塞发生:
Plsql代码 收藏代码
ALTER INDEX customers_sidx REBUILD ONLINE
PARAMETERS ('layer_gtype=POINT');

ONLINE重建索引的过程如图所示:
59e4dc54-2e39-36cf-83c1-57b13522f783.jpg
重建的索引数据保存到新索引表上,重建时旧的索引表仍在,旧的查询在旧索引上,因此重建过程不会影响正在进行的查询,在新索引建好后再切换到新的索引表上。

要了解更多关于Oracle Spatial空间索引设计原理:
“Efficient Processing of Large Spatial Queries using Interior Approximations.” Proceedings of the 7th International Symposium on Spatial and Temporal Databases (SSTD), 2001.

Oracle_spatial的空间操作符介绍

http://www.cnblogs.com/lanzi/archive/2010/12/28/1918806.html

2010年12月28日

空间操作符
一、主要空间操作符

1、SDO_FILTER

SDO_FILTER(geometry1, geometry2, param);

判断两个几何体是否有相交

SELECT c.mkt_id, c.name

FROM cola_markets c

WHERE SDO_FILTER(c.shape,

SDO_GEOMETRY(2003,

NULL,

NULL,

SDO_ELEM_INFO_ARRAY(1, 1003, 3),

SDO_ORDINATE_ARRAY(4, 6, 8, 8))) = 'TRUE';

2、SDO_JOIN

SDO_JOIN(table_name1, column_name1, table_name2, column_name2, params,

preserve_join_order) RETURN SDO_ROWIDSET;

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;

3、SDO_NN

SDO_NN(geometry1, geometry2, param [, number]);

在指定的距离内,按顺序返回离的最近的。

性能调优参数

sdo_num_res 指定返回离指定点最近的两个市场

sdo_batch_size 指定了一次批量提取多少条记录进行对比

SELECT /*+ INDEX(c cola_spatial_idx) */

c.mkt_id, c.name

FROM cola_markets c

WHERE SDO_NN(c.shape,

sdo_geometry(2001,

NULL,

sdo_point_type(10, 7, NULL),

NULL,

NULL),

'sdo_num_res=2') = 'TRUE';

SELECT /*+ INDEX(c cola_spatial_idx) */

c.mkt_id, c.name

FROM cola_markets c

WHERE SDO_NN(c.shape,

sdo_geometry(2001,

NULL,

sdo_point_type(10, 7, NULL),

NULL,

NULL),

'sdo_batch_size=3') = 'TRUE'

AND c.name < 'cola_d'

AND ROWNUM <= 2;

4、SDO_NN_DISTANCE

SDO_NN_DISTANCE(number);

Number必须与sdo_nn操作符的最后一个参数保持一致。

Sdo_nn操作符是通过计算距离来识别客户的,我们可以通过sdo_nn_distance辅助操作符来获取这些距离。

在使用该操作符时,必须指定一个性能调优参数,sdo_num_res或sdo_batch_size,如果不知道如何设置sdo_batch_size的值,就将其设为0,索引会在内部使用合适的值。

SELECT /*+ INDEX(c cola_spatial_idx) */

c.mkt_id, c.name, SDO_NN_DISTANCE(1) dist

FROM cola_markets c

WHERE SDO_NN(c.shape,

sdo_geometry(2001,

NULL,

sdo_point_type(10, 7, NULL),

NULL,

NULL),

'sdo_num_res=2',

1) = 'TRUE'

ORDER BY dist;

5、SDO_RELATE

SDO_RELATE(geometry1, geometry2, param);

判断两个几何体的关系

SELECT a.gid

FROM polygons a, query_polys B

WHERE B.gid = 1

AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=touch') = 'TRUE'

UNION ALL

SELECT a.gid

FROM polygons a, query_polys B

WHERE B.gid = 1

AND SDO_RELATE(A.Geometry, B.Geometry, 'mask=coveredby') = 'TRUE';

SELECT c.mkt_id, c.name

FROM cola_markets c

WHERE SDO_RELATE(c.shape,

SDO_GEOMETRY(2003,

NULL,

NULL,

SDO_ELEM_INFO_ARRAY(1, 1003, 3),

SDO_ORDINATE_ARRAY(4, 6, 8, 8)),

'mask=anyinteract') = 'TRUE';

6、SDO_WITHIN_DISTANCE

SDO_WITHIN_DISTANCE(geometry1, aGeom, params);

返回与指定点相距指定的距离内的某表的几何对象

SELECT c.name

FROM cola_markets c

WHERE SDO_WITHIN_DISTANCE(c.shape,

SDO_GEOMETRY(2003,

NULL,

NULL,

SDO_ELEM_INFO_ARRAY(1, 1003, 3),

SDO_ORDINATE_ARRAY(4, 6, 8, 8)),

'distance=10') = 'TRUE';

二、关系操作符

语法:

操作符OVERLAPBDYDISJOINT(geometry1, geometry2);

1、SDO_ANYINTERACT

2、SDO_CONTAINS

3、SDO_COVEREDBY

4、SDO_COVERS

5、SDO_EQUAL

6、SDO_INSIDE

7、SDO_ON

8、SDO_OVERLAPBDYDISJOINT

9、SDO_OVERLAPBDYINTERSECT

10、SDO_OVERLAPS

11、SDO_TOUCH

空间函数与操作符的对比
1、 空间操作符使用的表必须有空间索引;

空间操作符执行速度比函数快,因此可以采用操作符的情况下就使用操作符


本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • Oracle Spatial 中的距离操作及效率

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment