全部产品
云市场

空间对象访问函数

更新时间:2019-12-30 16:03:18

ST_GeometryType

描述

返回几何对象的类型名,比如: ‘ST_LineString’, ‘ST_Polygon’, ‘ST_MultiPoint’, 等等.

函数声明

  1. text ST_GeometryType(geometry geomA);

使用示例

  1. SELECT ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
  2. st_geometrytype(st_geomfromtext('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'))
  3. -------------------------------------------------------------------------------------------------
  4. ST_LineString
  5. (1 row)

ST_Boundary

描述

返回给定几何对象的边界。

函数声明

  1. geometry ST_Boundary(geometry geomA);

使用示例

  1. SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')));
  2. st_astext(st_boundary(st_geomfromtext('LINESTRING(1 1,0 0, -1 1)')))
  3. ----------------------------------------------------------------------
  4. MULTIPOINT ((1 1), (-1 1))
  5. (1 row)
  6. SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')));
  7. st_astext(st_boundary(st_geomfromtext('POLYGON((1 1,0 0, -1 1, 1 1))')))
  8. --------------------------------------------------------------------------
  9. LINESTRING (1 1, 0 0, -1 1, 1 1)
  10. (1 row)

ST_CoordDim

描述

返回给定几何对象的维度数。

函数声明

  1. integer ST_CoordDim(geometry geomA);

使用示例

  1. SELECT ST_CoordDim(ST_Point(1,2));
  2. st_coorddim(st_point(1, 2))
  3. -----------------------------
  4. 2
  5. (1 row)

ST_Dimension

描述

返回给定几何对象的固有维度。比如:Point是0,LineString是1,Polygon是2。

函数声明

  1. integer ST_Dimension(geometry g);

使用示例

  1. SELECT ST_Dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0))');
  2. st_dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0))')
  3. --------------------------------------------------------------------
  4. 1
  5. (1 row)

ST_EndPoint

描述

返回给定LineString参数的最后一个点,如果参数不是一个LineString,那么返回NULL。

函数声明

  1. boolean ST_EndPoint(geometry g);

使用示例

  1. SELECT ST_AsText(ST_EndPoint('LINESTRING(1 1, 2 2, 3 3)'::geometry));
  2. st_astext(st_endpoint(CAST('LINESTRING(1 1, 2 2, 3 3)' AS geometry)))
  3. -----------------------------------------------------------------------
  4. POINT (3 3)
  5. (1 row)
  6. SELECT ST_EndPoint('POINT(1 1)'::geometry) IS NULL AS is_null;
  7. is_null
  8. ---------
  9. t
  10. (1 row)

ST_Envelope

描述

返回给定几何对象的边界框。

函数声明

  1. geometry ST_Envelope(geometry g1);

使用示例

  1. SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
  2. st_astext(st_envelope(CAST('POINT(1 3)' AS geometry)))
  3. --------------------------------------------------------
  4. POINT (1 3)
  5. (1 row)
  6. SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'::geometry));
  7. st_astext(st_envelope(CAST('LINESTRING(0 0, 1 3)' AS geometry)))
  8. ------------------------------------------------------------------
  9. POLYGON ((0 0, 0 3, 1 3, 1 0, 0 0))
  10. (1 row)
  11. SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))'::geometry));
  12. st_astext(st_envelope(CAST('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))' AS geometry)))
  13. ------------------------------------------------------------------------------------------------
  14. POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))
  15. (1 row)
  16. SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))'::geometry));
  17. st_astext(st_envelope(CAST('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))' AS geometry)))
  18. ------------------------------------------------------------------------------------------------------
  19. POLYGON ((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))
  20. (1 row)

ST_ExteriorRing

描述

返回一个LineString,代表给定的Polygon参数的外边界。如果参数不是一个Polygon,就返回NULL。

函数声明

  1. geometry ST_ExteriorRing(geometry a_polygon);

使用示例

  1. select ST_ExteriorRing('POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))' :: geometry);
  2. st_exteriorring(CAST('POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))' AS geometry))
  3. ------------------------------------------------------------------------------------------
  4. LINESTRING (0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0)
  5. (1 row)

ST_GeometryN

描述

如果输入参数是:GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE,(MULTI)POLYGON 或 POLYHEDRALSURFACE,返回其给定的第n个几何对象(序号编码从1开始)。其它情况返回NULL。

函数声明

  1. geometry ST_GeometryN(geometry geomA, integer n);

