Golden Warehouse Database KingbaseGIS User Manual (6.6. Geometric Object Verification Function, 6.7. Spatial Reference System Function)

6.6. Geometry object validation function

6.6.1. ST_IsValid

ST_IsValid —如果ST_Geometry返回的值是满足WKT或EWKT或WKB或EWKB描述的,那么这个函数返回值是TRUE.

用法

boolean ST_IsValid(geometry g);
boolean ST_IsValid(geometry g, integer flags);

描述

测试ST_Geometry Whether the value of is in a canonical format.如果geometry是无效的, KingbaseES The object will be prompted. 更多关于OGCSimplicity and validity of normative definitions,参考 "Ensuring OpenGIS compliancy of geometries"

注意

SQL-MM 规范定义了ST_IsValid (NULL) 值为 0, 而KGIS返回值为 NULL.

• 该方法实现了规范 OpenGIS Simple Features Implementation Specification for SQL 1.1.

• 该方法实现了规范 SQL/MM specification. SQL-MM 3: 5.1.9

样例

SELECT ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) As good_line,
ST_IsValid(ST_GeomFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) As bad_poly;
--results
NOTICE: Self-intersection at or near point 0 0

good_line  | bad_poly
-----------+----------
t          | f

参考

ST_IsSimple , ST_IsValidReason , ST_IsValidDetail , ST_Summary

6.6.2. ST_IsValidDetail

ST_IsValidDetail — Returns whether the geometry type is valid and why and what the invalid geometry objects are

用法

valid_detail ST_IsValidDetail(geometry geom);
valid_detail ST_IsValidDetail(geometry geom, integer flags);

描述

Returns whether the geometry type is valid and why and what functions are the invalid geometry objects.通常用来替代ST_IsValid与 ST_IsValidReason,Used to generate invalidgeometryDetail report for the object.

参数flags是一个bit参数,It has the following values：

1: Consider the control formed by the self-intersecting ring to be valid,这也被称作 "the ESRI flag".pay attention to andOGC is contrary to the geometric specification.

样例

--First 3 Rejects from a successful quintuplet experiment
SELECT gid, reason(ST_IsValidDetail(the_geom)), ST_AsText(location(ST_IsValidDetail( the_geom))) as location
FROM
(SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), array_agg(f.line)) As the_geom, gid
FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
FROM generate_series(-4,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,8) z1
WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
INNER JOIN (SELECT
ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1*1, z1*2) As line
FROM generate_series(-3,6) x1
CROSS JOIN generate_series(2,5) y1 CROSS
JOIN generate_series(1,10) z1
WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
GROUP BY gid, e.buff) As quintuplet_experiment
WHERE ST_IsValid(the_geom) = false
ORDER BY gid
LIMIT 3;

gid   | reason            | location
------+-------------------+-------------
5330  | Self-intersection | POINT(32 5)
5340  | Self-intersection | POINT(42 4.999999999999999)
5350  | Self-intersection | POINT(52 4.999999999999999)

--simple example
SELECT * FROM ST_IsValidDetail('LINESTRING(220227 150406,2220227 150407,222020 150410)');

valid  | reason | location
-------+--------+----------
t      |        |

参考

ST_IsValid, ST_IsValidReason

6.6.3. ST_IsValidReason

ST_IsValidReason —返回一个geometry是否是有效的,以及无效的原因

用法

text ST_IsValidReason(geometry geomA);
text ST_IsValidReason(geometry geomA, integer flags);

描述

返回一个geometry是否是有效的,以及无效的原因.与函数ST_IsValidUseful together,Can be used to find detailed reports that generate invalid geometry objects and the reasons for the invalidationflags可以使用的值,Please see functions ST_IsValidDetail 说明

样例

--First 3 Rejects from a successful quintuplet experiment
SELECT gid,
ST_IsValidReason(the_geom) as validity_info
FROM
(SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), array_agg(f.line)) As the_geom, gid
FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
FROM generate_series(-4,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,8) z1
WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
INNER JOIN (SELECT
ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1*1, z1*2) As line
FROM generate_series(-3,6) x1
CROSS JOIN generate_series(2,5) y1 CROSS
JOIN generate_series(1,10) z1
WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
GROUP BY gid, e.buff) As quintuplet_experiment
WHERE ST_IsValid(the_geom) = false
ORDER BY gid LIMIT 3;

gid   | validity_info
------+--------------------------
5330  | Self-intersection [32 5]
5340  | Self-intersection [42 5]
5350  | Self-intersection [52 5]

--simple example
SELECT ST_IsValidReason('LINESTRING(220227 150406,2220227 150407,222020 150410)');

st_isvalidreason
------------------
Valid Geometry

参考

ST_IsValid, ST_Summary

6.6.4. ST_MakeValid

ST_MakeValid —without discarding vertices,Attempt to convert invalid geometry to valid geometry

用法

geometry ST_MakeValid(geometry input);

描述

without discarding vertices,Attempt to convert invalid geometry to valid geometry. is already a valid geometry object,则不处理,直接返回.

支持的输入类型有: POINTS, MULTIPOINTS, LINESTRINGS, MULTILINESTRINGS, POLYGONS, MULTIPOLYGONS和 GEOMETRYCOLLECTIONS（Contains any of the preceding types as well as mixtures of them）.

