Oracle-Spatial空间数据库基础

因为爱好各种版本hello world，就把两个空间数据库oracle-spatial，mysql版本的hello world 整理一下。

一、简介
oracle spatial是oracle公司推出的空间数据库组件，使oracle具备处理空间数据的能力。从9i开始对空间数据提供了比较完备的支持。Oracle Spatial主要通过元数据表、空间属性字段（SDO_GEOMETRY）和空间索引（R-tree和四叉树索引）来管理空间数据，并在此基础上提供一系列空间查询和空间分析的函数。

二、以上没啥用，意思就是：
1.建好表（多了一个SDO_GEOMETRY类型的字段）、插入数据后、插入个元数据视图，
2.空间属性字段就是空间表区别于其他表的标志，字段类型SDO_GEOMETRY（可以自定义）
3.空间索引可以理解为从普通表的B树索引演化来的，用来提高查询速度，四叉树索（原理我理解为切豆腐），但是对空间对象分布不均匀时，就会因不均衡速度变慢，所以主要用R-树索引，对于这两种索引有空再整理。
4.函数，部分依赖空间索引主要用来做查询，部分是不依赖索引的，所以当数据量大的时候就要先用依赖索引的函数快速缩小计算范围，然后再用空间分析函数计算想要的结果（刚开始用oracle-spatial的时候，用网上那些复制粘贴大神的方法，数据量大后就有问题了，后来受到上学时专业书内容的启发，想到一种办法，解决了查询不规则多边形内point的速度问题）

三、Oracle Spatial定义的SDO_GEOMETRY类型为：

 CREATE TYPE SDO_GEOMETRY AS OBJECT (
                    SDO_GTYPE   NUMBER,  //前面字符串为字段名；后面字符串为字段类型
                     SDO_SRID    NUMBER,
                     SDO_POINT    SDO_POINT_TYPE,
                     SDO_ELEM_INFO    SDO_ELEM_INFO_ARRAY,
                     SDO_ORDINATES    SDO_ORDINATE_ARRAY);

其中SDO_GEOMETRY AS OBJECT ，标识该类型为对象类型。开始我们可以理解它为ArcObjects中的Geometry对象（本来要素的shape字段中的对象就是Geometry），而不要理解他是怎么样组织的。至于该类型中的SDO_POINT_TYPE、SDO_ELEM_INFO_ARRAY、SDO_ORDINATE_ARRAY也是Oracle Spatial自定义的类型和 sdo_geometry 是一样的。

   现在对sdo_geometry 类型中的各个参数简单的介绍：
    1、SDO_GTYPE ：表示要存储的几何类型，如点线面。它是通过 NUMBER类型来表达的；
    2、SDO_SRID ：几何的空间参考坐标系，类型也为 NUMBER；
    3、SDO_POINT ：如果几何类型点类型的话，就是存储点坐标，否则为空。oracle自定义的SDO_POINT_TYPE类型；
    4、SDO_ELEM_INFO ：定义要如何理解SDO_ORDINATES中的坐标串的；
    5、SDO_ORDINATES ：存储实际坐标的，以X、Y以及不同点之间都是逗号隔开；

四、学习的时候看网上的不是复制粘贴，就是翻译翻译官网的例子，实际用还很不方便，下面直接说实际操作：
–0.查看是否安装了oracle-spatial

SQL> desc sdo_georaster;

Element         Type               
RASTERTYPE      NUMBER             
SPATIALEXTENT   MDSYS.SDO_GEOMETRY 
RASTERDATATABLE VARCHAR2(32)       
RASTERID        NUMBER             
METADATA        SYS.XMLTYPE  

这是装了，没装的话要手动装。

---

网上例子都是针对插入几条测试数据，公司实际用肯定需要批量插入，
不同数据格式导入有不同方法：

MapInfo连接oracle后，mapinfo的文件可直接导入。
ArcGIS的*.shp文件，需要使用Shp2SDO转换。

文本文件数据导入两种办法：

1.程序连接数据库一条条插（java-jdbc，python需要安装cx_oracle 模块）；
2.使用sqlldr批量插（但是每条数据需要拼成特殊格式）；

背景：
文本文件；
一条数据样例（三个字段id|经度|纬度，’|’分隔）：1|115.98346012774272|39.7059960734836 ；
数据量2000万条；

提前处理数据，拼接结果：