使用示例

  1. SELECT n.col1, ST_AsText(ST_GeometryN(the_geom, n.col1 :: int)) As geomewkt FROM (
  2. VALUES (ST_GeomFromWKT('MULTIPOINT(1 2, 3 4, 5 6, 8 9)') ),
  3. ( ST_GeomFromWKT('MULTILINESTRING ((30.00 20.00, 45.00 40.00, 10.00 40.00), (15.00 5.00, 40.00 10.00, 10.00 20.00))') )) As foo(the_geom)
  4. CROSS JOIN generate_series(1,100) n
  5. WHERE n.col1 <= ST_NumGeometries(the_geom);
  6. col1 | geomewkt
  7. ------+----------------------------------
  8. 1 | POINT (1 2)
  9. 1 | LINESTRING (30 20, 45 40, 10 40)
  10. 2 | POINT (3 4)
  11. 2 | LINESTRING (15 5, 40 10, 10 20)
  12. 3 | POINT (5 6)
  13. 4 | POINT (8 9)
  14. (6 rows)

ST_InteriorRingN

描述

返回一个LineString代表输入Polygon参数的第n条内部边界圈(从1开始计数)。如果输入参数不是一个Polygon或者n超出了范围,则返回NULL。

函数声明

  1. geometry ST_InteriorRingN(geometry a_polygon, integer n);

使用示例

  1. SELECT ST_AsText(ST_InteriorRingN('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.658, -122.358 47.653))', 1)) As the_geom;
  2. the_geom
  3. ----------
  4. (1 row)
  5. SELECT ST_AsText(ST_InteriorRingN('POLYGON ((-102 37, -102 41, -109 41, -109 37, -102 37), (-104 38, -106 38, -105 39, -104 38))', 1)) As the_geom;
  6. the_geom
  7. -------------------------------------------------
  8. LINESTRING (-104 38, -106 38, -105 39, -104 38)
  9. (1 row)

ST_IsPolygonCCW

描述

如果输入Polygon参数的所有外边界都是逆时针的,所有内边界都是顺时针的,则返回true。

函数声明

  1. boolean ST_IsPolygonCCW(geometry geom);

使用示例

  1. SELECT ST_IsPolygonCCW('POLYGON ((-102 37, -102 41, -109 41, -109 37, -102 37), (-104 38, -106 38, -105 39, -104 38))');
  2. st_ispolygonccw('POLYGON ((-102 37, -102 41, -109 41, -109 37, -102 37), (-104 38, -106 38, -105 39, -104 38))')
  3. ------------------------------------------------------------------------------------------------------------------
  4. t
  5. (1 row)

ST_IsClosed

描述

如果输入几何对象是封闭的,则返回true。

函数声明

  1. boolean ST_IsClosed(geometry g);

使用示例

  1. SELECT ST_IsClosed('LINESTRING(0 0, 1 1)'::geometry);
  2. st_isclosed(CAST('LINESTRING(0 0, 1 1)' AS geometry))
  3. -------------------------------------------------------
  4. f
  5. (1 row)
  6. SELECT ST_IsClosed('LINESTRING(0 0, 0 1, 1 1, 0 0)'::geometry);
  7. st_isclosed(CAST('LINESTRING(0 0, 0 1, 1 1, 0 0)' AS geometry))
  8. -----------------------------------------------------------------
  9. t
  10. (1 row)
  11. SELECT ST_IsClosed('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))'::geometry);
  12. st_isclosed(CAST('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))' AS geometry))
  13. -----------------------------------------------------------------------------------
  14. f
  15. (1 row)
  16. SELECT ST_IsClosed('POINT(0 0)'::geometry);
  17. st_isclosed(CAST('POINT(0 0)' AS geometry))
  18. ---------------------------------------------
  19. t
  20. (1 row)
  21. SELECT ST_IsClosed('MULTIPOINT((0 0), (1 1))'::geometry);
  22. st_isclosed(CAST('MULTIPOINT((0 0), (1 1))' AS geometry))
  23. -----------------------------------------------------------
  24. t
  25. (1 row)

ST_IsEmpty

描述

如果输入参数是一个空的GeometryCollection、Polygon或Point等,返回true。

函数声明

  1. boolean ST_IsEmpty(geometry geomA);

