UPDATEGEOMETRYSRID

📄字数 1.2K

👁️阅读量 加载中...

功能描述

更新几何列中所有要素的SRID。

语法格式

VARCHAR UPDATEGEOMETRYSRID(VARCHAR table_name, VARCHAR column_name, INTEGER srid);
VARCHAR UPDATEGEOMETRYSRID(VARCHAR schema_name, VARCHAR table_name, VARCHAR column_name, INTEGER srid);
VARCHAR UPDATEGEOMETRYSRID(VARCHAR catalog_name, VARCHAR schema_name, VARCHAR table_name, VARCHAR column_name, INTEGER srid);

参数说明

• catalog_name:数据库名称。设置为空串。
• schema_name: 模式的名称。
• table_name：表的名称。
• column_name；列的名称。
• srid: 空间参考系统ID，必须是对 SPATIAL_REF_SYS 表中条目的整数值引用;

函数返回类型

VARCHAR 类型

使用说明

1. 该函数支持 3d 并且不会丢失 z-index。
2. 该函数支持圆形字符串和曲线。

示例

将几何体插入到道路表，使用EWKT格式查询：

SQL> CREATE TABLE roads (id int, geom GEOMETRY(LINESTRINGZ,4326));

SQL> INSERT INTO roads values(1,'SRID=4326;LINESTRING(0 0 2, 10 10 2)');

SQL> SELECT id,ST_AsEWKT(geom) FROM roads;

+----+--------+
| ID | EXPR1  |
+----+--------+
| 1  | <CLOB> |
+----+--------+

修改道路表的几何体SRID，再查询：

SQL> SELECT UpdateGeometrySRID('roads','geom',3857);

+----------------------------------------+
|                 EXPR1                  |
+----------------------------------------+
| SYSDBA.ROADS.GEOM SRID changed to 3857 |
+----------------------------------------+

SQL> SELECT id,ST_AsEWKT(geom) FROM roads;

+----+--------+
| ID | EXPR1  |
+----+--------+
| 1  | <CLOB> |
+----+--------+

从系统表中查询几何体的SRID、Z、M 值：

SQL> SELECT SCH.SCHEMA_NAME,TAB.TABLE_NAME,COL.COL_NAME,COL.TYPE_NAME,COL.SCALE,
     (BIT_AND(COL.SCALE::BIGINT,134217664::BIGINT)/256)::int AS srid,
     (BIT_AND(COL.SCALE::BIGINT,2::BIGINT)/2)::BIGINT AS Z,
     BIT_AND(COL.SCALE::BIGINT,1::BIGINT) AS M
       FROM ALL_COLUMNS COL
      LEFT JOIN ALL_TABLES TAB ON COL.TABLE_ID=TAB.TABLE_ID
      LEFT JOIN ALL_SCHEMAS SCH ON SCH.SCHEMA_ID=TAB.SCHEMA_ID
      WHERE SCH.SCHEMA_NAME ='SYSDBA' AND TAB.table_name = 'roads';

+-------------+------------+----------+-----------+--------+--------+---+---+
| SCHEMA_NAME | TABLE_NAME | COL_NAME | TYPE_NAME | SCALE  |  SRID  | Z | M |
+-------------+------------+----------+-----------+--------+--------+---+---+
| SYSDBA      | ROADS      | ID       | INTEGER   | -1     | 524288 | 1 | 1 |
| SYSDBA      | ROADS      | GEOM     | GEOMETRY  | 987402 | 3857   | 1 | 0 |
+-------------+------------+----------+-----------+--------+--------+---+---+