DB2空间数据库的基本知识

create table master_markets(
mkt_id number primary key,
name varchar(100),
shape MDSYS.SDO_GEOMETRY)


insert into master_markets valueS (1,'广发银行大厦',MDSYS.Sdo_Geometry(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(1,1,5,7)
))

--面
insert into master_markets valueS (2,'广发银行大厦2店',MDSYS.Sdo_Geometry(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(2,3,4,5)
))


insert into master_markets valueS (3,'广发银行导航线路',MDSYS.Sdo_Geometry(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(10,50,100,400)
))



--点
insert into master_markets valueS (5,'广发银行导航测试',MDSYS.SDO_GEOMETRY(2001, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1), SDO_ORDINATE_ARRAY(
4.73333333, 3.93333333))
)

insert into master_markets valueS (6,'广发银行导航测试test',MDSYS.
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(1, 7, 1, 1, 5, 1, 5, 3, 3, 3, 4, 5, 5, 5, 5, 7, 1, 7)))

---线
insert into master_markets valueS (7,'广发银行线测试',MDSYS.
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,3,2,2),
SDO_ORDINATE_ARRAY(10,10,10,14,6,10,14,10)))

--创建索引
create index master_spatial_idx on master_markets(shape)
indextype is MDSYS.spatial_index

--返回a/c市场的交

select sdo_geom.sdo_intersection(c_a.shape,c_c.shape,0.005)
from master_markets c_a,master_markets c_c
-- where

--返回所在市场的面积

select name ,sdo_geom.sdo_area(shape,0.005)
from master_markets

--求周长,如果几何体中间带洞，
--使用注意如果输入多边形含有一个或多个洞，则该函数计算外边界的周和所有洞的周长，然后将这些周长进行累加，得到最终的总周长。

select name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo)
FROM master_markets c, user_sdo_geom_metadata m
WHERE m.table_name = 'master_MARKETS' AND m.column_name = 'SHAPE'


--- 矩形
--sdo_gtype=2003
--sdo_srid=null
--sdo_point=null
--sdo_etype=(1,1003,3)



SELECT c.name, SDO_GEOM.SDO_BUFFER(c.shape, m.diminfo, 1000,'unit=km arc_tolerance=0.05')
FROM master_markets c, user_sdo_geom_metadata m
WHERE m.table_name = 'master_MARKETS' AND m.column_name = 'SHAPE'

---确定线'广发银行线测试' 50米范围内的
select name
from master_markets,master_markets p
where SDO_GEOM.sdo_area