- 浏览: 1026143 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (538)
- 奇文共赏 (36)
- spring (13)
- hibernate (10)
- AOP/Aspectj (9)
- spring security (7)
- lucence (5)
- compass (3)
- jbmp (2)
- jboss rule(drools) (0)
- birt (1)
- jasper (1)
- cxf (3)
- flex (98)
- webgis (6)
- 设计模式 (1)
- 代码重构 (2)
- log4j (1)
- tomcat (9)
- 神品音乐 (1)
- 工作计划 (2)
- appfuse (1)
- svn (4)
- 寻章摘句 (3)
- eclipse (10)
- arcgis api for flex (1)
- 算法 (5)
- opengis-cs (1)
- bug心得 (13)
- 图标 (1)
- software&key (14)
- java (17)
- 搞笑视频 (13)
- sqlserver (9)
- postgresql (1)
- postgis (0)
- geoserver (5)
- 日子 (50)
- 水晶报表 (1)
- 绝对电影 (3)
- Alternativa3D (1)
- 酷站大全 (10)
- c++ (5)
- oracle (17)
- oracle spatial (25)
- flashbuilder4 (3)
- TweenLite (1)
- DailyBuild (6)
- 华山论贱 (5)
- 系统性能 (5)
- 经典古文 (6)
- SOA/SCA/OSGI (6)
- jira (2)
- Hadoop生态圈(hadoop/hbase/pig/hive/zookeeper) (37)
- 风水 (1)
- linux操作基础 (17)
- 经济 (4)
- 茶 (3)
- JUnit (1)
- C# dotNet (1)
- netbeans (1)
- Java2D (1)
- QT4 (1)
- google Test/Mock/AutoTest (3)
- maven (1)
- 3d/OSG (1)
- Eclipse RCP (3)
- CUDA (1)
- Access control (0)
- http://linux.chinaunix.net/techdoc/beginner/2008/01/29/977725.shtml (1)
- redis (1)
最新评论
-
dove19900520:
朋友,你确定你的标题跟文章内容对应???
tomcat控制浏览器不缓存 -
wussrc:
我只想说牛逼,就我接触过的那点云计算的东西,仔细想想还真是这么 ...
别样解释云计算,太TM天才跨界了 -
hw_imxy:
endpoint="/Hello/messagebr ...
flex+java代码分两个工程 -
gaohejie:
rsrsdgrfdh坎坎坷坷
Flex 与 Spring 集成 -
李涤尘:
谢谢。不过说得有点太罗嗦了。
Oracle数据库数据的导入及导出(转)
http://www.cnoug.org/archiver/?tid-67549.html
Step1. 创建一张表,其中shape用来存放空间数据
CREATE TABLE mylake (
feature_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);
Step2. 在user_sdo_geom_metadata表中插入新记录,用于描述空间字段
INSERT INTO user_sdo_geom_metadata VALUES (
'mylake', //---表名
'shape', //---字段名
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.05), //---X维最小,最大值和容忍度。
MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.05) //---Y维最小,最大值和容忍度
),
NULL //---坐标系,缺省为笛卡尔坐标系
);
Step3. 创建空间索引
CREATE INDEX mylake_idx ON mylake(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
Step4. 插入空间数据
Oracle Spatial用MDSYS.SDO_GEOMETRY来存储空间数据,定义为:CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);
SDO_GTYPE:用四个数字定义了所有的形状
第一位:维数
第二位:线性表示。用于3,4维数据,二维为0
最后两位: Value Geometry Description
00 UNKNOWN_GEOMETRY Spatial ignores this value
01 POINT A single point element
02 LINE or CURVE Contains one line string element that may be linear, curved or both
03 POLYGON Contains one polygon element with or without other polygon elements in it
04 COLLECTION A heterogeneous collection of elements
05 MULTIPOINT Contains one or more points
06 MULTILINE or MULTICURVE Contains one or more line string elements
07 MULTIPOLYGON Contains multiple polygon elements that maybe disjoint
SDO_SRID:坐标系,NULL为笛卡尔坐标系。
SDO_POINT:Oracle Spatial也可定义单个的点,SDO_POINT的定义:
CREATE TYPE sdo_point_type AS OBJECT (X NUMBER,Y NUMBER,Z NUMBER);
如何是二维,Z为NULL。
SDO_ELEM_INFO:每三个值描述一个元素。
第一个值:第一个顶点在SDO_ORDINATES_ARR开始位置
第二个值:元素类型
第三个值:顶点连接方式:1-通过直线连接,2-通过圆弧连接
定义为
CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
SDO_ORDINATES:几何图形所有顶点列表。定义为
CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;
// 插入包含一个岛屿的湖泊
INSERT INTO mylake VALUES(
10,
'Lake Calhoun',
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4)
));
// 插入两艘小船
INSERT INTO mylake VALUES(
11,
'The Windswept',
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(2,2, 3,2, 3,2, 2,3, 2,2)
)
);
INSERT INTO mylake VALUES(
12,
'Blue Crest',
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(7,7, 8,7, 8,7, 7,8, 7,7)
)
);
Step4. 查询
Oracle Spatial查询数据包括二个处理过程:
1.只通过索引查询候选项。通过函数SDO_FILTER实现:
SDO_FILTER(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
geometry1:必须是被索引的几何数据
geometry2:不一定是表中的空间字段,也不要求被索引
params:Filter类型
querytype=WINDOW:geometry2不要求来自表
querytype=JOIN:geometry2必须来自表
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE';
2.再检查每个候选项是否和条件精确匹配。通过函数SDO_RELATE实现:
SDO_RELATE(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
params:masktype类型
DISJOINT — the boundaries and interiors do not intersect
TOUCH — the boundaries intersect but the interiors do not intersect
OVERLAPBDYDISJOINT — the interior of one object intersects the boundary and interior of the other object, but the two boundaries do not intersect. This relationship occurs, for example, when a line originates outside a polygon and ends inside that polygon.
OVERLAPBDYINTERSECT — the boundaries and interiors of the two objects intersect
EQUAL — the two objects have the same boundary and interior
CONTAINS — the interior and boundary of one object is completely contained in the interior of the other object
COVERS — the interior of one object is completely contained in the interior of the other object and their boundaries intersect
INSIDE — the opposite of CONTAINS. A INSIDE B implies B CONTAINS A.
COVEREDBY — the opposite of COVERS. A COVEREDBY B implies B COVERS A.
ON — the interior and boundary of one object is on the boundary of the other object (and the second object covers the first object). This relationship occurs, for example, when a line is on the boundary of a polygon.
ANYINTERACT — the objects are non-disjoint.
// 选择在定义矩形内的所有小船
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE'
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'masktype=INSIDE querytype=WINDOW') = 'TRUE'
// masktype可联合使用
SELECT feature_id id
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE'
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'masktype=INSIDE+TOUCH querytype=WINDOW') = 'TRUE'
Oracle Spatial 提供的其他查询函数:
Query Description
SDO_NN Nearest neighbor
SDO_SDO_WITHIN_DISTANCE All geometries with a certain distance
Functions Description
SDO_GEOM.SDO_MBR The minimum bounding rectangle for a geometry
SDO_GEOM.SDO_DISTANCE The distance between two geometries
SDO_GEOM.SDO_INTERSECTION Provides the intersection point of two geometries
Step1. 创建一张表,其中shape用来存放空间数据
CREATE TABLE mylake (
feature_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);
Step2. 在user_sdo_geom_metadata表中插入新记录,用于描述空间字段
INSERT INTO user_sdo_geom_metadata VALUES (
'mylake', //---表名
'shape', //---字段名
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', 0, 100, 0.05), //---X维最小,最大值和容忍度。
MDSYS.SDO_DIM_ELEMENT('Y', 0, 100, 0.05) //---Y维最小,最大值和容忍度
),
NULL //---坐标系,缺省为笛卡尔坐标系
);
Step3. 创建空间索引
CREATE INDEX mylake_idx ON mylake(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
Step4. 插入空间数据
Oracle Spatial用MDSYS.SDO_GEOMETRY来存储空间数据,定义为:CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO MDSYS.SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES MDSYS.SDO_ORDINATE_ARRAY);
SDO_GTYPE:用四个数字定义了所有的形状
第一位:维数
第二位:线性表示。用于3,4维数据,二维为0
最后两位: Value Geometry Description
00 UNKNOWN_GEOMETRY Spatial ignores this value
01 POINT A single point element
02 LINE or CURVE Contains one line string element that may be linear, curved or both
03 POLYGON Contains one polygon element with or without other polygon elements in it
04 COLLECTION A heterogeneous collection of elements
05 MULTIPOINT Contains one or more points
06 MULTILINE or MULTICURVE Contains one or more line string elements
07 MULTIPOLYGON Contains multiple polygon elements that maybe disjoint
SDO_SRID:坐标系,NULL为笛卡尔坐标系。
SDO_POINT:Oracle Spatial也可定义单个的点,SDO_POINT的定义:
CREATE TYPE sdo_point_type AS OBJECT (X NUMBER,Y NUMBER,Z NUMBER);
如何是二维,Z为NULL。
SDO_ELEM_INFO:每三个值描述一个元素。
第一个值:第一个顶点在SDO_ORDINATES_ARR开始位置
第二个值:元素类型
第三个值:顶点连接方式:1-通过直线连接,2-通过圆弧连接
定义为
CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
SDO_ORDINATES:几何图形所有顶点列表。定义为
CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;
// 插入包含一个岛屿的湖泊
INSERT INTO mylake VALUES(
10,
'Lake Calhoun',
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1),
MDSYS.SDO_ORDINATE_ARRAY(0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4)
));
// 插入两艘小船
INSERT INTO mylake VALUES(
11,
'The Windswept',
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(2,2, 3,2, 3,2, 2,3, 2,2)
)
);
INSERT INTO mylake VALUES(
12,
'Blue Crest',
MDSYS.SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
MDSYS.SDO_ORDINATE_ARRAY(7,7, 8,7, 8,7, 7,8, 7,7)
)
);
Step4. 查询
Oracle Spatial查询数据包括二个处理过程:
1.只通过索引查询候选项。通过函数SDO_FILTER实现:
SDO_FILTER(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
geometry1:必须是被索引的几何数据
geometry2:不一定是表中的空间字段,也不要求被索引
params:Filter类型
querytype=WINDOW:geometry2不要求来自表
querytype=JOIN:geometry2必须来自表
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE';
2.再检查每个候选项是否和条件精确匹配。通过函数SDO_RELATE实现:
SDO_RELATE(geometry1 MDSYS.SDO_GEOMETRY, geometry2 MDSYS.SDO_GEOMETRY, params VARCHAR2)
params:masktype类型
DISJOINT — the boundaries and interiors do not intersect
TOUCH — the boundaries intersect but the interiors do not intersect
OVERLAPBDYDISJOINT — the interior of one object intersects the boundary and interior of the other object, but the two boundaries do not intersect. This relationship occurs, for example, when a line originates outside a polygon and ends inside that polygon.
OVERLAPBDYINTERSECT — the boundaries and interiors of the two objects intersect
EQUAL — the two objects have the same boundary and interior
CONTAINS — the interior and boundary of one object is completely contained in the interior of the other object
COVERS — the interior of one object is completely contained in the interior of the other object and their boundaries intersect
INSIDE — the opposite of CONTAINS. A INSIDE B implies B CONTAINS A.
COVEREDBY — the opposite of COVERS. A COVEREDBY B implies B COVERS A.
ON — the interior and boundary of one object is on the boundary of the other object (and the second object covers the first object). This relationship occurs, for example, when a line is on the boundary of a polygon.
ANYINTERACT — the objects are non-disjoint.
// 选择在定义矩形内的所有小船
SELECT name boat_name
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE'
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'masktype=INSIDE querytype=WINDOW') = 'TRUE'
// masktype可联合使用
SELECT feature_id id
FROM mylake t
WHERE feature_id = 12
AND SDO_FILTER(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'querytype=WINDOW') = 'TRUE'
AND SDO_RELATE(t.shape, mdsys.sdo_geometry(2003,NULL,NULL,
mdsys.sdo_elem_info_array(1,1003,1),
mdsys.sdo_ordinate_array(2,2, 5,2, 5,5, 2,5, 2,2)),
'masktype=INSIDE+TOUCH querytype=WINDOW') = 'TRUE'
Oracle Spatial 提供的其他查询函数:
Query Description
SDO_NN Nearest neighbor
SDO_SDO_WITHIN_DISTANCE All geometries with a certain distance
Functions Description
SDO_GEOM.SDO_MBR The minimum bounding rectangle for a geometry
SDO_GEOM.SDO_DISTANCE The distance between two geometries
SDO_GEOM.SDO_INTERSECTION Provides the intersection point of two geometries
发表评论
-
利用Oracle Spatial求几何对象的长度和面积
2010-04-28 16:38 2335http://www.cnblogs.com/clarkzhe ... -
ogr2ogr 参数大全
2010-03-31 08:15 5465http://www.gdal.org/ogr2ogr.htm ... -
数据转换系列二--shp格式数据导入PostGIS数据库之ogr2ogr命令
2010-03-31 08:12 5832http://andrewzhuyl.spaces.live. ... -
数据转换系列一--e00格式向shp(shapefile)转换
2010-03-31 08:11 4911数据转换系列一--e00格式向shp(shapefile)转换 ... -
基于Oracle Spatial的空间分析性能优化研究
2010-03-29 08:13 1294http://www.lw23.com/pdf_5c5f0e6 ... -
Oracle Spatial 和 ArcSDE区别与联系
2010-03-29 08:09 7288一、ORACLE SPATIAL概述 ORAC ... -
浅谈Oracle Spatial几何结构
2010-03-29 08:06 1259http://database.51cto.com/art/2 ... -
Oracle Spatial创建空间索引时遇到的ORA-29855问题
2009-09-16 10:18 3845过程描述: 执行的SQL语句:CREATE INDEX G ... -
Oracle Spatial 空间分析操作函数——SDO_WITHIN_DISTANCE
2009-09-16 10:16 7693SDO_GEOM.SDO_WITHIN_DISTANCE(sd ... -
Oracle Spatial 空间分析操作函数——SDO_GEOM.SDO_NN
2009-09-16 10:16 5542SDO_NN( sdo_Geometry1, sdo_Geom ... -
Oracle Spatial 空间分析操作函数——SDO_GEOM.RELATE
2009-09-16 10:15 9009http://gis8.blog.sohu.com/78072 ... -
Oracle Spatial: Spatial Concepts
2009-09-15 11:13 1422http://www.cnblogs.com/mjgforev ... -
选择矩形框内的空间对象
2009-04-16 15:25 1215select * from well t WHERE ... -
GIS中的坐标
2009-04-16 13:36 2291地图是用坐标来描述的,而坐标又是如何确定的呢?地球是一个形状不 ... -
网格索引
2009-04-16 13:34 1733http://blog.csdn.net/sjzwl/arch ... -
四叉树索引
2009-04-16 13:32 2054http://blog.csdn.net/sjzwl/arch ... -
空间索引
2009-04-16 13:27 1268http://blog.csdn.net/sjzwl/arch ... -
空间分析的基础--数据
2009-04-16 11:25 1072数据是信息化的基础,空间分析是GIS核心技术之一,深层次的数据 ... -
将 Oracle Spatial 与geoserver, Google Earth 集成
2009-04-16 11:08 3933将 Oracle Spatial 与 Google Earth ... -
执行基于位置的分析
2009-04-16 11:00 1183http://www.oracle.com/technolog ...
相关推荐
《Oracle Spatial空间信息管理:Oracle Database 11g》是世界级Oracle Spatial专家Ravi Kothuri、Albert Godfrind和Euro Beinat自力作,旨在向您提供空间信息管理方面的概念知识和实用技能,帮助您成为该领域的专家。...
《Oracle Spatial空间信息管理:Oracle Database 11g》是世界级Oracle Spatial专家Ravi Kothuri、Albert Godfrind和Euro Beinat自力作,旨在向您提供空间信息管理方面的概念知识和实用技能,帮助您成为该领域的专家...
本书是世界级Oracle Spatial专家Ravi Kothuri、Albert Godfrind和Euro Beinat的力作,旨在向您提供空间信息管理方面的概念知识和实用技能,帮助您成为该领域的专家。书中涵盖了Oracle数据库中空间信息管理的所有内容...
主要将了ArcGIS与Oracle Spatial之间的连接问题,解决了ArcGIS对于Oracle Spatial数据的读写问题
Oracle Spatial是甲骨文公司针对空间数据管理的一组插件, ...此,为了能更加快速、准确地查询空间对象及其属性,必须要对空 间数据建立一种高效的索引。Oracle Spatial提供了两种索引机制 即R树索引和四叉树索引。
Oracle Spatial的空间查询分析功能浅析.pdf
基于Oracle Spatial的空间查询技术及其优化.pdf
[Packt Publishing] Oracle Spatial 应用扩展 (英文版) [Packt Publishing] Applying and Extending Oracle Spatial (E-Book) ☆ 图书概要:☆ Overview Understand how to develop Oracle Spatial data models ...
OracleSpatial的空间查询处理机制分析及优化[归纳].pdf
学习Oracle Spatial的入门书
基于Oracle Spatial的矢量空间数据管理机制.pdf
xtreme for java连接oracle spatial 空间数据库
oracle spatial 空间 笔记
介绍了如何使用Oracle Spatial操作通过ArcSDE存储到Oracle数据库中的空间数据
《Oracle Spatial空间信息管理:Oracle Database 11g》是世界级Oracle Spatial专家Ravi Kothuri、Albert Godfrind和Euro Beinat自力作,旨在向您提供空间信息管理方面的概念知识和实用技能,帮助您成为该领域的专家。...
Oracle Spatial 11g GeoRaster Oracle Spatial 11g GeoRaster Oracle Spatial 11g GeoRaster Oracle Spatial 11g GeoRaster
空间数据库课件:第四讲 Oracle 及Oracle Spatial.ppt
ArcSDE和Oracle Spatial的定位不同
oracle spatial 介绍,应用,案例,实用说明等相关介绍
Pro Oracle Spatial for Oracle Database 11g shows how to take advantage of Oracle Database's built-in feature set for working with location-based data. A great deal of the information used in business ...