当前位置： 首页 > news >正文

Oracle Spatial构建点、线、面

news 来源：原创 2024/5/3 12:30:08

在ArcGIS中通过ArcSDE存储空间数据到Oracle中有多种存储方式，分别有：二进制Long Raw 、ESRI的ST_Geometry以及基于Oracle Spatial的SDO_Geometry等等。

最近我主要是学习基于Oracle Spatial的存储方式，通过这种存储方式的几何列Shape的字段类型为mdsys.sdo_geometry类型。

关于Oracle Spatial定义的SDO_GEOMETRY类型，我将单独在一章里来说明，个人觉得关于操作空间数据的SQL语句来说，相交于PostgreSQL、MS SQLServer麻烦一些，下面将给出在Oracle库中构建点、线、面等几何体的SQL语句：


------------单点

DECLARE

geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2001,---2001代表单点
null,
sdo_point_type
(
63918.6936868593,
39300.6724619204,
null),
null,
null
);

execute immediate 'update zd_test set shape=:gm where objectid=227944' using geom;

END;


select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227944


--------------多点

geom sdo_geometry;
BEGIN
geom:=sdo_geometry
(2005,---2005代表多点
null,
null,
sdo_elem_info_array(1,1,7),---"7"代表有七个点的坐标数据
sdo_ordinate_array( 63918.6936868593, 39300.6724619204,63918.7296493314, 39302.5029543953,63918.7576130323, 39304.3335865351, 63918.777577428, 39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709 )
);
execute immediate 'update zd_test set shape=:gm where objectid=227945' using geom;
END;


select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227945

 

-----------------折线串：由直线组成的折线串

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2002,---2002代表单线或曲线
null,
null,
sdo_elem_info_array(1,2,1),
sdo_ordinate_array(63918.6936868593, 39300.6724619204, 63918.7296493314, 39302.5029543953, 63918.7576130323, 39304.3335865351, 63918.777577428, 39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709, 63918.7774366272, 39313.4876188202, 63918.7574018344, 39315.3183549036, 63918.7293677405, 39317.1489859668)
);

execute immediate 'update zd_test set shape=:gm where objectid=227943' using geom;

END;


select objectid,shape from zd_test where objectid=227943

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227943

 

---------------单曲线

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2002,
null,
null,
sdo_elem_info_array(1,2,2),----2,2 代表由曲线片段组成的多义线，每一线段由三个点来描述：起点、任意在曲线段上的一点，终点，且前一个曲线终点是下一个曲线的起点
sdo_ordinate_array(40602883.52196759, 3497165.8231581002, 40602944.9893748, 3497397.0575331002, 40602901.083979294, 3497657.5621717945)
);

execute immediate 'update zd_test set shape=:gm where objectid=227937' using geom;

END;


select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227937

---------------z折线串

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2006,---2006代表多线或多曲线
null,
null,
sdo_elem_info_array(1,2,1,17,2,1),--"17"代表第二条直线第一个点的坐标在坐标序列中的位置
sdo_ordinate_array(63918.6936868593, 39300.6724619204, --第一条线
63918.7296493314, 39302.5029543953, 63918.7576130323, 39304.3335865351, 63918.777577428, 39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709,
63824.5720349896, 39287.4658036968, --第二条线
63824.4568945635, 39285.8607059794, 63824.3337761012, 39284.2562004966, 63824.3191716341, 39284.0725430048, 63818.6178073972, 39264.4770559283, 63818.3871845309, 39262.8932601417, 63818.1486480711, 39261.3106368899, 63817.9022039774, 39259.7292257135, 63817.6478584071, 39258.1490661232
)
);

execute immediate 'update zd_test set shape=:gm where objectid=227947' using geom;

END;


select objectid,shape from zd_test where objectid=227947

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=227947


--------------复杂多义线
DECLARE
geom sdo_geometry;
BEGIN

