- 浏览: 224748 次
- 性别:
- 来自: beijing
文章分类
<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>
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>
发表评论
-
geoserver 在liunix 系统下 地图乱码问题解决
2013-01-04 13:20 1142... -
openscales1.2 实现加载arcgis 切片
2012-09-14 12:30 2445package org.openscales.core.lay ... -
重写openalyers 的方法,加载天地图瓦片数据
2012-06-20 09:16 6261将TiandituLayer 加载到OpenLayers.js ... -
利用工具将shp文件导入到oracle spatial中
2012-05-21 14:20 43281 下载shp2sdo 工具,将工具放在 oracle按照目录 ... -
oracle spatial 空间数据学习
2012-05-17 16:09 1746最近项目使用 空间数据库 oracle spatial ... -
在ArcGIS 10 Desktop中打开Google Map,BingMap
2012-04-13 15:56 1509安装使用: 1 双击ArcBruTile_0.2.zip ... -
Arcgis object for java 访问栅格数据,使用GP工具,进行栅格矢量化---栅格矢量化面。
2012-06-20 09:17 4050直接贴代码了,不多解释了。 1、栅格数据的 ... -
arcgis server 服务
2012-01-06 17:58 1145package com.hdmap.fymas; imp ... -
ArcObject中的游标Cursor的理解
2011-05-04 10:48 1249cursor给人的第一 ... -
esri arcobj 开发for java
2011-04-15 14:04 10471、查找数据 1)、利用FeaturCursor进行空间查询 ... -
ESRI Flex GIS开发连载(1) ---环境搭建
2011-03-25 10:29 18311首先 用 argis server 发布一个 s ... -
树-Oracle用Start with...Connect By子句递归查询
2010-12-23 15:41 1147转 。。。。。。。。。。。。。。。。。。。。。。。。。 St ... -
无聊写的oracle 存储过程
2010-09-27 11:37 926create or replace procedure qin ... -
oracle中判断某个字段是否存在
2010-08-30 16:43 4333oracle中判断某个字段是否存在 docum ... -
Oracle 10g for Solaris 安装
2010-07-26 16:25 10831 .首先设置核心系统参数 # vi /etc/sy ... -
Oracle job 的写法
2010-04-29 14:22 1339写了一个job 很简单的,一分钟内 想一个表内插入 一 ... -
drop>; truncate >; delete
2010-03-09 20:40 800注意:这里说的delete是指 ... -
arcgis SDE for Java
2010-03-04 12:32 4104API:http://edndoc.esri.com/arcs ... -
sys_guid()
2010-02-01 21:26 992select sys_guid() from dual -
Oracle timestamp
2009-12-04 17:13 1925我们都知道date和timesta ...
相关推荐
Oracle Solaris11 Transitioning From Oracle Solaris10 to Oracle Solaris11-140
Oracle Solaris 11.1 Administration:Oracle Solaris Zones,Oracle Solaris 10 Zones,and Resource Management-444
Oracle Solaris 11.2 Planning for Network Deployment in Oracle Solaris 11.2-32
Oracle Solaris 11.2 Using a FIPS 140 Enabled System in Oracle Solaris 11.2-16
oracle on k8s 部署yaml
The Oracle Hacker's Handbook: Hacking and Defending Oracle
Learn through this practical guide to SQL tuning how Oracle's own experts do it, using a freely downloadable tool called SQLTXPLAIN. This new edition has been expanded to include AWR, Oracle 12c ...
Oracle Framework Developer's Guide 12.1.3. Oracle Framework Developer's Guide 12.1.3.
It’s safe to say you’re interested in learning about Oracle database, as that’s what this book is about. Why Oracle database, and why this book? Oracle Database is one of the most popular database ...
Fusion Developer's Guide for Oracle ADF [11.1.1.7]
Oracle Programmer's Hand Book - Beginner
Oracle Essbase & Oracle OLAP: The Guide to Oracle's Multidimensional Solution (Osborne ORACLE Press Series) By Michael Schrader, Dan Vlamis, Mike Nader, Chris Claterbos, Dave Collins, Mitch Campbell, ...
Oracle Fusion Middleware Developer's Guide for OracleTopLink.pdf
This book is an easy-to-understand guide to building Oracle8i (Oracle's "Internet database") Web applications using a variety of tools -- PL/SQL, HTML, XML, WebDB, and Oracle Application Server (OAS)....
Oracle Database Net Services Administrator's Guide is intended for the following readers: ■Network administrators ■Directory server administrators ■Database administrators ■Decision makers This ...
Oracle Spatial User's Guide and Reference(11.1g)
Oracle 的OLE DB开发接口文档
Oracle Spatial Developer's Guide
Oracle 体系概述:本章主要介绍 Oracle 的安装、配置、体系结构的初步认识。通过阅读本章内容,你可以自己安装并配置数据库,搭建自己的 Oracle 开发环境。并能处理一些常见的 Oracle 问题。 Oracle 开发概述:本章...
121wfag Oracle workflow Administrator's Guide