–1.建表

create table wyp_point_test(
id number,
geoloc sdo_geometry)

–2.sqlldr导入数据（这步sqlldr控制文件和普通表的控制文件是不一样的，要针对SDO_GEOMETRY 结构，这个网上没有，下面是点的例子，线和多边形的要修改控制文件和数据格式）
控制文件point.ctl

load data
infile "/data1/all_signal_dev/wyp/allrsl.txt"
append
continueif next(1:1)='#'
into table china_subway_point
fields terminated by "|"
trailing nullcols(
id，
geoloc column object
(sdo_gtype,
sdo_srid,
sdo_point column object(
x,y)))

–3.导入数据后更新元数据试图

insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME
,DIMINFO,SRID) values(
'wyp_point_test',
'geoloc',
mdsys.sdo_dim_array(
MDSYS.SDO_DIM_ELEMENT('x',70.000000000,140.000000000,0.000000050),
MDSYS.SDO_DIM_ELEMENT('y',0.000000000,60.000000000,0.000000050)),
8307--SRID
);

–4.创建索引

create index wyp_point_test_idx on wyp_point_test(geoloc)
indextype is mdsys.spatial_index

1）创建索引之前总是要为空间层插入元数据(更新元数据视图)
2）如果之前创建的索引失败了，必须先删除才能创建

Drop index wyp_point_test_idx ;

在索引创建过程中，Oracle检查索引列的sdo_srid和user_sdo_geom_metadata中的srid是否匹配，如果不匹配，Oracle会产生ora-13365错误。
空间索引信息可查看user_sdo_index_metadata或者较简单的user_sdo_index_info视图。
空间索引表存储在这个SDO_INDEX_TABLE字段中，总是以MDRT开头。不能将一个空间索引表和普通的表一样对待-即不能将它从一个表空间移到另一个表空间，也不能将它删除、复制等。否则，会出现无效的空间索引并导致后续的空间查询操作符或空间索引重建失败。
（R-树索引、四叉树索引原理、以及索引参数以后再说）

–5.空间查询和空间分析的函数
函数，部分依赖空间索引主要用来做查询，部分是不依赖索引的就是空间分析函数，其实，函数到好学，一看就会，就是准备数据的部分，网上说的很少吗，所以主要说数据组织部分，函数简单说。

–SDO_WITHIN_DISTANCE 查询500米范围内的对象

SELECT id
FROM WYP_POINT_TEST A 
WHERE SDO_WITHIN_DISTANCE(A.Geoloc, 
                           MDSYS.SDO_GEOMETRY(2001, 
                                              8307, 
                                              MDSYS.SDO_POINT_TYPE(116.4601216738,39.9534043499, 0), 
                                              NULL, 
                                              NULL), 
                            'distance=' || 500 || ' unit=m') = 'TRUE'

–SDO_FILTER、SDO_GEOM.RELATE 查询多边形内的point

SELECT
ID FROM WYP_POINT_TEST S  WHERE
SDO_FILTER(S.GEOLOC,MDSYS.SDO_GEOMETRY(2003,8307,NULL,
                                        MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
                                        MDSYS.SDO_ORDINATE_ARRAY(113.6578611,34.74956944,113.6539528,34.75609722,113.6560056,34.75886667,113.6608,34.76098889,113.6660139,34.75729722,113.665475,34.75417222,113.66205,34.75011111,113.6578611,34.74956944)),
                                        'QUERYTYPE = WINDOW') = 'TRUE'
AND
SDO_GEOM.RELATE(MDSYS.SDO_GEOMETRY(2003,8307,NULL,
                                     MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
                                     MDSYS.SDO_ORDINATE_ARRAY(113.6578611,34.74956944,113.6539528,34.75609722,113.6560056,34.75886667,113.6608,34.76098889,113.6660139,34.75729722,113.665475,34.75417222,113.66205,34.75011111,113.6578611,34.74956944)),
                                      'ANYINTERACT',
                                      S.GEOLOC,
                                      0.005)='TRUE'

6.重点：