使用示例

  1. SELECT ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
  2. st_isempty(st_geomfromtext('GEOMETRYCOLLECTION EMPTY'))
  3. ---------------------------------------------------------
  4. t
  5. (1 row)
  6. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON EMPTY'));
  7. st_isempty(st_geomfromtext('POLYGON EMPTY'))
  8. ----------------------------------------------
  9. t
  10. (1 row)
  11. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))'));
  12. st_isempty(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))'))
  13. -------------------------------------------------------------------
  14. f
  15. (1 row)
  16. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) = false;
  17. (st_isempty(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) = false)
  18. -----------------------------------------------------------------------------
  19. t
  20. (1 row)

ST_IsRing

描述

当输入几何对象的判定条件ST_IsClosed为true而且ST_IsSimple也为true时,本函数返回true。

函数声明

  1. boolean ST_IsRing(geometry g);

使用示例

  1. SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
  2. FROM (SELECT 'LINESTRING(0 0, 0 1, 1 1, 1 0, 0 0)'::geometry AS the_geom) AS foo;
  3. st_isring(the_geom) | st_isclosed(the_geom) | st_issimple(the_geom)
  4. ---------------------+-----------------------+-----------------------
  5. t | t | t
  6. (1 row)
  7. SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
  8. FROM (SELECT 'LINESTRING(0 0, 0 1, 1 0, 1 1, 0 0)'::geometry AS the_geom) AS foo;
  9. st_isring(the_geom) | st_isclosed(the_geom) | st_issimple(the_geom)
  10. ---------------------+-----------------------+-----------------------
  11. f | t | f
  12. (1 row)

ST_IsSimple

描述

当输入参数是一个没有自相交点的简单几何对象时,返回true。Simple的准确定义请参考OGC标准文档。

函数声明

  1. boolean ST_IsSimple(geometry geomA);

使用示例

  1. SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))'));
  2. st_issimple(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))'))
  3. --------------------------------------------------------------------
  4. t
  5. (1 row)
  6. SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
  7. st_issimple(st_geomfromtext('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'))
  8. -----------------------------------------------------------------------
  9. f
  10. (1 row)

ST_IsValid

描述

判断输入参数是否满足OGC标准中的Valid判定条件。Valid的判定标准请参考OGC标准文档。注意,TSDB 2.0 不接受 Invalid 空间对象,因此能被 TSDB 2.0 正确存储和处理的空间对象应该都是 Valid 的。

函数声明

  1. boolean ST_IsValid(geometry g);

使用示例

  1. SELECT ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) As good_line;
  2. good_line
  3. -----------
  4. t
  5. (1 row)

ST_NPoints

描述

返回一个几何对象中顶点的个数。

函数声明

  1. integer ST_NPoints(geometry g1);

