虚谷文档 v1.0.3

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


DROPGEOMETRYCOLUMN ​

📄字数 2.2K
👁️阅读量 加载中...

功能描述 ​

从空间表中删除几何列。

语法格式 ​

sql
VARCHAR DROPGEOMETRYCOLUMN(VARCHAR table_name, VARCHAR column_name);
VARCHAR DROPGEOMETRYCOLUMN(VARCHAR schema_name, VARCHAR table_name, VARCHAR column_name);
VARCHAR DROPGEOMETRYCOLUMN(VARCHAR catalog_name, VARCHAR schema_name, VARCHAR table_name, VARCHAR column_name);

参数说明 ​

  • catalog_name:数据库名称。设置为空串。
  • schema_name: 模式的名称。
  • table_name：表的名称。
  • column_name；列的名称。

函数返回类型 ​

VARCHAR 类型

使用说明 ​

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

示例 ​

sql
# DROPGEOMETRYCOLUMN
## 功能描述

从空间表中删除几何列。

## 语法格式

```sql
VARCHAR DROPGEOMETRYCOLUMN(VARCHAR table_name, VARCHAR column_name);
VARCHAR DROPGEOMETRYCOLUMN(VARCHAR schema_name, VARCHAR table_name, VARCHAR column_name);
VARCHAR DROPGEOMETRYCOLUMN(VARCHAR catalog_name, VARCHAR schema_name, VARCHAR table_name, VARCHAR column_name);

参数说明 ​

  • catalog_name:数据库名称。设置为空串。
  • schema_name: 模式的名称。
  • table_name：表的名称。
  • column_name；列的名称。

函数返回类型 ​

VARCHAR 类型

使用说明 ​

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

示例 ​

sql
SQL> CREATE SCHEMA my_schema;

Execute successful.
Use time:1 ms.

SQL> CREATE TABLE my_schema.my_spatial_table (id int);

Execute successful.
Use time:22 ms.

SQL> SELECT AddGeometryColumn ('my_schema','my_spatial_table','geom',4326,'POINT',2);

+-------------------------------------------------------------+
|                            EXPR1                            |
+-------------------------------------------------------------+
| MY_SCHEMA.MY_SPATIAL_TABLE.GEOM SRID:4326 TYPE:POINT DIMS:2 |
+-------------------------------------------------------------+

SQL> SELECT AddGeometryColumn ('my_schema','my_spatial_table','geom_c',4326,'POINT',2, false);

+---------------------------------------------------------------+
|                             EXPR1                             |
+---------------------------------------------------------------+
| MY_SCHEMA.MY_SPATIAL_TABLE.GEOM_C SRID:4326 TYPE:POINT DIMS:2 |
+---------------------------------------------------------------+

SQL> SELECT AddGeometryColumn ('my_schema','my_spatial_table','geomcp_c',4326,'CURVEPOLYGON',2, false);

+------------------------------------------------------------------------+
|                                 EXPR1                                  |
+------------------------------------------------------------------------+
| MY_SCHEMA.MY_SPATIAL_TABLE.GEOMCP_C SRID:4326 TYPE:CURVEPOLYGON DIMS:2 |
+------------------------------------------------------------------------+

SQL> SELECT SCH.SCHEMA_NAME,TAB.TABLE_NAME,COL.COL_NAME,COL.TYPE_NAME,COL.SCALE
           FROM DBA_COLUMNS COL
          LEFT JOIN DBA_TABLES TAB ON COL.TABLE_ID=TAB.TABLE_ID
          LEFT JOIN DBA_SCHEMAS SCH ON SCH.SCHEMA_ID=TAB.SCHEMA_ID
          WHERE SCH.SCHEMA_NAME ='MY_SCHEMA' AND TAB.table_name = 'MY_SPATIAL_TABLE';

+-------------+------------------+----------+-----------+---------+
| SCHEMA_NAME |    TABLE_NAME    | COL_NAME | TYPE_NAME |  SCALE  |
+-------------+------------------+----------+-----------+---------+
| MY_SCHEMA   | MY_SPATIAL_TABLE | ID       | INTEGER   | -1      |
| MY_SCHEMA   | MY_SPATIAL_TABLE | GEOM     | GEOMETRY  | 1107460 |
| MY_SCHEMA   | MY_SPATIAL_TABLE | GEOM_C   | GEOMETRY  | 1107460 |
| MY_SCHEMA   | MY_SPATIAL_TABLE | GEOMCP_C | GEOMETRY  | 1107496 |
+-------------+------------------+----------+-----------+---------+

SQL> SELECT DropGeometryColumn ('my_schema','my_spatial_table','geom_c');

+--------------------------------------------------------+
|                         EXPR1                          |
+--------------------------------------------------------+
| MY_SCHEMA.MY_SPATIAL_TABLE.GEOM_C effectively removed. |
+--------------------------------------------------------+

SQL> SELECT DropGeometryColumn ('','my_schema','my_spatial_table','geomcp_c');

+----------------------------------------------------------+
|                          EXPR1                           |
+----------------------------------------------------------+
| MY_SCHEMA.MY_SPATIAL_TABLE.GEOMCP_C effectively removed. |
+----------------------------------------------------------+

SQL> SELECT SCH.SCHEMA_NAME,TAB.TABLE_NAME,COL.COL_NAME,COL.TYPE_NAME,COL.SCALE
           FROM DBA_COLUMNS COL
          LEFT JOIN DBA_TABLES TAB ON COL.TABLE_ID=TAB.TABLE_ID
          LEFT JOIN DBA_SCHEMAS SCH ON SCH.SCHEMA_ID=TAB.SCHEMA_ID
          WHERE SCH.SCHEMA_NAME ='MY_SCHEMA' AND TAB.table_name = 'MY_SPATIAL_TABLE';

+-------------+------------------+----------+-----------+---------+
| SCHEMA_NAME |    TABLE_NAME    | COL_NAME | TYPE_NAME |  SCALE  |
+-------------+------------------+----------+-----------+---------+
| MY_SCHEMA   | MY_SPATIAL_TABLE | ID       | INTEGER   | -1      |
| MY_SCHEMA   | MY_SPATIAL_TABLE | GEOM     | GEOMETRY  | 1107460 |
+-------------+------------------+----------+-----------+---------+