Gold Warehouse Database KingbaseGIS User Manual (6.2. Management Functions)

6.2. 管理函数

6.2.1. AddGeometryColumn

AddGeometryColumn—Add a geometry types of columns in an existing table.Default type modifier to define the field rather than constraints editor.可以传递一个falseBoolean value touse_typmodParameters to get based on the constraint conditions that the old ways.

用法

text AddGeometryColumn(varchar table_name, varchar column_name,
   integer srid, varchar type, integer dimension, boolean use_typmod=true);
text AddGeometryColumn(varchar schema_name, varchar table_name,
   varchar column_name,
   integer srid, varchar type, integer dimension, boolean use_typmod=true);
text AddGeometryColumn(varchar catalog_name, varchar schema_name,
   varchar table_name, varchar column_name,
   integer srid, varchar type, integer dimension, boolean use_typmod=true);

描述

Add a geometry types of columns in the table to a known.schema_name就是表的schema_name. SRID必须是KGISThe plug-in system tablesSPATIAL_REF_SYSInteger value existing in the.如果schema_nameOr does not exist in the currentsearch_pathValues are not visible,具体的SRID、geometryType or dimension value is invalid,都会抛出异常.

相关参考

DropGeometryColumn, DropGeometryTable, `On the view of geometric type column`_, `手动在geometry_columnsRegistered geometric data type column in the table`_

6.2.2. DropGeometryColumn

DropGeometryColumn — From a space to delete a geometric types of columns in the table.

用法：

text DropGeometryColumn(varchar table_name, varchar column_name);
text DropGeometryColumn(varchar schema_name, varchar table_name,
   varchar column_name);
text DropGeometryColumn(varchar catalog_name, varchar schema_name,
   varchar table_name, varchar column_name);

描述

From a space to delete a geometric types of columns in the table.注意schema_name是需要的,为了匹配geometry_columnsTable geometry type column.

注意

一个数据库可以有多个schema,In order to accurately express a field,正确的方式是：schema_name.table_name.column_name .

• 该方法实现了OpenGIS 关于SQL 1.1The simple feature specification OpenGIS Simple Features Implementation Specification for SQL 1.1.

• 这个函数支持3D对象,并且不会删除z坐标.

• 该方法支持CircularString和curveGeometric type fields

注意

既然geometry_columns是一个依赖于system catalogThe data information of view,因此可以使用ALTER TABLEThis syntax like other general types of the columns of the table to delete a geometric type column.

相关参考

AddGeometryColumn, DropGeometryTable, `On the view of geometric type column`_

6.2.3. DropGeometryTable

DropGeometryTable — 删除一个表及其在geometry_columns 中的引用.

语法

boolean DropGeometryTable(varchar table_name);
boolean DropGeometryTable(varchar schema_name, varchar table_name);
boolean DropGeometryTable(varchar catalog_name, varchar schema_name,
   varchar table_name);

描述

删除一个表及其在geometry_columns 中的引用.

注意

If the function is not setschema_name的值,将使用 current_schema()的值作为schema_name.

样例

SELECT DropGeometryTable ('my_schema','my_spatial_table');

my_schema.my_spatial_table dropped.

This is equivalent to above：

DROP TABLE my_schema.my_spatial_table;

相关参考

AddGeometryColumn, DropGeometryColumn, `On the view of geometric type column`_

6.2.4. Find_SRID

Find_SRID —The function of usage isfind_srid(a_db_schema, a_table,a_column),The return value of a function is a specified column intSRID值. This function is actually by querying theGEOMETRY_COLUMNS 表得到的.

用法

integer Find_SRID(varchar a_schema_name, varchar a_table_name,
   varchar a_geomfield_name);

描述

The function of usage isfind_srid(a_db_schema, a_table,a_column),The return value of a function is a specified column intSRID值. This function is actually by querying theGEOMETRY_COLUMNS表得到的. 如果geometryType column didn't pass the functionAddGeometryColumns()To correctly add,Is can't use this function to the column.

样例

SELECT Find_SRID('public', 'tiger_us_state_2007', 'the_geom_4269');

find_srid
----------
4269

参考

ST_SRID

6.2.5. Populate_Geometry_Columns

Populate_Geometry_Columns — 确保geometry类型的列是用typmod方式来定义的或者有合适的空间约束条件,这样才能保证这些列能被正确地注册到geometry_columns这个视图中. 默认情况下会将所有的非typmod定义的geometry类型列转换为typmod方式的,如果想用老的方式,需要把use_typmod值设为false.

语法

text Populate_Geometry_Columns(boolean use_typmod=true);
int Populate_Geometry_Columns(oid relation_oid, boolean use_typmod=true);

描述

确保geometry类型的列是用typmod方式来定义的或者有合适的空间约束条件,这样才能保证这些列能被正确地注册到geometry_columns这个视图中.