geom:=sdo_geometry(
2002,
NULL,
NULL,
sdo_elem_info_array(1,4,2, 1,2,1, 3,2,2), -- 复杂多义线

---有三个三元组，其中后两个是SDO_ELEM_INFO属性元素三元组，前一个为表述组合。具体参考 图4。
---第一个三元组【1,4,2】，根据4可以得到是个描述三元组，2表示有两个几何元素组成，即后两个三元组描述各自的几何A和几何B。
---第二个三元组【1,2,1】，为SDO_ELEM_INFO属性元素三元组，它描述的是几何A。根据 图4可以得到它是一条直线段，且该直线段的最后一个节点还是下一个几何B的开始点，即几何A和几何B有几何节点重合。
---第二个三元组【3,2,2】，为SDO_ELEM_INFO属性元素三元组，它描述的是几何B。根据 图4可以得到它是一条曲线段，该几何B的起点和几何A终点节点重合。
sdo_ordinate_array( 40602944.9893748, 3497397.0575331002,40602883.52196759, 3497165.8231581002,40602356.658564195, 3497423.4007337987, 40602421.052973494, 3497730.7377698943)
--sdo_ordinate_array(10,10, 10,14, 6,10, 14,10)
);

execute immediate 'update zd_test set shape=:gm where objectid=218950' using geom;

END;

select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=218950

--------------圆

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2003,
null,
null,
sdo_elem_info_array(1,1003,4),
sdo_ordinate_array(40603430.7963257,3497241.6246947944,40603417.9331055,3497762.5902709961,40604307.223693892,3498210.6824950948)
);

execute immediate 'update zd_test set shape=:gm where objectid=242137' using geom;

END;

select objectid,shape from zd_test where objectid=242137
select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=242137

--------------简单面

DECLARE
geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2003,
null,
null,
sdo_elem_info_array(1,1003,1),
sdo_ordinate_array(58184.2949999999, 39390.5210000016, 58208.6500000013, 39291.8900000025, 58499.0099999998, 39310.700000003, 58482.4699999997, 39409.8360000011, 58184.2949999999, 39390.5210000016)
);

execute immediate 'update zd_test set shape=:gm where objectid=242137' using geom;

END;


select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=242137


---------多边形：环（也叫带“洞”的多边形）

DECLARE

geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2003,
null,
null,
sdo_elem_info_array(1,1003,1,15,2003,1),-----第一个“1”代表外多边形的第一个坐标在坐标数组中的位置，最后一个“1”代表该多边形是由直线组成的多边形
-----其中1003表明对应的坐标序列组成的几何A为外多边形环(顺时针)；而2003表明对应的坐标序列组成的几何B为内多边形环(逆时针)
----15 表示几何B坐标序列开始的位置，也就是说从15开始的几何坐标组成几何B，而1到18组成几何A
sdo_ordinate_array(40604250.220275894,3498887.5979003981,40602839.3861084,3498132.3029174954,40602696.877685592,3497070.6145019978,40603886.8234863,3496137.183898896,40604549.488098189,3496557.5839232951,40604984.139099091,3497947.0416869968,40604250.220275894,3498887.5979003981,40604214.5930786,3496977.9838866964,40603430.7963257,3496956.6077270955,40603430.7963257,3497241.6246947944,40603417.9331055,3497762.5902709961,40604307.223693892,3498210.6824950948,40604556.613525391,3497483.8892821968,40604214.5930786,3496977.9838866964)
);

execute immediate 'update zd_test set shape=:gm where objectid=234137' using geom;

END;


select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=234137


----------多边形：多面


DECLARE

geom sdo_geometry;

BEGIN

geom:=sdo_geometry
(2007,--多面或多个多边形组成
null,
null,
sdo_elem_info_array(1,1003,1,9,1003,1),
---sdo_elem_info_array(1,1003,1,15,1003,1,29,1003,1),-----由三个面组成的元素组合
---sdo_ordinate_array(40602883.52196759, 3497165.8231581002, 40602944.9893748, 3497397.0575331002, 40602901.083979294, 3497657.5621717945, 40602684.4847361, 3497678.0513685942, 40602543.9877269, 3497408.7655408978, 40602605.455134094, 3497154.1149671972, 40602883.52196759, 3497165.8231581002, 40602356.658564195, 3497423.4007337987, 40602421.052973494, 3497730.7377698943, 40602301.045160994, 3497824.4023818, 40602116.642939195, 3497839.0373914987, 40602005.416376695, 3497640.0001600981, 40602107.861933395, 3497285.8309706002, 40602356.658564195, 3497423.4007337987, 40601762.4737498, 3497379.4953382984, 40601823.941157, 3497537.5545424, 40601900.043757096, 3497751.226783596, 40601815.1601512, 3497964.8991468996, 40601692.2253366, 3497959.0451429933, 40601490.260981292, 3497593.1677626, 40601616.1227365, 3497376.5683362931, 40601762.4737498, 3497379.4953382984 )
sdo_ordinate_array(63918.6936868593, 39300.6724619204, 63918.7296493314, 39302.5029543953, 63918.7576130323, 39304.3335865351,63918.6936868593, 39300.6724619204, 63918.777577428 ,39306.1643233876, 63918.7895421375, 39307.9951299983, 63918.7935069322, 39309.8259714115, 63918.7894717364, 39311.6568126709,63918.777577428, 39306.1643233876)
);