If some or all dimensions are collapsed,The output geometry is a collection of lower-dimensional geometry objects or a simple polygon with self-intersecting lower-dimensional geometry objects may be returned as MULTI-几何对象.

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

参考

ST_IsValid ST_CollectionExtract

6.7. Spatial reference system function

6.7.1. ST_SetSRID

ST_SetSRID —put a geometric object SRID Set to a given integer value.

用法

geometry ST_SetSRID(geometry geom, integer srid);

描述

put a geometric object SRID Set to a given integer value.For easy query construction bounding box 时,需要用到它.

注意

This function does not do any kind of coordinate conversion,It just sets the spatial reference system of the objectSRID元数据信息.可以使用函数ST_Transformto project the specified geometric object into the new coordinate system.

• 该函数方法实现了规范 OpenGIS Simple Features Implementation Specification for SQL 1.1.

• 该函数支持CircularString和Curve几何类型对象.

样例

-- Mark a point as WGS 84 long lat --
SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611),4326) As wgs84long_lat;
-- the ewkt representation (wrap with ST_AsEWKT) -
SRID=4326;POINT(-123.365556 48.428611)
-- Mark a point as WGS 84 long lat and then transform to web mercator (Spherical Mercator) --
SELECT ST_Transform(ST_SetSRID(ST_Point(-123.365556, 48.428611),4326),3785) As spere_merc;
-- the ewkt representation (wrap with ST_AsEWKT) -
SRID=3785;POINT(-13732990.8753491 6178458.96425423)

参考

ST_AsEWKT , ST_Point , ST_SRID , ST_Transform , UpdateGeometrySRID

6.7.2. ST_SRID

ST_SRID —返回ST_Geometry值对应对象的SRID值,该值必须存在spatial_ref_sys表中.

用法

integer ST_SRID(geometry g1);

描述

返回ST_Geometry值对应对象的SRID值,该值必须存在spatial_ref_sys表中.

样例

SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
--result
4326

参考

ST_GeomFromText , ST_SetSRID , ST_Transform

6.7.3. ST_Transform

ST_Transform — 返回一个根据给定的SRID转换成新的坐标系对象.

用法

geometry ST_Transform(geometry g1, integer srid);

描述

返回一个根据给定的SRID转换成新的坐标系对象,SRID值必须存在于 SPATIAL_REF_SYS 表中.ST_Transform Functions are usually combined with functionsST_SetSRID()混淆. ST_TransformThe function actually converts the coordinates of a geometric object from one frame of reference to another,而函数ST_SetSRID() Just simple modification of the input geometry objectSRID值.

注意

If the same conversion is used multiple times,It is useful to add this function index on columns where the function is frequently used,This makes full use of the index.

样例

Change Mass state plane US feet geometry to WGS 84 long lat
SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450, 743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)) As wgs_geom;

wgs_geom
---------------------------
POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326
42.3903829478009,-71.1775844305465 42.3903826677917,-71.1775825927231
42.3902893647987,-71.177684 8522251 42.3902896512902));
(1 row)

--3D Circular String example
SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=2249;CIRCULARSTRING(743238 2967416 1,743238 2967450 2,743265 2967450 3,743265.625 2967416 3,743238 2967416 4)'),4326));

st_asewkt
--------------------------------------------------------------------------------------
SRID=4326;CIRCULARSTRING(-71.1776848522251 42.3902896512902 1,-71.1776843766326
42.3903829478009 2,-71.1775844305465 42.3903826677917 3, -71.1775825927231
42.3902893647987 3,-71.1776848522251 42.3902896512902 4)

An example of creating a functional index.For tables that are not sure which geometry objects will be recorded,最好的办法是使用 partial index 类型的索引,The index will be ignoredNULL类型几何对象,Save storage space and make indexes smaller and more efficient.

CREATE INDEX idx_the_geom_26986_parcels
ON parcels
USING gist
(ST_Transform(the_geom, 26986))
WHERE the_geom IS NOT NULL;

Configure the conversion method

With grid offsets,Coordinate transformation sometimes fails,例如：When no grid offset file is provided or the coordinates are not within the grid offset range defined by the file. 默认情况下,KGISAn error is thrown when no grid offset file is provided,But this way can be changed by changing the spatial_ref_sys table表中的proj4textvalue to configure eachSRID基准. 例如：proj4text参数+datum=NAD87是如下+nadgridsSimple equivalent for parameters：

[email protected],@alaska,@ntv2_0.gsb,@ntv1_can.dat

参数 @ prefix Meaning if no grid offset file is provided,将不会报错,But if the search to the end of the file list,No suitable file was found,将会报错. 相反,If it is guaranteed that the most basic documents have been provided,But after scanning all files, no corresponding conversion was found,The following parameters should be used：

[email protected],@alaska,@ntv2_0.gsb,@ntv1_can.dat,null

这里的 null The grid offset file is a valid grid offset file that covers all places,The effect is to ensure that there is no offset. Hence as a complete example,If you want to convert a reference frame that is not in the correct range to SRID=4267 in the coordinate reference system,And promise not to reportERROR,应该使用如下的方式：

UPDATE spatial_ref_sys SET proj4text = '+proj=longlat +ellps=clrk66 [email protected],
@alaska,@ntv2_0.gsb,@ntv1_can.dat,null +no_defs' WHERE srid = 4267;

参考

ST_AsText , ST_SetSRID , UpdateGeometrySRID