For backward compatibility and some space requirements such as table inheritance,Namely each index may have differentgeometry类型. The old way of constraint still support.If you want to use the old way,需要把use_typmod设置为false.After passing the parameter,geometryTypes of columns will be set to no type modifiers properties, 但是会有3个约束条件,这意味着每个geometryTypes of columns in a table at least3个约束：

• enforce_dims_the_geom –确保每个geometryType fields at the same dimension(参考 ST_NDims )

• enforce_geotype_the_geom -确保每个geometryType field type is the same(参考 GeometryType )

• enforce_srid_the_geom –确保每个geometryType field in the same projection space(参考 ST_SRID )

If you provide a tableoid,This function will try to determinesrid,dimensionValue and the table of allgeometry类型,And add the necessary constraints. 如果成功了,The correct record will be“插入”到geometry_cloumns视图中,Otherwise it will cause an exception and displays a specific error message.

If you provide a view ofoid,Like a tableoid一样,This function will try to determinesrid,dimensionValues and the view allgeometry类型. 如果成功了,The correct record will be“插入”到geometry_cloumns视图中,But don't do anything to constrain.

无参数方法（指的是返回值为text的第一种方法）Is a band parameters（返回值为int的）方法的封装. It first for each space type tables and views firsttruncate geometry_columns,And then reinsert togeometry_columns,For each table to add the appropriate space constraints. It returns in a database to find allgeometryThe sum of type column,And inserted into thegeometry_cloumnsThe total number of. Parametric version of the simple return is inserted into thegeometry_columns中的总记录数.

样例

CREATE TABLE public.myspatial_table(gid serial, geom geometry);
INSERT INTO myspatial_table(geom) VALUES(ST_GeomFromText('LINESTRING(1 2, 3 4)',4326) );

这里会使用 type modifiers,Must have the data in the table can execute the statement

SELECT Populate_Geometry_Columns('public.myspatial_table'::regclass);

populate_geometry_columns
--------------------------
1

\d myspatial_table

Table "public.myspatial_table"
Column       | Type                                  | Modifiers
-------------+---------------------------------------+---------------------------------------------------------------
gid          | integer                               | not null default nextval('myspatial_table_gid_seq'::regclass)
geom         | geometry(LineString,4326)             |

如果没有用typmodOr it has been a constraint,这里会改变geometryTypes of columns to use constraint. The following statements if you want to run successful,Need to have data in the table.

CREATE TABLE public.myspatial_table_cs(gid serial, geom geometry);
INSERT INTO myspatial_table_cs(geom)
VALUES(ST_GeomFromText('LINESTRING(1 2, 3 4)',4326) );
SELECT Populate_Geometry_Columns('public.myspatial_table_cs'::regclass, false);

populate_geometry_columns
--------------------------
1

\d myspatial_table_cs

Table "public.myspatial_table_cs"
Column      |Type      | Modifiers
------------+----------+------------------------------------------------------------------
gid         | integer  | not null default nextval('myspatial_table_cs_gid_seq'::regclass)
geom        | geometry |

Check constraints:
"enforce_dims_geom" CHECK (st_ndims(geom) = 2)
"enforce_geotype_geom" CHECK (geometrytype(geom) = 'LINESTRING'::text OR geom IS NULL)
"enforce_srid_geom" CHECK (st_srid(geom) = 4326)

6.2.6. UpdateGeometrySRID

UpdateGeometrySRID — Update in the table ageometryAll the geometric objects in the column of typeSRID,At the same time update geometric constraints and reference information in column. If the column type definition by adding constraint,The type definition will be updated.

语法

text UpdateGeometrySRID(varchar table_name, varchar column_name,
   integer srid);
text UpdateGeometrySRID(varchar schema_name, varchar table_name,
   varchar column_name, integer srid);
text UpdateGeometrySRID(varchar catalog_name, varchar schema_name,
   varchar table_name, varchar column_name, integer srid);

描述

Update in the table ageometryAll the geometric objects in the column of typeSRID,At the same time update geometric constraints and reference information in column. If the column type definition by adding constraint,The type definition will be updated.

注意

如果没有给出schema_name的值,该函数将使用 current_schema() 的值作为schema_name.

• 这个函数支持3D对象,并且不会删除z坐标.

• 该方法支持CircularString和curveGeometric type fields.

样例

The following statement will put the tableroads的列geom的SRID改为4326,No matter of change the column beforeSRId值是多少：

SELECT UpdateGeometrySRID('roads','geom',4326);

The sample above statement is equivalent to the followingDDL语句：

ALTER TABLE roads
ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 4326)
USING ST_SetSRID(geom,4326);

If the data is loaded when the projection is wrong,Want to one-time change projected coordinate system toWeb Mercator投影,可以使用下面的DDL语句,But there is no equivalentKGISManagement function can do that at once.

ALTER TABLE roads
ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 3857) USING
ST_Transform(ST_SetSRID(geom ,4326),3857) ;

相关参考

ST_SetSRID, ST_Transform