execute immediate 'update zd_test set shape=:gm where objectid=234169' using geom;

END;


select objectid,shape from zd_test

select objectid,SDO_UTIL.TO_WKTGEOMETRY(shape) as ShapeString from zd_test where objectid=234169

 

转载于:https://www.cnblogs.com/khfang/p/5782606.html

相关文章：

  • 【趣事】一根网线发起的攻击
  • UVA10976 Fractions Again?!
  • 关于ECC内存
  • shell脚本实例(2)
  • WinForm 窗体属性
  • Javascript弹出层-初探
  • Java json工具类，jackson工具类，ObjectMapper工具类
  • PostgreSQL 最佳实践 - 任意时间点恢复源码分析
  • 第一次作业p7 1-1 1-2 1-6
  • 华为VRRP配置
  • 你掌握的技术排第几？
  • 解决mysql 1062 主从错误
  • LINKEDSERVER 与 ALIAS
  • while、dowhile、switchcase 循环嵌套、穷举、迭代
  • Git安装
  • @jsonView过滤属性
  • [译]前端离线指南（上）
  • canvas绘制圆角头像
  • JavaSE小实践1：Java爬取斗图网站的所有表情包
  • JS创建对象模式及其对象原型链探究（一）：Object模式
  • learning koa2.x
  • socket.io+express实现聊天室的思考（三）
  • vue从创建到完整的饿了么（11）组件的使用（svg图标及watch的简单使用）
  • 道格拉斯-普克 抽稀算法 附javascript实现
  • 关于 Linux 进程的 UID、EUID、GID 和 EGID
  • 机器学习中为什么要做归一化normalization
  • ------- 计算机网络基础
  • 聊聊springcloud的EurekaClientAutoConfiguration
  • 人脸识别最新开发经验demo
  • 如何学习JavaEE,项目又该如何做？
  • 算法---两个栈实现一个队列
  • 鱼骨图 - 如何绘制？
  • 智能合约开发环境搭建及Hello World合约
  • 好程序员web前端教程分享CSS不同元素margin的计算 ...
  • 好程序员大数据教程Hadoop全分布安装（非HA)
  • ​比特币大跌的 2 个原因
  • $.ajax,axios,fetch三种ajax请求的区别
  • （06）金属布线——为半导体注入生命的连接
  • （C语言）共用体union的用法举例
  • (Demo分享)利用原生JavaScript-随机数-实现做一个烟花案例
  • （板子）A* astar算法，AcWing第k短路+八数码 带注释
  • （附源码）spring boot公选课在线选课系统 毕业设计 142011
  • （九十四）函数和二维数组
  • （论文阅读23/100）Hierarchical Convolutional Features for Visual Tracking
  • (图)IntelliTrace Tools 跟踪云端程序
  • (转载)CentOS查看系统信息|CentOS查看命令
  • ..回顾17，展望18
  • .htaccess 强制https 单独排除某个目录
  • .Net 高效开发之不可错过的实用工具
  • .NetCore项目nginx发布
  • .NET连接数据库方式
  • @transactional 方法执行完再commit_当@Transactional遇到@CacheEvict，你的代码是不是有bug！...
  • [ NOI 2001 ] 食物链
  • [ vulhub漏洞复现篇 ] Celery ＜4.0 Redis未授权访问+Pickle反序列化利用
  • [8-23]知识梳理：文件系统、Bash基础特性、目录管理、文件管理、文本查看编辑处理...