# oracle spatial 增查SQL示例（三 ） 半圆弧和R-tree索引建立

1. 建表
create table itestg1(id int,
m1 MDSYS.SDO_GEOMETRY,–存放弧线（半圆）circularstring
m2 MDSYS.SDO_GEOMETRY–存放一个点
) ;

2. 建立索引
insert into user_sdo_geom_metadata(table_name,COLUMN_NAME, DIMINFO, SRID)
values(
‘ITESTG1’,
‘m2’,
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT(‘X’,-180,180,0.005),
MDSYS.SDO_DIM_ELEMENT(‘Y’,-90,90,0.005)
),
null
)；
insert into user_sdo_geom_metadata(table_name,COLUMN_NAME, DIMINFO, SRID)
values(
‘ITESTG1’,
‘m1’,
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT(‘X’,-180,180,0.005),
MDSYS.SDO_DIM_ELEMENT(‘Y’,-90,90,0.005)
),
null
)
建索引
create index index_m1 on itestg1(m1) indextype is mdsys.spatial_index;
create index index_m2 on itestg1(m2) indextype is mdsys.spatial_index;

3. 插入数据
INSERT INTO itestg1 VALUES(
1,
MDSYS.SDO_GEOMETRY(
2002,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,2,2),
MDSYS.SDO_ORDINATE_ARRAY(0,0, 1,1,2,0) ),
MDSYS.SDO_GEOMETRY(
2001,
NULL,
MDSYS.sdo_POINT_TYPE(1,1,null),
null,
null )
);

4. 执行查询
SELECT t.id, sdo_inside(t.m1,t.m2),sdo_inside(t.m2,t.m1) FROM ITESTG1 t