在oracle上创建空间索引 [英] Creating a spatial index on oracle

问题描述

我有一个表'floating_options'，并且我想在'area_geo'列(这是sdo_geometry列)上创建一个空间索引，当我从* floating_options中选择*时，两行数据按预期出现./p>

我使用了以下代码，但收到以下错误.我将非常感谢您的帮助！谢谢！

  CREATE INDEX area_idx ON float_options(area_geo)INDEXTYPE是MDSYS.SPATIAL_INDEX;错误报告 -SQL错误:ORA-29855:在执行ODCIINDEXCREATE例程时发生错误ORA-13203:无法读取USER_SDO_GEOM_METADATA视图ORA-13203:无法读取USER_SDO_GEOM_METADATA视图ORA-06512:位于"MDSYS.SDO_INDEX_METHOD_10I"的第10行29855. 00000-执行ODCIINDEXCREATE例程时发生错误"*原因:无法成功执行ODCIIndexCreate例程.*措施:检查例程是否已正确编码. 

解决方案

在索引表之前，应先对其进行空间启用".
尝试检查它是否显示在空间元数据中:

  SELECT *来自USER_SDO_GEOM_METADATA在哪里TABLE_NAME = UPPER('floating_options')AND COLUMN_NAME = UPPER('area_geo'); 

如果没有结果-则有两个选项可用.
一种肮脏的方式-直接插入数据

  INSERT INTO USER_SDO_GEOM_METADATA值(UPPER('floating_options')，UPPER('area_geo')，mdsys.SDO_DIM_ARRAY(mdsys.SDO_DIM_ELEMENT('Easting'，< lowest_x> ;,< highest_x> ;,< x_tolerance>)，mdsys.SDO_DIM_ELEMENT('Northing'，< lowest_y> ;,< highest_y> ;,< y_tolerance>))，< SRID>); 

请更改<占位符>

也请看看 https://community.oracle.com/thread/836452?tstart = 0 或 http://gerardnico.com/wiki/oracle_spatial/metadata

I have a table 'floating_options', and I want to create a spatial index on a column 'area_geo' (which is a sdo_geometry column, with two rows of data that appear as expected when I select * from floating_options).

I have used the following code but I am receiving the error below. I would be very grateful for any help! Thanks!

CREATE INDEX area_idx ON floating_options(area_geo)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX;


Error report -
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.
*Action:   Check to see if the routine has been coded correctly.

解决方案

Before indexing the table you should have it 'spatially enabled'.
Try to check if it is shown in spatial metadata:

SELECT * FROM USER_SDO_GEOM_METADATA
    WHERE TABLE_NAME = UPPER('floating_options')
      AND COLUMN_NAME = UPPER('area_geo');

If there are no results - then a couple of options are available.
One dirty way - insert data directly

INSERT INTO USER_SDO_GEOM_METADATA
VALUES (UPPER('floating_options'),UPPER('area_geo'),
        mdsys.SDO_DIM_ARRAY(
             mdsys.SDO_DIM_ELEMENT('Easting', <lowest_x>, <highest_x>, <x_tolerance>),
             mdsys.SDO_DIM_ELEMENT('Northing', <lowest_y>, <highest_y>, <y_tolerance>)
        ), <SRID>);

Please change the < placeholders > accordingly

Please take a look also at https://community.oracle.com/thread/836452?tstart=0 or http://gerardnico.com/wiki/oracle_spatial/metadata

这篇关于在oracle上创建空间索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！