› 推特 ›阿里云技术文档›正文

空间对象访问函数_时空函数_SQL参考_时序时空数据库TSDB2.0_时序时空数据库TSDB

admin 阿里云技术文档 2020-02-11 174 0
阿里云服务器优惠

空间对象访问函数

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)
  8. SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')));
  9.  st_astext(st_boundary(st_geomfromtext('POLYGON((1 1,0 0, -1 1, 1 1))'))) 
  10. --------------------------------------------------------------------------
  11.  LINESTRING (1 1, 0 0, -1 1, 1 1)
  12. (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)
  7. SELECT ST_EndPoint('POINT(1 1)'::geometry) IS NULL AS is_null;
  8.  is_null 
  9. ---------
  10.  t
  11. (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)
  8. SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'::geometry));
  9.  st_astext(st_envelope(CAST('LINESTRING(0 0, 1 3)' AS geometry))) 
  10. ------------------------------------------------------------------
  11.  POLYGON ((0 0, 0 3, 1 3, 1 0, 0 0))
  12. (1 row)
  15. SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))'::geometry));
  16.  st_astext(st_envelope(CAST('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))' AS geometry))) 
  17. ------------------------------------------------------------------------------------------------
  18.  POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))
  19. (1 row)
  22. SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))'::geometry));
  23.  st_astext(st_envelope(CAST('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))' AS geometry))) 
  24. ------------------------------------------------------------------------------------------------------
  25.  POLYGON ((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))
  26. (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. ----------
  5. (1 row)
  8. 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;
  9.                     the_geom                     
  10. -------------------------------------------------
  11.  LINESTRING (-104 38, -106 38, -105 39, -104 38)
  12. (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)
  8. SELECT ST_IsClosed('LINESTRING(0 0, 0 1, 1 1, 0 0)'::geometry);
  9.  st_isclosed(CAST('LINESTRING(0 0, 0 1, 1 1, 0 0)' AS geometry)) 
  10. -----------------------------------------------------------------
  11.  t
  12. (1 row)
  15. SELECT ST_IsClosed('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))'::geometry);
  16.  st_isclosed(CAST('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))' AS geometry)) 
  17. -----------------------------------------------------------------------------------
  18.  f
  19. (1 row)
  22. SELECT ST_IsClosed('POINT(0 0)'::geometry);
  23.  st_isclosed(CAST('POINT(0 0)' AS geometry)) 
  24. ---------------------------------------------
  25.  t
  26. (1 row)
  29. SELECT ST_IsClosed('MULTIPOINT((0 0), (1 1))'::geometry);
  30.  st_isclosed(CAST('MULTIPOINT((0 0), (1 1))' AS geometry)) 
  31. -----------------------------------------------------------
  32.  t
  33. (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)
  8.  SELECT ST_IsEmpty(ST_GeomFromText('POLYGON EMPTY'));
  9.  st_isempty(st_geomfromtext('POLYGON EMPTY')) 
  10. ----------------------------------------------
  11.  t
  12. (1 row)
  15. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))'));
  16.  st_isempty(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) 
  17. -------------------------------------------------------------------
  18.  f
  19. (1 row)
  22. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) = false;
  23.  (st_isempty(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) = false) 
  24. -----------------------------------------------------------------------------
  25.  t
  26. (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)
  9. SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
  10.     FROM (SELECT 'LINESTRING(0 0, 0 1, 1 0, 1 1, 0 0)'::geometry AS the_geom) AS foo;
  11.  st_isring(the_geom) | st_isclosed(the_geom) | st_issimple(the_geom) 
  12. ---------------------+-----------------------+-----------------------
  13.  f                   | t                     | f
  14. (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)
  8. SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
  9.  st_issimple(st_geomfromtext('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)')) 
  10. -----------------------------------------------------------------------
  11.  f
  12. (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)
  7. --Geometry Collection Example - multis count as one geom in a collection
  8. 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)))'));
  9.  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)))')) 
  10. ---------------------------------------------------------------------------------------------------------------------------------------------------
  11.                                                                                                                                                  3
  12. (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)
  12. SELECT ST_AsText(ST_PointN(ST_GeometryFromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)'), -2));
  13.  st_astext(st_pointn(st_geometryfromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)'), - 2)) 
  14. -----------------------------------------------------------------------------------
  15.  POINT (1 1)
  16. (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)
  8. SELECT ST_StartPoint('POINT(0 1)'::geometry) IS NULL AS is_null;
  9.  is_null 
  10. ---------
  11.  t
  12. (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)
  8. SELECT ST_X(ST_Centroid(ST_GeomFromWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  9.  st_x(st_centroid(st_geomfromwkt('LINESTRING(1 2 3 4, 1 1 1 1)'))) 
  10. -------------------------------------------------------------------
  11.                                                                1.0
  12. (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)
  8. SELECT ST_Y(ST_Centroid(ST_GeomFromWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  9.  st_y(st_centroid(st_geomfromwkt('LINESTRING(1 2 3 4, 1 1 1 1)'))) 
  10. -------------------------------------------------------------------
  11.                                                                1.5
  12. (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)
版权声明

本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。

评论

-----