使用示例

  1. SELECT ST_NPoints(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
  2. st_npoints(st_geomfromtext('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'))
  3. --------------------------------------------------------------------------------------------
  4. 4
  5. (1 row)

ST_NumGeometries

描述

如果输入对象是几何集合,如:GEOMETRYCOLLECTION (或者 MULTI*),则返回其中几何对象的个数。

函数声明

  1. integer ST_NumGeometries(geometry geom);

使用示例

  1. SELECT ST_NumGeometries(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
  2. st_numgeometries(st_geomfromtext('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'))
  3. --------------------------------------------------------------------------------------------------
  4. 1
  5. (1 row)
  6. --Geometry Collection Example - multis count as one geom in a collection
  7. SELECT ST_NumGeometries(ST_GeomFromWKT('GEOMETRYCOLLECTION(MULTIPOINT(-2 3 , -2 2), LINESTRING(5 5 ,10 10), POLYGON((-7 4.2,-7.1 5,-7.1 4.3,-7 4.2)))'));
  8. st_numgeometries(st_geomfromwkt('GEOMETRYCOLLECTION(MULTIPOINT(-2 3 , -2 2), LINESTRING(5 5 ,10 10), POLYGON((-7 4.2,-7.1 5,-7.1 4.3,-7 4.2)))'))
  9. ---------------------------------------------------------------------------------------------------------------------------------------------------
  10. 3
  11. (1 row)

ST_NumInteriorRings

描述

返回输入Polygon对象的内边界环的个数。

函数声明

  1. integer ST_NumInteriorRings(geometry a_polygon);

使用示例

  1. SELECT ST_NumInteriorRings('POLYGON ((-102 37, -102 41, -109 41, -109 37, -102 37), (-104 38, -106 38, -105 39, -104 38))');
  2. st_numinteriorrings('POLYGON ((-102 37, -102 41, -109 41, -109 37, -102 37), (-104 38, -106 38, -105 39, -104 38))')
  3. ----------------------------------------------------------------------------------------------------------------------
  4. 1
  5. (1 row)

ST_NumPoints

描述

返回一个LineString中包含的点的个数。

函数声明

  1. integer ST_NumPoints(geometry g1);

使用示例

  1. SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
  2. st_numpoints(st_geomfromtext('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'))
  3. ----------------------------------------------------------------------------------------------
  4. 4
  5. (1 row)

ST_PointN

描述

返回输入几何对象LineString或者环形LineString中的第n个点。如果n是负数,那么将从末尾向前计数。如果几何对象不是LineString,则返回NULL。

函数声明

  1. geometry ST_PointN(geometry a_linestring, integer n);

使用示例

  1. -- Extract all POINTs from a LINESTRING
  2. SELECT ST_AsText(ST_PointN(col1, generate_series(1, ST_NPoints(col1))))
  3. FROM ( VALUES ('LINESTRING(0 0, 1 1, 2 2)'::geometry) ) AS foo;
  4. st_astext(st_pointn(col1, generate_series(1, st_npoints(col1))))
  5. ------------------------------------------------------------------
  6. POINT (0 0)
  7. POINT (1 1)
  8. POINT (2 2)
  9. (3 rows)
  10. SELECT ST_AsText(ST_PointN(ST_GeometryFromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)'), -2));
  11. st_astext(st_pointn(st_geometryfromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)'), - 2))
  12. -----------------------------------------------------------------------------------
  13. POINT (1 1)
  14. (1 row)

ST_SRID

描述

返回给定几何对象的SRID(坐标参考系标识)。目前 TSDB 2.0 只支持 4326 坐标参考系。所以本函数总是返回4326。

函数声明

  1. integer ST_SRID(geometry g1);

使用示例

  1. SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)'));
  2. st_srid(st_geomfromtext('POINT(-71.1043 42.315)'))
  3. ----------------------------------------------------
  4. 4326
  5. (1 row)

ST_StartPoint

描述

返回一个LineString或环形LineString的起点,如果输入对象不是这两个类型的,则返回NULL。

函数声明

  1. geometry ST_StartPoint(geometry geomA);

使用示例

  1. SELECT ST_AsText(ST_StartPoint('LINESTRING(0 1, 0 2)'::geometry));
  2. st_astext(st_startpoint(CAST('LINESTRING(0 1, 0 2)' AS geometry)))
  3. --------------------------------------------------------------------
  4. POINT (0 1)
  5. (1 row)
  6. SELECT ST_StartPoint('POINT(0 1)'::geometry) IS NULL AS is_null;
  7. is_null
  8. ---------
  9. t
  10. (1 row)

ST_X

描述

返回一个几何坐标点的X坐标值。

函数声明

  1. float ST_X(geometry a_point);

使用示例

  1. SELECT ST_X(ST_GeomFromWKT('POINT(1 2)'));
  2. st_x(st_geomfromwkt('POINT(1 2)'))
  3. ------------------------------------
  4. 1.0
  5. (1 row)
  6. SELECT ST_X(ST_Centroid(ST_GeomFromWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  7. st_x(st_centroid(st_geomfromwkt('LINESTRING(1 2 3 4, 1 1 1 1)')))
  8. -------------------------------------------------------------------
  9. 1.0
  10. (1 row)

ST_Y

描述

返回输入坐标点的Y坐标值。

函数声明

  1. float ST_Y(geometry a_point);

使用示例

  1. SELECT ST_Y(ST_GeomFromWKT('POINT(1 2 3 4)'));
  2. st_y(st_geomfromwkt('POINT(1 2 3 4)'))
  3. ----------------------------------------
  4. 2.0
  5. (1 row)
  6. SELECT ST_Y(ST_Centroid(ST_GeomFromWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  7. st_y(st_centroid(st_geomfromwkt('LINESTRING(1 2 3 4, 1 1 1 1)')))
  8. -------------------------------------------------------------------
  9. 1.5
  10. (1 row)

ST_Z

描述

返回输入坐标点的Z坐标值。目前 TSDB 2.0 不支持三维或四维坐标点,因此本函数总是返回 null。

函数声明

  1. float ST_Z(geometry a_point);

使用示例

  1. SELECT ST_Z(ST_GeomFromWKT('POINT(1 2 3 4)')) is null;
  2. (st_z(st_geomfromwkt('POINT(1 2 3 4)')) IS NULL)
  3. --------------------------------------------------
  4. t
  5. (1 row)