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

Oracle Spatial基础（基于11g）

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

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

转载请注明来源。

文章目录

• Oracle Spatial基础（基于11g）
• • @[toc]
  • 一、数据导入
  • • 1.前期准备：
    • 2.测试数据导入
    • • 2.1 教材参考数据导入（from .dmp格式）
      • 2.2 通过shapefile文件导入
  • 二、了解空间元数据
  • • 1.什么是元数据
    • 2.字典视图说明
    • • 2.1 diminfo属性说明
      • 2.2 srid属性说明
    • 3. 示例——填充空间元数据
  • 三、*sdo_geometry数据类型
  • • 1.可存储哪些几何数据？
    • 2.geometry的逻辑实现
    • 3.geometry的类型、属性和值的描述
    • • 3.1 gtype——维度和形状的确定
      • 3.2 srid——坐标系的确定
      • 3.3 sdo_point——确定单点坐标
      • 3.4 element_info和ordinates——线、面（多点）及复杂几何体
  • 四、空间数据的加载
  • 五、sql语句操作Geometry数据——举例
  • • 1.读取几何信息
    • 2.oracle数组（array）基础
    • • 2.1 定义固定长度的一维数组
      • 2.2 定义可变长度的一维数组
      • 2.3 定义多维数组
      • 2.4 Oracle调用函数返回的数组
      • 2.5 强制修改已经被引用的自定义type
      • 2.6 oracle数组属性和函数
    • 3.创建几何体的函数
    • • 3.1创建点
      • 3.2 创建矩形
      • 3.3 创建线段
    • 4.修改几何体
    • • 4.1 从线段删除某点
      • 4.2 追加点
    • 5.构造通用函数——从几何体中提取信息
    • • 5.1 计算几何体中的点数
      • 5.2 从线串中某位置获取一点
      • 5.3 获取几何体中的一个点
  • 六、sql实例
  • • 1.道格拉斯扑克simplify——线压缩
    • • 1.1 测试结果
      • 1.2 代码
    • 2.线面缓冲区生成buffer
    • • 2.1 结果-点、线缓冲区
      • 2.2 代码
    • 3.*ArcGIS-smooth功能粗实现
    • • 3.1 算法简介
      • • 3.1.1 三次B样条曲线方程基函数
        • 3.1.2 线平滑
        • 3.1.3 面平滑
      • 3.2 实现策略
      • 3.3 处理面的代码
      • 3.4 结果
    • 4.Python连接及操作oracle

