Oracle 空间数据库基础（基于11g）

Oracle Spatial基础（基于11g）

本过程来源于上学期课程《空间数据库》课余设计，Oracle对空间数据的支持使得其在GIS方向有其独有的优势，由于近期有用到Oracle的需求，故重新整理了一下之前的文档以备用及分享，如有错误请留言指出。

教材基于Oracle11g，网上关于Oracle的资源教程较少且多基于此版本，所以笔者推荐11g用于熟悉工作。

转载请注明来源。

一、数据导入

1.前期准备：

• Oracleg11g：部署基本环境(https://blog.csdn.net/m0_37768631/article/details/89164329)；

• sqlplus、sqlDeveloper：用于管理及操作空间数据库、管理及操作数据，后者为图形化操作界面；本组主要进行几何形状展示，未涉及属性，否则需借助Mapbuilder或Geoserver.

• Mapbuilder：用于显示携带空间信息数据表，可用于代码测试及结果展示；更方便地可通过sqldeveloper或PLSQL地图视图查看几何信息并编辑，可在oracle官网下载对应版本；

• 教材参考——《Oracle Spatial空间信息管理：Oracle Database 11g-清华大学出版社》，十分经典实用的教材。

  • 链接：https://pan.baidu.com/s/1dDKd6yVjQT6BzW__gXeLEQ ，提取码：s3n4
  • 内容有侵权请联系删除

• 关于Oracle Spatial自带的一些函数可用看这个参考及教程，文末我使用其中的缓冲区及道格拉斯函数进行了简单的示例，可自行参考

2.测试数据导入

2.1 教材参考数据导入（from .dmp格式）

我首先通过系统账号SYS/SYSTEM创建了新账户Spatial(且我设置我的密码为spatial)并授予管理权限，因为如果使用系统账户登录，其自带大量表格不便于查询及管理。具体查看电子书前言部分

• 通过SqlPlus导入（cmd中输入）：–imp **账号/**密码@数据库实例 dmp所在文件地址 其他参数（可选）；

• Imp数据文件地址：官方提供：https://github.com/Apress/pro-oracle-spatial-for-oracle-db-11g

  • ①、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\app_data.dmp ignore=y FULL=y;

  • ②、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\citybldgs.dmp ignore=y FULL=y;

  • ③、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\gc.dmp ignore=y FULL=y;

  • ④、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\map_detailed.dmp ignore=y FULL=y;

  • ⑤、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\map_large.dmp ignore=y FULL=y;

  • ⑥、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\net.dmp ignore=y FULL=y;

  • ⑦、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\routing.dmp ignore=y FULL=y;

  • ⑧、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\styles.dmp ignore=y FULL=y;

  • ⑨、imp spatial/spatial@orcl

    file= C:\Users\admin\Desktop\Coding\Oracle\Examples\Data\zip.dmp ignore=y FULL=y;

/* 函数说明：CCENSE/CCENSE@OracleDB —>用户名/密码@数据库实例

​ file=“本地路径+需要导入/导出的dmp文件全称”

​ log=“日志文件”

​ FULL=y;

​ 注意：此语句应该在CMD中运行

*/

• 教材提供数据齐全，包含美国地区点、线、面数据及部分全球数据表，部分表含人口等统计数据可用。

2.2 通过shapefile文件导入

此处导入有很多种方式，最简单易行的是通过mapbuild工具直接导入

• 打开mapbuild并连接数据库
  
• tools——import shapefile
  

二、了解空间元数据

1.什么是元数据

狭隘的说，可以理解为对二元表的某一字段（列）的约束格式，如我需要限制某表日期这一列为年月日格式且日期处在1998年1月1日之后，就是其“元数据”

• 携带空间信息的表通常将其几何对象存储在SDO_GEOMETRY列中，这是专用于存储空间数据的数据类型，下章会介绍；这一列数据的所有对象即作为一个空间层。

• 所以：要对该列几何对象进行操作（验证、建索引、查询等）需只指定元数据

• 包含信息，该信息以字典形式存储在：其他用户→MDSYS→视图→user_sdo_geometry_metadata中，可自行查看，如下

  • 维数

  • 维度的边界

  • 容差

  • 坐标系

2.字典视图说明

• table_name 和 column_name用于唯一标识每个空间层
• diminfo用于存储维度信息
• srid此属性很重要，用于存储坐标系的相关信息

2.1 diminfo属性说明

！也就是说若该列为点——经纬度坐标，则会有两个element类型，一个经度一个维度

• sdo_dim_array说明
  

  • 一个名称、一个大小范围、一个容差，容差一般默认，主要影响涉及要素距离的查询精度

2.2 srid属性说明

• 该字段一般用数字代码指定坐标系，见示例
  

3. 示例——填充空间元数据

• 此表为customers Table

INSERT INTO USER_SDO_GEOM_METADATA VALUES
(
'CUSTOMERS', 表名
'LOCATION', 指定列名
SDO_DIM_ARRAY -- DIMINFO attribute for storing dimension bounds, tolerance存储维度信息的属性字段
	(							//两个ELEMENT，说明为(x,y)形式
		 SDO_DIM_ELEMENT		//这是第一个元素
		 (
		 'LONGITUDE', -- DIMENSION NAME for first dimension
		 -180, -- SDO_LB for the dimension
		 180, -- SDO_UB for the dimension
		 0.5 -- Tolerance of 0.5 meters		//指定名称、上下限和容差，下同
		 ),
		 SDO_DIM_ELEMENT		//这是第二个
		 (
		 'LATITUDE', 
		 -90, 
		 90, 
		 0.5 
		 )
	),
8307 -- SRID代表大地坐标系
);

三、*sdo_geometry数据类型

1.可存储哪些几何数据？

• 可以存储点线面及其集合
• 针对不同的几何体不展开描述，本身也不难理解
• 我们更加关心的是如何实现？——不难想到，用数组

2.geometry的逻辑实现

• 下图为几何类型的构造图及关系，对于我们而言应该很眼熟（在AE开发中常用）

• 简单来讲就是一个坐标的标准+N个坐标，如我建立一个笛卡尔坐标系，然后用数组存储即可如：[(x1,y1,z1), (x2,y2, z2), (x3, y3, z3)，...]

  对于更复杂的形体则通过如下列关系层层包含嵌套存储：

3.geometry的类型、属性和值的描述

3.1 gtype——维度和形状的确定

3.2 srid——坐标系的确定

• *这部分要展开内容会很丰富，但实际中不需要深究、有需要可看教材详解

3.3 sdo_point——确定单点坐标

很容易理解，就是存储$(X,Y,Z）$坐标对，其数据类型的描述如下

• 以下示例为插入一个点到已知表中

  INSERT INTO geometry_examples (name, description, geom) VALUES
(
'POINT',
'2-dimensional Point at coordinates (-79,37) with srid set to 8307',
SDO_GEOMETRY
(
2001, -- SDO_GTYPE format: D00T. Set to 2001 for a 2-dimensional point
8307, -- SDO_SRID (geodetic)
SDO_POINT_TYPE
(
-79, -- ordinate value for Longitude
37, -- ordinate value Latitude
NULL -- no third dimension (only 2 dimensions)
),
NULL,
NULL
)
);//五个元素的填充，点、后两个默认为空

• 另外，我们可以通过更为通俗易懂的方式（使用构造函数）构造一个点，如下

3.4 element_info和ordinates——线、面（多点）及复杂几何体

这里简单描述：

• ordinates为一系列的坐标点，应数组存储如：

• element_info用于指定这些点如何组成几何体
• 示例：见下一节

四、空间数据的加载

• 往SDO_GEOMETRY列中插入数据，以面数据为例，代码可自行在资料中获取

五、sql语句操作Geometry数据——举例

1.读取几何信息

• 以下代码（来自数据）展示创建一个新的店铺（分店）位置，计算店铺能销售客户的区域范围，并创建一个运输的路径，且当货车运行时对线路进行延伸

DECLARE
b_long NUMBER;
b_lat NUMBER;
new_long NUMBER;
new_lat NUMBER;
new_branch_loc SDO_GEOMETRY;
sales_region SDO_GEOMETRY;
route SDO_GEOMETRY;
//变量声明
BEGIN
-- Obtain Old location for branch id=1
SELECT br.location.sdo_point.x, br.location.sdo_point.y
INTO b_long, b_lat
FROM branches br
WHERE id=1;
//将获取的x,y赋值给经纬度变量
-- Compute new coordinates: say the location is displaced by 0.0025 degrees
new_long := b_long+ 0.0025;
new_lat := b_lat + 0.0025;
//生成新的点——作为新点的位置
-- Create new branch location using old location
new_branch_loc :=
point
(
X=> new_long,
Y=> new_lat,
SRID=> 8307
) ;
//用上述获取的新点创建新分店
-- Compute sales region for this branch
sales_region :=
rectangle
(
CTR_X=> new_long,
CTR_Y=> new_lat,
EXP_X=> 0.005,
EXP_Y=> 0.0025,
SRID=> 8307
) ;
//计算销售区域（范围）
-- Create Delivery Route
route :=
line
(
FIRST_X=> -122.4804,
FIRST_Y=> 37.7805222,
NEXT_X=> -123,
NEXT_Y=> 38,
SRID=> 8307
) ;
//创建运输路线
-- Update Delivery Route by adding new point
route :=
add_to_line
(
GEOM=> route,
POINT => POINT(-124, 39, 8307)
) ;
//通过加点调整运输路线
-- Perform additional analysis such as length of route,
-- or # of customers in sales region (we will see examples in Chapters 8 and 9)
-- ...
-- Update geometry in branches table
UPDATE branches SET LOCATION = new_branch_loc WHERE id=1;
//刷新
END;

2.oracle数组（array）基础

• 相比于常规的语言sql数字操作显得较为麻烦，需要输血

• array是可以嵌套使用的，并且其内部有序

• 先通过一个较为综合的示例进行讲解，后续展开

  SET SERVEROUTPUT ON
DECLARE

  -- Declare a type for the VARRAT
TYPE MY_ARRAY_TYPE IS VARRAY(10) OF NUMBER;
//声明一个自定义的数组的类型
-- Declare a varray variable
V MY_ARRAY_TYPE;
//定义一个数组V
-- Other variables
I NUMBER;
K NUMBER;
L NUMBER;
ARRAY_CAPACITY NUMBER;
N_ENTRIES NUMBER;
//其他变量定义
BEGIN
-- Initialize the array
V := MY_ARRAY_TYPE (1,2,3,4);
//数组初始化
-- Get the value of a specific entry
DBMS_OUTPUT.PUT_LINE('* Values for specific array entries');
K := V(3);
DBMS_OUTPUT.PUT_LINE('V(3)='|| V(3));
I := 2;
L := V(I+1);
DBMS_OUTPUT.PUT_LINE('I=' || I);
DBMS_OUTPUT.PUT_LINE('V(I+1)=' || V(I+1));
//下表访问取值，输出打印

  -- Find the capacity of a VARRAY:
DBMS_OUTPUT.PUT_LINE('* Array capacity');
ARRAY_CAPACITY := V.LIMIT();
DBMS_OUTPUT.PUT_LINE('Array Capacity: V.LIMIT()='||V.LIMIT());
N_ENTRIES := V.COUNT();
DBMS_OUTPUT.PUT_LINE('Current Array Size: V.COUNT()='||V.COUNT());
//打印（输出）当前数组的容量

  -- Range over all values in a VARRAY
DBMS_OUTPUT.PUT_LINE('* Array Content');
FOR I IN 1..V.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')=' || V(I));
END LOOP;
//使用for循环遍历数组中的值

  FOR I IN V.FIRST()..V.LAST() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')=' || V(I));
