oracle s

<pre name="code" class="sql">---检查 oracle 是否有 Spatial DBA 权限
SELECT COMP_NAME, STATUS
FROM DBA_REGISTRY
WHERE COMP_NAME = 'Spatial';
--result：1 Spatial VALID
---检查 Spatial 是否完整，完整无返回值
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
FROM ALL_OBJECTS
WHERE OWNER='MDSYS' AND STATUS <> 'VALID'
ORDER BY OBJECT_NAME;

--- 创建 us_restaurants_new 表
CREATE TABLE us_restaurants_new
(
id NUMBER,
poi_name VARCHAR2(32),
location SDO_GEOMETRY -- New column to store locations
);
---插入 us_restaurants_new 表
INSERT INTO us_restaurants_new VALUES
(
1,
'PIZZA HUT',
SDO_GEOMETRY
(
2001, -- 点定义.
NULL, -- other fields are set to NULL.
SDO_POINT_TYPE -- Specifies the coordinates of the point
(
-87, -- first ordinate, i.e., value in longitude dimension
38, -- second ordinate, i.e., value in latitude dimension
NULL -- third ordinate, if any
),
NULL,
NULL
)
);
---地址编码器服务
SELECT
SDO_GCDR.GEOCODE_AS_GEOMETRY
(
'SPATIAL', -- Spatial schema storing the geocoder data
SDO_KEYWORDARRAY -- Object combining different address components
(
'3746 CONNECTICUT AVE NW',
'WASHINGTON, DC 20008'
),
'US' -- Name of the country
) geom
FROM DUAL ;
---查找距离最近的5个餐馆
SELECT poi_name
FROM
(
SELECT poi_name,
SDO_GEOM.SDO_DISTANCE(P.location, I.geom, 0.5) distance
FROM us_interstates I, us_restaurants P
WHERE I.interstate = 'I795'
ORDER BY distance
)
WHERE ROWNUM <= 5;
--删除创建索引
DROP INDEX us_restaurants_sidx;
CREATE INDEX us_restaurants_sidx ON us_restaurants(location)
INDEXTYPE IS mdsys.spatial_index;
--利用函数SDO_NN 找到距离 I795 公路 5个餐馆
SELECT poi_name
FROM us_interstates I, us_restaurants P
WHERE I.interstate = 'I795'
AND SDO_NN(P.location, I.geom) ='TRUE'
AND ROWNUM <= 5;
--缓冲查询 距离50公里 的餐馆
SELECT POI_NAME
FROM us_interstates I, us_restaurants P
WHERE
SDO_ANYINTERACT
(
P.location,
SDO_GEOM.SDO_BUFFER(I.geom, 50, 0.5, 'UNIT=KM')
) ='TRUE'
AND I.interstate='I795' ;


</pre>
<p> </p>