一、数据导入

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 **账号
  • 教材提供数据齐全，包含美国地区点、线、面数据及部分全球数据表，部分表含人口等统计数据可用。

  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   ） ( X,Y,Z\ ） (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_OUTPUT.PUT_LINE('* Empty the array');

    V.DELETE();

    //删除数组所有元素

    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;

    END;

  2.1 定义固定长度的一维数组

  ​

  type type_array is varray(10) of varchar2(20);

  sey

  ​ 1、varray(10)表示定义长度为10的数组

  ​ 2、varchar2(20)表示数组为字符型，且元素字符串长度不超过20

  2.2 定义可变长度的一维数组

  ​ type type_array is table of varchar2(20) index by binary_integer;

  1、table表示可变长度
    2、index by binary_integer 表示以符号整数为索引
          一维数组的初始化
          /*初始化为空数组*/
    var_array type_array := type_array();
           /*初始化为六个元素数组*/
  
    var_array type_array := type_array('ggs','jjh','wsb','csl','dd','bb'); 
          /*直接对各个元素进行赋值*/
  
    var_array.extend(3);
    var_array(1) = '1';
    var_array(2) = '2';
    var_array(3) = '3';
   
    /*通过遍历数组元素方式进行初始化操作*/
    for i in 1..var_array.count loop
        var_array(i) = to_char(i);
    end loop;
             

  1、oracle数组的索引从1开始，而不是从0开始2、count为数组的长度

  2.3 定义多维数组

  ​ type type_array is table of Tbl_User % rowtype index by binary_integer;

  ​ 说明：Tbl_User 为基础表

  多维数组的初始化
    select * 
    bulk collect into var_array 
    from t_user;
          说明：bulk collect 表示将批量查询数据直接插入collection中，而不是通过cursur一条条插入
          多维数组的读取方法
  
    for i in 1..var_array.count loop
        dbms_output.put_line(var_array(i).user_id);          
        dbms_output.put_line(var_array(i).username);
    end loop;
             

  2.4 Oracle调用函数返回的数组

  ​ 方法一

  declare 
        aa Tbl_StrSplit := Tbl_StrSplit();
    begin
        aa := PKG_TCH_COURSE_INIT.Fn_GetTermDate('root', 2);
        dbms_output.put_line(aa(1));
        dbms_output.put_line(aa(2));
    end;
          方法二
    Select Fn_GetTermDate(P_SchoolKey, P_TermKey)
    Into Tbl_TermDate
    From dual;
             

  2.5 强制修改已经被引用的自定义type

  • 修改自定义type类型的OBJTYPE_VAR时失败

  OBJTYPE_VAR原有结构
  
    CREATE OR REPLACE TYPE "OBJTYPE_VAR"  AS OBJECT
    (
    field0      VARCHAR2(1000),
    field1      VARCHAR2(1000)
    )
  
         修改后的新结构
    CREATE OR REPLACE TYPE "OBJTYPE_VAR"  AS OBJECT
    (
         serialNo    Number,
     field0      VARCHAR2(1000),
     field1      VARCHAR2(1000)
    )
   
          执行修改时提示错误
         “cannot drop or replace a type with type or table  dependents”
  
          原因是已经在其他地方使用了OBJTYPE_VAR，oracle不允许直接修改修改或删除被引用的OBJTYPE_VAR，但可以通过加上force关键字强制执行。
  
          强制重新创建
  
    CREATE OR REPLACE TYPE "OBJTYPE_VAR"  FORCE  AS OBJECT 
    (
          serialNo    NUMBER,
          field0      VARCHAR2(1000),
          field1      VARCHAR2(1000)
    ) 
          强制删除掉OBJTYPE_VAR
  
    drop type OBJTYPE_VAR force
          定义一维可变数组
  
    CREATE OR REPLACE TYPE "TBLTYP_VAR"    Is Table Of  VARCHAR2 (32767);
   
    CREATE OR REPLACE TYPE "TBLTYP_Int"    Is Table Of  Pls_Integer;
          定义三维可变数组
  
    CREATE OR REPLACE TYPE "OBJTYPE_VAR"  FORCE  AS OBJECT
    (
          serialNo    NUMBER,
          field0      VARCHAR2(1000),
          field1      VARCHAR2(1000)
    ) 
             

  2.6 oracle数组属性和函数

  • COUNT 返回集合中元素的个数
  • DELETE 删除集合中所有元素
  • DELETE(x) 删除元素下标为x的元素 对VARRAY非法
  • DELETE(x,y) 删除元素下标从X到Y的元素 对VARRAY非法
  • EXIST(x) 如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
  • EXTEND 在集合末尾添加一个元素 对Index_by非法
  • EXTEND(x) 在集合末尾添加x个元素 对Index_by非法
  • EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法
  • FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
  • LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等COUNT.
  • LIMIT 返回VARRY集合的最大的元素个数 Index_by集合和嵌套表无用
  • NEXT(x) 返回在第x个元素之后及紧挨着它的元素值，如果x是最后一个元素，返回null.
  • PRIOR(x) 返回在第x个元素之前紧挨着它的元素的值，如果x是第一个元素，则返回null。
  • TRIM 从集合末端开始删除一个元素 对于index_by不合法
  • TRIM(x) 从集合末端开始删除x个元素

  3.创建几何体的函数

  3.1创建点

  CREATE OR REPLACE FUNCTION point (

  x NUMBER, y NUMBER, srid NUMBER DEFAULT 8307)

  RETURN SDO_GEOMETRY

  DETERMINISTIC

  IS

  BEGIN

  RETURN SDO_GEOMETRY (

  2001, srid, SDO_POINT_TYPE (x,y,NULL), NULL, NULL);

  END;

  3.2 创建矩形

  CREATE OR REPLACE FUNCTION rectangle (

  ctr_x NUMBER, ctr_y NUMBER, exp_x NUMBER, exp_y NUMBER, srid NUMBER)

  RETURN SDO_GEOMETRY

  DETERMINISTIC

  IS

  r SDO_GEOMETRY;

  BEGIN

  r := SDO_GEOMETRY (

  2003, srid, NULL,

  SDO_ELEM_INFO_ARRAY (1, 1003, 3),

  SDO_ORDINATE_ARRAY (

  ctr_x - exp_x, ctr_y - exp_y,

  ctr_x + exp_x, ctr_y + exp_y));

  RETURN r;

  END;

  3.3 创建线段

  CREATE OR REPLACE FUNCTION line (

  first_x NUMBER, first_y NUMBER, next_x NUMBER, next_y NUMBER, srid NUMBER)

  RETURN SDO_GEOMETRY

  DETERMINISTIC

  IS

  l SDO_GEOMETRY;

  BEGIN

  l := SDO_GEOMETRY (

  2002, srid, NULL,

  SDO_ELEM_INFO_ARRAY (1, 2, 1),

  SDO_ORDINATE_ARRAY (

  first_x, first_y,

  next_x, next_y));

  RETURN l;

  END;

  4.修改几何体

  以下示例均可以从资料中获取，涉及到添加或移除点时需要注意各点在数组中存储的实际位置

  4.1 从线段删除某点

  --移除点的函数

  create or replace FUNCTION remove_point (

  geom SDO_GEOMETRY, point_number NUMBER

  ) RETURN SDO_GEOMETRY

  IS

  g MDSYS.SDO_GEOMETRY; -- Updated Geometry

  d NUMBER; -- Number of dimensions in geometry

  p NUMBER; -- Index into ordinates array

  i NUMBER; -- Index into ordinates array

  BEGIN

  d := SUBSTR (geom.SDO_GTYPE, 1, 1);

  IF point_number = 0 THEN

  p := geom.SDO_ORDINATES.COUNT() - d + 1;

  ELSE

  p := (point_number-1) * d + 1;

  END IF;

  IF p > geom.SDO_ORDINATES.COUNT() THEN

  RETURN NULL;

  END IF;

  g := geom;

  FOR i IN p..g.SDO_ORDINATES.COUNT()-d LOOP

  g.SDO_ORDINATES(i) := g.SDO_ORDINATES(i+d);

  END LOOP;

  g.SDO_ORDINATES.TRIM (d);

  RETURN g;

  END;

  4.2 追加点

  • note:线的开始插入一点则传入1，最后一点则传入0

  create or replace FUNCTION add_to_line (

  geom SDO_GEOMETRY,

  point SDO_GEOMETRY,

  point_number NUMBER DEFAULT 0

  ) RETURN SDO_GEOMETRY

  IS

  g SDO_GEOMETRY; -- Updated geometry

  d NUMBER; -- Number of dimensions in line geometry

  t NUMBER; -- Geometry type

  p NUMBER; -- Insertion point into ordinates array

  i NUMBER;

  BEGIN

  d := SUBSTR (geom.SDO_GTYPE, 1, 1);

  IF point_number = 0 THEN

  p := geom.SDO_ORDINATES.COUNT() + 1;

  ELSE

  p := (point_number-1) * d + 1;

  END IF;

  IF point_number <> 0 THEN

  IF p > geom.SDO_ORDINATES.LAST()

  OR p < geom.SDO_ORDINATES.FIRST() THEN

  RAISE_APPLICATION_ERROR (-20000, 'Invalid insertion point');

  END IF;

  END IF;

  g := geom;

  g.SDO_ORDINATES.EXTEND(d);

  FOR i IN REVERSE p..g.SDO_ORDINATES.COUNT()-d LOOP

  g.SDO_ORDINATES(i+d) := g.SDO_ORDINATES(i);

  END LOOP;

  g.SDO_ORDINATES(p) := point.SDO_POINT.X;

  g.SDO_ORDINATES(p+1) := point.SDO_POINT.Y;

  IF d = 3 THEN

  g.SDO_ORDINATES(p+2) := point.SDO_POINT.Z;

  END IF;

  RETURN g;

  END;

  5.构造通用函数——从几何体中提取信息

  5.1 计算几何体中的点数

  • –获取点的个数函数

    create or replace FUNCTION get_num_points (

    g SDO_GEOMETRY)

    RETURN NUMBER

    DETERMINISTIC

    IS

    BEGIN

    RETURN g.SDO_ORDINATES.COUNT() / SUBSTR(g.SDO_GTYPE,1,1);

    END;

  5.2 从线串中某位置获取一点

  • Getting the First,Middle, and Last Points of a Line String

  -- Getting the first point of a line string

  SELECT get_point(geom) p

  FROM us_interstates

  WHERE interstate='I95';

  -- Getting the last point of a line string

  SELECT get_point(geom, get_num_points(geom)) p

  FROM us_interstates

  WHERE interstate='I95';

  -- Getting the middle point of a line string

  SELECT get_point(geom, ROUND(get_num_points(geom)/2)) p

  FROM us_interstates

  WHERE interstate='I95';

  5.3 获取几何体中的一个点

  - Function to Extract a Point from a Geometry
    `CREATE OR REPLACE FUNCTION get_point (`
      `geom SDO_GEOMETRY, point_number NUMBER DEFAULT 1`
    `) RETURN SDO_GEOMETRY`
    `IS`
      `g SDO_GEOMETRY;       -- Updated Geometry`
      `d NUMBER;             -- Number of dimensions in geometry`
      `p NUMBER;             -- Index into ordinates array`
      `px NUMBER;            -- X of extracted point`
      `py NUMBER;            -- Y of extracted point`
  
  `BEGIN`
    `-- Get the number of dimensions from the gtype`
    `d := SUBSTR (geom.SDO_GTYPE, 1, 1);`
  
    `-- Verify that the point exists`
    `IF point_number < 1`
    `OR point_number > geom.SDO_ORDINATES.COUNT()/d THEN`
      `RETURN NULL;`
    `END IF;`
  
    `-- Get index in ordinates array`
    `p := (point_number-1) * d + 1;`
  
    `-- Extract the X and Y coordinates of the desired point`
    `px := geom.SDO_ORDINATES(p);`
    `py := geom.SDO_ORDINATES(p+1);`
  
    `-- Construct and return the point`
    `RETURN`
      `SDO_GEOMETRY (`
        `2001,`
        `geom.SDO_SRID,`
        `SDO_POINT_TYPE (px, py, NULL),`
        `NULL, NULL);`
  `END;`
             

  六、sql实例

  以下实例，尤其是第三个，作业实现，涉及到自建函数、数据源等等，脱离本机环境则无法运行，仅供参考

  其中待改进的地方颇多（包括方法策略），有错请忽略

  为了便于多次测试且不影响数据库原数据，代码中都有先删除表再新建临时表的操作

  1.道格拉斯扑克simplify——线压缩

  1.1 测试结果

  • 原图

  

  • 压缩效果 ，阈值分别为10km 30km 100km

    

    

    

  1.2 代码

  drop table copy_us_interstates;

  create table copy_us_interstates as (select * from us_interstates);

  delete from user_sdo_geom_metadata where table_name='COPY_US_INTERSTATES';

  INSERT INTO user_sdo_geom_metadata VALUES (

  ​

  'copy_us_interstates',

  ​

  'geom',

  ​

  MDSYS.SDO_DIM_ARRAY(

  ​

  MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),

  ​

  MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)

  ​

  ),8307 );

  declare

  ​

  threshold number;

  ​

  n number;

  begin

  ​

  threshold := 60000;

  ​

  select count(*) into n from copy_us_interstates;

  ​

  for i in 1..n loop

  ​

  update copy_us_interstates set GEOM = SDO_UTIL.SIMPLIFY(GEOM,threshold,0.0000005)

  ​

  WHERE id = i;

  ​

  end loop;

  ​

  commit;

  end;

  2.线面缓冲区生成buffer

  2.1 结果-点、线缓冲区

  

  

  2.2 代码

  --脚本1

  drop table buffer_table;

  CREATE TABLE buffer_table AS SELECT NAME,

  SDO_GEOM.SDO_BUFFER(A.geometry, 0.5, 0.5, 'arc_tolerance=0.005 unit=mile') geom FROM points_dtz A;

  --添加空间信息元数据（可选）

  delete from user_sdo_geom_metadata where table_name='buffer_table';

  INSERT INTO user_sdo_geom_metadata VALUES (

  ​

  'buffer_table',

  ​

  'geometry',

  ​

  MDSYS.SDO_DIM_ARRAY(

  ​

  MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),

  ​

  MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)

  ​

  ),

  8307 );

  --测试,点缓冲区，地图视图运行

  SELECT * FROM buffer_table;

  SELECT * FROM points_dtz;

  ``

  --脚本2

  `drop table buffer_table;`
  
  `CREATE TABLE buffer_table AS SELECT ID,`
  
  `SDO_GEOM.SDO_BUFFER(A.geometry, 0.5, 0.5, 'arc_tolerance=0.005 unit=mile') geom FROM lineforbuffer A;`
  
  `--添加空间信息元数据（可选）`
  
  `delete from user_sdo_geom_metadata where table_name='buffer_table';`
  
  `INSERT INTO user_sdo_geom_metadata VALUES (`
  
  ​    `'buffer_table',` 
  
  ​    `'geometry',` 
  
  ​    `MDSYS.SDO_DIM_ARRAY(`   
  
  ​        `MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),`    
  
  ​        `MDSYS.SDO_DIM_ELEMENT('Y', -90, 90, 0.05)` 
  
  ​    `),`
  
  `8307 );`
  
  `--测试，线缓冲区，地图视图运行`
  
  `SELECT * FROM buffer_table;`
  
  `SELECT * FROM lineforbuffer;`
             

  3.*ArcGIS-smooth功能粗实现

  3.1 算法简介

  简介，更详细的请自行百度

  ​ 通过使用三次B样条曲线方程对线条进行平滑处理，而平滑处理可分为近似拟合和插值拟合两种。其中近似拟合是不过特征点，求出近似的平滑曲线；而插值拟合是通过特征点，需要经过反算得到各特征点的控制点，再通过近似拟合获得过特征点的平滑曲线。
  • B样条曲线的总方程为： P ( t ) = ∑ i = 0 n P i F i , k ( t ) \mathrm{P}(\mathrm{t})=\sum_{i=0}^{n} P_{i} F_{i, k}(t) P(t)=∑i=0n​Pi​Fi,k​(t) ①
  • 其中 P i j P_{ij} Pij​是控制曲线的特征点 F i , k ( t ) F_{i, k}(t) Fi,k​(t)则是 K 阶 B ​ K阶B​ K阶B​样条基函数。

  3.1.1 三次B样条曲线方程基函数

  F i , k ( t ) = 1 k ! ∑ m = 0 k − i ( − 1 ) m ( m k + 1 ) ( t + k − m − j ) k F_{i, k}(t)=\frac{1}{k !} \sum_{m=0}^{k-i}(-1)^{m}\left(\begin{array}{c}{m} \\ {k+1}\end{array}\right)(t+k-m-j)^{k} Fi,k​(t)=k!1​∑m=0k−i​(−1)m(mk+1​)(t+k−m−j)k ②

  其中 ( m k + 1 ) ​ \left(\begin{array}{c}{m} \\ {k+1}\end{array}\right)​ (mk+1​)​表示阶乘，化为本算法中式子为：

  F 0 , 3 ( t ) = 1 6 ( 1 − t ) 3 F_{0,3}(t)=\frac{1}{6}(1-t)^{3} F0,3​(t)=61​(1−t)3

  F 1 , 3 ( t ) = 1 6 ( 3 t 3 − 6 t 2 + 4 ) F_{1,3}(t)=\frac{1}{6}\left(3 t^{3}-6 t^{2}+4\right) F1,3​(t)=61​(3t3−6t2+4)

  F 2 , 3 ( t ) = 1 6 ( − 3 t 3 + 3 t 2 + 3 t + 4 ) F_{2,3}(t)=\frac{1}{6}\left(-3 t^{3}+3 t^{2}+3 t+4\right) F2,3​(t)=61​(−3t3+3t2+3t+4)

  F 3 , 3 ( t ) = 1 6 t 3 F_{3,3}(t)=\frac{1}{6} t^{3} F3,3​(t)=61​t3

  将以上基函数代入①中，就是：

  P ( t ) = P 0 ∗ F 0 , 3 ( t ) + P 1 ∗ F 1 , 3 ( t ) + P 2 ∗ F 2 , 3 ( t ) + P 3 ∗ F 3 , 3 ( t ) P(t)=P_{0} * F_{0,3}(t)+P_{1} * F_{1,3}(t)+P_{2} * F_{2,3}(t)+P_{3} * F_{3,3}(t) P(t)=P0​∗F0,3​(t)+P1​∗F1,3​(t)+P2​∗F2,3​(t)+P3​∗F3,3​(t) ③

  3.1.2 线平滑

  （1）近似拟合

  使用游标遍历数据点，每四个点为一组，计算每个点的拟合时需要用到它的三个后继点，代入式③中可求得其一段三次B样条曲线，起始点在P_0，终点在P_1，每条曲线中等间隔取11个点用于绘制曲线，只计算到倒数第四个点。

  （2）插值拟合

  同样使用游标遍历数据点，反求每个点的控制点，需要每个点的前驱一个点和后继两个点作为计算数据。计算第一个点的控制点是把其本身作为前驱进行计算；计算最后两个点时，缺少后继时将其本身顺位作为后继进行计算。求得控制点后，再根据控制点进行线要素的近似拟合便可获得经过特征点的平滑结果。

  3.1.3 面平滑

  （1）近似拟合

  使用游标遍历要素点，每四个点为一组，计算每个点的拟合时需要用到它的三个后继点，代入式③中可求得其一段三次B样条曲线，起始点在P_0，终点在P_1，每条曲线中等间隔取11个点用于绘制曲线；四个游标初始化时，提前储存前三个点的数据，当游标走到倒数三个点时，使用初始三个点进行辅助运算。

  （2）插值拟合

  同样使用游标遍历数据点，反求每个点的控制点，需要每个点的前驱一个点和后继两个点作为计算数据。因为面是闭合几何体，采用近似拟合类似的方法存储前三个点的数据，对所有点进行计算得控制点。求得控制点后，再根据控制点进行面要素的近似拟合便可获得经过特征点的平滑结果。

  3.2 实现策略

  几何数据处理不便，同时为了匹配组员写的数学计算算法（小组分工实现），采用以下一个较为笨重繁琐的流程实现了该过程：
  • 算法策略图（解释略-看代码）

  

  3.3 处理面的代码

  线的类似、冗余多，略

  `drop table temptable;`
  
  `create table temptable`
  
  `(`
  
  ​    `id number primary key,`
  
  ​    `x float,`
  
  ​    `y float,`
  
  ​    `geom sdo_geometry`
  
  `);`
  
  `` 
  
  `drop table test;`
  
  `create table test as (select * from testsmooth);`
  
  `` 
  
  `update test set geometry = feature_to_table(geometry)` 
  
  `WHERE id = 0;`
  
  `--select * from temptable`
  
  `--select * from to_temp;`
  
  `--插值计算过程`
  
  `drop table to_temp;` 
  
  `create table to_temp(`
  
  `id number primary key,`
  
  `x binary_double,`
  
  `y binary_double`
  
  `) ;`
  
  `declare`
  
    `-- Local variables here`
  
    `t     float := 0;`
  
    `to_id number := 1;`
  
    `a1    float := 0;`
  
    `a2    float := 0;`
  
    `a3    float := 0;`
  
    `a4    float := 0;`
  
    `lx1   temptable.x%type;`
  
    `lx2   temptable.x%type;`
  
    `lx3   temptable.x%type;`
  
    `ly1   temptable.x%type;`
  
    `ly2   temptable.x%type;`
  
    `ly3   temptable.x%type;`
  
    `/*初始四个游标推进时将前面几个点存储*/`
  
    `x  temptable.x%type;`
  
    `y  temptable.y%type;`
  
    `x1 temptable.x%type;`
  
    `x2 temptable.x%type;`
  
    `x3 temptable.x%type;`
  
    `x4 temptable.x%type;`
  
    `y1 temptable.y%type;`
  
    `y2 temptable.y%type;`
  
    `y3 temptable.y%type;`
  
    `y4 temptable.y%type;`
  
  
  
    `cursor cur1 is`
  
  ​    `select x, y from temptable;`
  
    `cursor cur2 is`
  
  ​    `select x, y from temptable;`
  
    `cursor cur3 is`
  
  ​    `select x, y from temptable;`
  
    `cursor cur4 is`
  
  ​    `select x, y from temptable;`
  
  `begin`
  
    `-- Test statements here`
  
  
  
    `open cur4;`
  
    `fetch cur4`
  
  ​    `into x4, y4;`
  
    `lx1 := x4;`
  
    `ly1 := y4;`
  
    `fetch cur4`
  
  ​    `into x4, y4;`
  
    `lx2 := x4;`
  
    `ly2 := y4;`
  
    `fetch cur4`
  
  ​    `into x4, y4;`
  
    `lx3 := x4;`
  
    `ly3 := y4;`
  
    `fetch cur4`
  
  ​    `into x4, y4;`
  
    `open cur3;`
  
    `fetch cur3`
  
  ​    `into x3, y3;`
  
    `fetch cur3`
  
  ​    `into x3, y3;`
    `fetch cur3`
  ​    `into x3, y3;`
    `open cur2;`
    `fetch cur2`
  ​    `into x2, y2;`
    `fetch cur2`
  ​    `into x2, y2;`
    `open cur1;`
    `fetch cur1`
  ​    `into x1, y1;`
  
    `/*游标就位和初始值赋值*/`
  
    `while cur1%found loop`
  ​    `if cur4%notfound then`
  ​      `if cur3%notfound then`
  ​        `if cur2%notfound then`
  
  ​          `/*计算最后一个点*/`
  ​          `t := 0;`
  ​          `FOR int in 1 .. 11 LOOP`
  ​            `a1 := POWER((1 - t), 3) / 6;`
  ​            `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;`
  ​            `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;`
  ​            `a4 := POWER(t, 3) / 6;`
  ​            `x  := a1 * x1 + a2 * lx1 + a3 * lx2 + a4 * lx3;`
  ​            `y  := a1 * y1 + a2 * ly1 + a3 * ly2 + a4 * ly3;`
  ​            `insert into to_temp (id, x, y) values (to_id, x, y);`
  ​            `to_id := to_id + 1;`
  ​            `t     := t + 0.1;`
  ​          `end loop;`
  ​        `else`
  
  ​          `/*计算倒数第二个点*/`
  ​          `t := 0;`
  ​          `FOR int in 1 .. 11 LOOP`
  ​            `a1 := POWER((1 - t), 3) / 6;`
  ​            `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;`
  ​            `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;`
  ​            `a4 := POWER(t, 3) / 6;`
  ​            `x  := a1 * x1 + a2 * x2 + a3 * lx1 + a4 * lx2;`
  ​            `y  := a1 * y1 + a2 * y2 + a3 * ly1 + a4 * ly2;`
  ​            `insert into to_temp (id, x, y) values (to_id, x, y);`
  ​            `to_id := to_id + 1;`
  ​            `t     := t + 0.1;`
  ​          `end loop;`
  ​        `end if;`
  ​      `else`
  
  ​        `/*计算倒数第三个点*/`
  ​        `t := 0;`
  ​        `FOR int in 1 .. 11 LOOP`
  ​          `a1 := POWER((1 - t), 3) / 6;`
  ​          `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;`
  ​          `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;`
  ​          `a4 := POWER(t, 3) / 6;`
  ​          `x  := a1 * x1 + a2 * x2 + a3 * x3 + a4 * lx1;`
  ​          `y  := a1 * y1 + a2 * y2 + a3 * y3 + a4 * ly1;`
  ​          `insert into to_temp (id, x, y) values (to_id, x, y);`
  ​          `to_id := to_id + 1;`
  ​          `t     := t + 0.1;`
  ​        `end loop;`
  ​      `end if;`
  
  ​    `else`
  ​      `/*计算一般点*/`
  ​      `t := 0;`
  ​      `FOR int in 1 .. 11 LOOP`
  ​        `a1 := POWER((1 - t), 3) / 6;`
  ​        `a2 := (3 * POWER(t, 3) - 6 * POWER(t, 2) + 4) / 6;`
  ​        `a3 := (-3 * POWER(t, 3) + 3 * POWER(t, 2) + 3 * t + 1) / 6;`
  ​        `a4 := POWER(t, 3) / 6;`
  ​        `x  := a1 * x1 + a2 * x2 + a3 * x3 + a4 * x4;`
  ​        `y  := a1 * y1 + a2 * y2 + a3 * y3 + a4 * y4;`
  ​        `insert into to_temp (id, x, y) values (to_id, x, y);`
  ​        `to_id := to_id + 1;`
  ​        `t     := t + 0.1;`
  ​      `end loop;`
  ​    `end if;`
  ​    `fetch cur1`
  ​      `into x1, y1;`
  ​    `fetch cur2`
  ​      `into x2, y2;`
  ​    `fetch cur3`
  ​      `into x3, y3;`
  ​    `fetch cur4`
  ​      `into x4, y4;`
    `end loop;`
    `commit;`
  `end;`
  `\--`
  
  `declare`
  `n1 number;`
  `n2 number;`
  `px number;`
  `py number;`
  
  `BEGIN`
  ​    `select get_num_points(geometry) into n1 from test WHERE id =0;`
  ​    `select count(*) into n2 from to_temp;`
  ​    `for i in 1..n1 loop`
  ​        `update test set geometry = remove_point(geometry,0) where id =0;`
  ​    `end loop;`
  ​    `for i in 1..n2 loop`
  ​        `select x into px from to_temp where id=i;`
  ​        `select y into py from to_temp where id=i;`
  ​        `update test set geometry = add_to_line(geometry,point(px,py),0) where id =0;`
  ​    `end loop;`
  `end;`
  `` 
  `delete from user_sdo_geom_metadata where table_name='TEST';`
  `INSERT INTO user_sdo_geom_metadata VALUES (`
  ​    `'TEST',    ---含有空间字段的表名`
  ​    `'geometry',    ---字段名（列名）`
  ​    `MDSYS.SDO_DIM_ARRAY(`   
  ​        `MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),`    
  ​        `MDSYS.SDO_DIM_ELEMENT('Y', 0, 90, 0.05)` 
  ​    `),`
  ​    `8307` 
  `);`
  `--drop index testindex;`
  `--create INDEX testindex ON test(geometry) INDEXTYPE IS MDSYS.SPATIAL_index;`
             

  3.4 结果

  

  • 面平滑后的图

  

  

  • 线平滑后的图

  

  4.Python连接及操作oracle

  以下不完善，待续，且仅能通过简单语句/脚本测试

  `user = input("请输入用户名： ");`
  
  `password = input("输入密码：");`
  
  `Filepath = input(“请添加文件路径：”);`
  
  `try:`
  
  ​    `os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8'`
  
  ​    `db = cx_Oracle.connect(user, password, 'localhost/orcl')`
  
  ​    `c = db.cursor()`
  
  ​    `with open('buffer.sql', 'r+') as f:`
  
  ​        `sql_list = f.read().split(';')[:-1];`
  
  ​        `sql_list = [x.replace('\n', ' ') if '\n' in x else x for x in sql_list]  # 将每段sql里的换行符改成空格`
  
  `for sql_item in sql_list:`
  
  ​        `print(sql_item)`
  
  ​        `c.execute(sql_item)`
  
  `except cx_Oracle.Error as e:`
  
  ​    `print(e)`
  
  `finally:`
  
  ​    `c.close()`
  
  ​    `db.commit()`
  
  op;`
  
  `end;`
             

  `delete from user_sdo_geom_metadata where table_name='TEST';`
  
  `INSERT INTO user_sdo_geom_metadata VALUES (`
  
  ​    `'TEST',    ---含有空间字段的表名`
  
  ​    `'geometry',    ---字段名（列名）`
  
  ​    `MDSYS.SDO_DIM_ARRAY(`   
  
  ​        `MDSYS.SDO_DIM_ELEMENT('X', -180, 180, 0.05),`    
  
  ​        `MDSYS.SDO_DIM_ELEMENT('Y', 0, 90, 0.05)` 
  
  ​    `),`
  
  ​    `8307` 
  
  `);`
  
  `--drop index testindex;`
  
  `--create INDEX testindex ON test(geometry) INDEXTYPE IS MDSYS.SPATIAL_index;`
  
  
  ### 4.Python连接及操作oracle
  
  > 以下不完善，待续，且仅能通过简单语句/脚本测试
  
  ```python
  `user = input("请输入用户名： ");`
  
  `password = input("输入密码：");`
  
  `Filepath = input(“请添加文件路径：”);`
  
  `try:`
  
  ​    `os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.utf8'`
  
  ​    `db = cx_Oracle.connect(user, password, 'localhost/orcl')`
  
  ​    `c = db.cursor()`
  
  ​    `with open('buffer.sql', 'r+') as f:`
  
  ​        `sql_list = f.read().split(';')[:-1];`
  
  ​        `sql_list = [x.replace('\n', ' ') if '\n' in x else x for x in sql_list]  # 将每段sql里的换行符改成空格`
  
  `for sql_item in sql_list:`
  
  ​        `print(sql_item)`
  
  ​        `c.execute(sql_item)`
  
  `except cx_Oracle.Error as e:`
  
  ​    `print(e)`
  
  `finally:`
  
  ​    `c.close()`
  
  ​    `db.commit()`
  
  ​    `db.close()`