1.上面两个例子都是都是用了个临时对象，也可以插入另一张表里。
2.Oracle对sql的长度是有限制的，例如一个多边形的经纬度有500多对，临时对象的方法就不行了，我想到的解决办法是，先把多边形入到另一张空间表里，这样sql长度就没有限制了，还有个问题，入表里不能用insert into 需要使用sqlldr， 因为insert 同样是sql。。。
3.SDO_GEOM.RELATE函数是不依赖索引的，所以数据量上千后就遍历很慢，困扰了很久，我做梦想到的办法就是，先通过索引查询出，多边形最小矩形内的点，再和多变形计算，需要使用RELATE计算的数据量巨减。
4.使用SDO_WITHIN_DISTANCE 同样可以查询多边形周边以及内部的点，距离设置为0，不就是内部的点了嘛，大于0就是周边和内部的了，但有个细节，这么做是最好对查询出的结果算下点和多边形距离，因为实际用的时候发现，使用这种方法的多边形经纬度要逆时针排列，顺时针的话，数据出来是错的，差了十万八千里。
5.2000万个点时，查询速度是秒级，平常用应该没什么问题。

7.个人总结的空见查询提高性能方法（仅供参考）

1.建立索引时,指明空间索引中的对象类型（点线面）
create index CELL_ADJ_ALL_idx on DIM_M_CELL_ADJ_ALL_SPATIAL(geoloc)
indextype is mdsys.spatial_index
PARAMETERS('layer_gtype=POINT')  
其中index_name为空间索引名，table_name为表名，row_name为列名，
layer_gtype的值除POINT外，还可以为LINE，POLYGON等。

2.利用表分区提高性能
当需要存储和检索的数据量庞大时,为了易于管理和提高空间数据检索的效率，可以在这个庞大的数据表中建立分区，
然后在这些分区表上建立空间索引，这样不仅使表易于管理，减少每次查询的查询数量。  

3.选择合理的空间操作符的参数次序    
在执行包含空间操作符的SQL语句时，Oracle会根据参数次序选择不同的执行计划。
因此，需根据查询对象的特点选择合理的参数次序，以便使Oracle Spatial选择较好的执行计划，
提高空间查询的效率。但是，并不是改变每个空间操作符的次序都可以提高空间查询的效率，
仅改变那些对参数次序敏感的空间操作符如SOD_WITHIN_DISTANCE才可以显著地提高空间查询效率.
之所以合理的空间操作符的参数次序可以提高空间查询的效率，主要在于Oracle Spatial的空间查询机制。
Oracle Spatial对空间查询使用双重查询机制，进行主过滤环节和次过滤环节。
调整空间操作符的参数次序可以使得主过滤阶段更充分的运用空间索引，从而提高空间查询的效率，缩短了空间查询

---

简单介绍一下sdo_Geom.Relate 吧

sdo_Geom.Relate(sdo_Geometry1, ‘MASK’, sod_Geometry2, tolerance )：
用于判断一个几何体与另一个几何体的关系，我们用于判断当前点是否在某一个面(省份面、县市面、乡镇面)上。 
　　参数说明： 
　　　　sdo_Geometry1，sdo_Geometry2为空间数据对应的几何对象。 
　　　　Tolerance: 容许的精度范围； 
　　    MASK参数: 
　　　　Anyinteract: sdo_Geometry2落在sdo_Geometry1面上包括在边上。 
　　　　Contains: sdo_Geometry2完全包含在sdo_Geometry1几何对象中，并且两个几何对象的边没有交叉。 
　　　　Coveredby: sdo_Geometry1完全包含在sdo_Geometry2中，并且这两个几何对象的边有一个或多个点相互重叠。 
　　　　Covers: sdo_Geometry2完全包含在sdo_Geometry1中，并且这两个几何对象的边有一个或多个点相互重叠。 
　　　　Disjoint: 两个几何没有重叠交叉点，也没有共同的边。 
　　　　Equal: 两个几何是相等的。 
　　　　Inside: sdo_Geometry1完全包含在sdo_Geometry2几何对象中，并且两个几何对象的边没有交叉。 
　　　　On: sdo_Geometry1的边和内部的线完全在sdo_Geometry2上。 
　　　　Overlapbdydisjoint: 两个几何对象交迭，但是边没有交叉。 
　　　　Overlapbdyintersect: 两个几何对象交迭，并且边有部分交叉。 
　　　　Touch: 两个几何对象有共同的边，但没有交叉。

最近用了下mysql的空间数据库，速度也很快,而且在不断更新，以后有机会想整理一下mysql、postgis这些空间数据库，以及复杂多边形的数据结构，还有四叉树、R-tree索引的原理。