--sunfruit
空間數(shù)據(jù)庫(kù)Oracle Spatial的建立過(guò)程如下：
-- 創(chuàng)建最基本的個(gè)人興趣點(diǎn)表結(jié)構(gòu)
drop table poi;
create table poi (gid?INTEGER,
??opid?INTEGER,
??gname?VARCHAR2(256),
??gshape?MDSYS.SDO_GEOMETRY);
-- 更新用戶空間數(shù)據(jù)對(duì)象視圖(建立索引依賴她)
delete from USER_SDO_GEOM_METADATA where TABLE_NAME='POI' and COLUMN_NAME='GSHAPE';
insert into USER_SDO_GEOM_METADATA values ('poi',
?????'gshape',
?????MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('lon',?-- lontitude
??????????-64800000,?-- min(china 26430867 73.41907434101486)
??????????64800000,?-- max(china 49679991 137.99997381765377)
??????????1),??-- scale (china abs 23249124)
???????MDSYS.SDO_DIM_ELEMENT('lat',??-- latitude
??????????-32400000,?-- min(china -1677502 -4.6597267116858045)
??????????32400000,?-- max(china 21571819 59.92171939467364)
??????????1)),??-- scale (china abs 23249321)
?????NULL);
-- 插入一個(gè)個(gè)人興趣點(diǎn)的SQL語(yǔ)句，使用標(biāo)準(zhǔn)點(diǎn)地物空間數(shù)據(jù)類型
delete from POI;
insert into POI values (20010001,
???1,
???'我的家',
???MDSYS.SDO_GEOMETRY(2001,?-- SDO_GTYPE
?????NULL,??-- SDO_SRID
?????SDO_POINT_TYPE(41884696, 14377039, NULL), NULL, NULL));
-- 插入一個(gè)個(gè)人興趣點(diǎn)的SQL語(yǔ)句，使用另一種點(diǎn)地物空間數(shù)據(jù)組織結(jié)構(gòu)
delete from POI;
insert into POI values (20010001,
???1,
???'我的家',
???MDSYS.SDO_GEOMETRY(2001,?-- SDO_GTYPE
?????NULL,??-- SDO_SRID
?????NULL,??-- SDO_POINT
?????MDSYS.SDO_ELEM_INFO_ARRAY (1,?-- SDO_STARTING_OFFSET
????????1,?-- SDO_ETYPE
????????1),?-- SDO_INTERPRETATION
?????MDSYS.SDO_ORDINATE_ARRAY (41884696,14377039)));
-- 創(chuàng)建缺省的R-tree空間索引
drop index POI_IDX;
CREATE INDEX POI_IDX on poi(gshape)
?INDEXTYPE is MDSYS.SPATIAL_INDEX;
--?PARAMETERS('SDO_LEVEL=10000');?-- 180*60*60*1000*2/100/100*90*60*60*1000*2/100/100 = 8398080000
-- 索引粗濾矩形窗口選擇SQL語(yǔ)句(對(duì)于點(diǎn)地物對(duì)象，索引粗濾的結(jié)果是精確的)
SELECT * FROM POI P
?WHERE sdo_filter(P.gshape,
??mdsys.sdo_geometry(2003,NULL,NULL,
????mdsys.sdo_elem_info_array(1,1003,3),
????mdsys.sdo_ordinate_array(41883696,14376039, 41885696,14378039)),
??'querytype=window') = 'TRUE';
-- 精確匹配矩形窗口選擇SQL語(yǔ)句(計(jì)算非常耗時(shí))
SELECT * FROM POI P
?WHERE sdo_relate(P.gshape,
??mdsys.sdo_geometry(2003,NULL,NULL,
????mdsys.sdo_elem_info_array(1,1003,3),
????mdsys.sdo_ordinate_array(41883696,14376039, 41885696,14378039)),
??'mask=INSIDE querytype=window') = 'TRUE';
?