END LOOP;
//first()和last()分别返回array[]最小和最大的下标

  I := V.COUNT();
WHILE I IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')=' || V(I));
I := V.PRIOR(I);
END LOOP;
//prior（x）和next(x)返回前一个和后一个下标，如上用来后向遍历

  -- Extend the VARRAY
DBMS_OUTPUT.PUT_LINE('* Extend the array');
I := V.LAST();
V.EXTEND(2);
V(I+1) := 5;
V(I+2) := 6;
//给数组append值

  DBMS_OUTPUT.PUT_LINE('Array Capacity: V.LIMIT()='||V.LIMIT());
DBMS_OUTPUT.PUT_LINE('Current Array Size: V.COUNT()='||V.COUNT());
FOR I IN 1..V.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')='|| V(I));
END LOOP;

  -- Shrink the VARRAY
DBMS_OUTPUT.PUT_LINE('* Trim the array');
V.TRIM();
//弹出trim()
DBMS_OUTPUT.PUT_LINE('Array Capacity: V.LIMIT()='||V.LIMIT());
DBMS_OUTPUT.PUT_LINE('Current Array Size: V.COUNT()='||V.COUNT());
FOR I IN 1..V.COUNT() LOOP
DBMS_OUTPUT.PUT_LINE('V('||I||')='|| V(I));
END LOOP;

  -- Delete all entries from the VARRAY
DBMS_OU