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

空间对象访问函数_空间函数SQL参考_时空数据库_时序时空数据库TSDB

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

空间对象访问函数

GeometryType

描述

返回几何对象的类型名，比如: ‘LINESTRING’, ‘POLYGON’, ‘MULTIPOINT’, 等等.

函数声明

  1. text GeometryType(geometry  geomA);

使用示例

  1. SELECT GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
  2.  geometrytype
  3. --------------
  4.  LINESTRING

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
  3. -----------
  4. MULTIPOINT(1 1,-1 1)
  6. SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')));
  7. st_astext
  8. ----------
  9. LINESTRING(1 1,0 0,-1 1,1 1)
  11. --Using a 3d polygon
  12. SELECT ST_AsEWKT(ST_Boundary(ST_GeomFromEWKT('POLYGON((1 1 1,0 0 1, -1 1 1, 1 1 1))')));
  14. st_asewkt
  15. -----------------------------------
  16. LINESTRING(1 1 1,0 0 1,-1 1 1,1 1 1)
  18. --Using a 3d multilinestring
  19. SELECT ST_AsEWKT(ST_Boundary(ST_GeomFromEWKT('MULTILINESTRING((1 1 1,0 0 0.5, -1 1 1),(1 1 0.5,0 0 0.5, -1 1 0.5, 1 1 0.5) )')));
  21. st_asewkt
  22. ----------
  23. MULTIPOINT(-1 1 1,1 1 0.75)

ST_CoordDim

描述

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

函数声明

  1. integer ST_CoordDim(geometry  geomA);

使用示例

  1. SELECT ST_CoordDim('CIRCULARSTRING(1 2 3, 1 3 4, 5 6 7, 8 9 10, 11 12 13)');
  2. ---result--
  3.     3
  5. SELECT ST_CoordDim(ST_Point(1,2));
  6. --result--
  7.     2

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
  3. -----------
  4. 1

ST_EndPoint

描述

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

函数声明

  1. boolean ST_EndPoint(geometry  g);

使用示例

  1. postgis=# SELECT ST_AsText(ST_EndPoint('LINESTRING(1 1, 2 2, 3 3)'::geometry));
  2.  st_astext
  3. ------------
  4.  POINT(3 3)
  5. (1 row)
  7. postgis=# SELECT ST_EndPoint('POINT(1 1)'::geometry) IS NULL AS is_null;
  8.   is_null
  9. ----------
  10.  t
  11. (1 row)
  13. --3d endpoint
  14. SELECT ST_AsEWKT(ST_EndPoint('LINESTRING(1 1 2, 1 2 3, 0 0 5)'));
  15.   st_asewkt
  16. --------------
  17.  POINT(0 0 5)
  18. (1 row)

ST_Envelope

描述

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

函数声明

  1. geometry ST_Envelope(geometry  g1);

使用示例

  2. SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
  3.  st_astext
  4. ------------
  5.  POINT(1 3)
  6. (1 row)
  9. SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'::geometry));
  10.            st_astext
  11. --------------------------------
  12.  POLYGON((0 0,0 3,1 3,1 0,0 0))
  13. (1 row)
  16. SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))'::geometry));
  17.                           st_astext
  18. --------------------------------------------------------------
  19.  POLYGON((0 0,0 1,1.00000011920929 1,1.00000011920929 0,0 0))
  20. (1 row)
  21. SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))'::geometry));
  22.                           st_astext
  23. --------------------------------------------------------------
  24.  POLYGON((0 0,0 1,1.00000011920929 1,1.00000011920929 0,0 0))
  25. (1 row)
  27. SELECT Box3D(geom), Box2D(geom), ST_AsText(ST_Envelope(geom)) As envelopewkt
  28.     FROM (SELECT 'POLYGON((0 0, 0 1000012333334.34545678, 1.0000001 1, 1.0000001 0, 0 0))'::geometry As geom) As foo;

ST_BoundingDiagonal

描述

返回一个LineString，代表给定几何对象的边界框的对角线。

函数声明

  1. geometry ST_BoundingDiagonal(geometry  geom, boolean  fits=false);

使用示例

  2. -- Get the minimum X in a buffer around a point
  3. SELECT ST_X(ST_StartPoint(ST_BoundingDiagonal(
  4.   ST_Buffer(ST_MakePoint(0,0),10)
  5. )));
  6.  st_x
  7. ------
  8.   -10

ST_ExteriorRing

描述

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

函数声明

  1. geometry ST_ExteriorRing(geometry  a_polygon);

使用示例

  2. --If you have a table of polygons
  3. SELECT gid, ST_ExteriorRing(the_geom) AS ering
  4. FROM sometable;
  6. --If you have a table of MULTIPOLYGONs
  7. --and want to return a MULTILINESTRING composed of the exterior rings of each polygon
  8. SELECT gid, ST_Collect(ST_ExteriorRing(the_geom)) AS erings
  9.     FROM (SELECT gid, (ST_Dump(the_geom)).geom As the_geom
  10.             FROM sometable) As foo
  11. GROUP BY gid;
  13. --3d Example
  14. SELECT ST_AsEWKT(
  15.     ST_ExteriorRing(
  16.     ST_GeomFromEWKT('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))')
  17.     )
  18. );
  20. st_asewkt
  21. ---------
  22. LINESTRING(0 0 1,1 1 1,1 2 1,1 1 1,0 0 1)

ST_GeometryN

描述

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

函数声明

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

使用示例

  1. --Extracting a subset of points from a 3d multipoint
  2. SELECT n, ST_AsEWKT(ST_GeometryN(the_geom, n)) As geomewkt
  3. FROM (
  4. VALUES (ST_GeomFromEWKT('MULTIPOINT(1 2 7, 3 4 7, 5 6 7, 8 9 10)') ),
  5. ( ST_GeomFromEWKT('MULTICURVE(CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (10 11, 12 11))') )
  6.     )As foo(the_geom)
  7.     CROSS JOIN generate_series(1,100) n
  8. WHERE n <= ST_NumGeometries(the_geom);
  10.  n |               geomewkt
  11. ---+-----------------------------------------
  12.  1 | POINT(1 2 7)
  13.  2 | POINT(3 4 7)
  14.  3 | POINT(5 6 7)
  15.  4 | POINT(8 9 10)
  16.  1 | CIRCULARSTRING(2.5 2.5,4.5 2.5,3.5 3.5)
  17.  2 | LINESTRING(10 11,12 11)
  20. --Extracting all geometries (useful when you want to assign an id)
  21. SELECT gid, n, ST_GeometryN(the_geom, n)
  22. FROM sometable CROSS JOIN generate_series(1,100) n
  23. WHERE n <= ST_NumGeometries(the_geom);

ST_GeometryType

描述

返回给定几何对象的类型。

函数声明

  1. text ST_GeometryType(geometry  g1);

使用示例

  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 
  3. -----------------
  4.  ST_LineString

ST_InteriorRingN

描述

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

函数声明

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

使用示例

  1. SELECT ST_AsText(ST_InteriorRingN(the_geom, 1)) As the_geom
  2. FROM (SELECT ST_BuildArea(
  3.         ST_Collect(ST_Buffer(ST_Point(1,2), 20,3),
  4.             ST_Buffer(ST_Point(1, 2), 10,3))) As the_geom
  5.         )  as foo；
  6.                                           the_geom                                                                              
  8. --------------------------------------------------------------------------------------------------------------------------------
  9. --------------------------------------------------------------------------------------------------------------------------------
  10. --------------------------------------------------------------------------------------------
  11.  LINESTRING(11 2,9.66025403784436 7.00000000000005,5.99999999999995 10.6602540378444,0.999999999999954 12,-4.00000000000003 10.6
  12. 602540378444,-7.66025403784441 6.99999999999997,-9 1.99999999999997,-7.66025403784437 -3.00000000000002,-3.99999999999998 -6.660
  13. 2540378444,1.00000000000001 -8,6.00000000000001 -6.66025403784438,9.66025403784439 -3,11 2)

ST_IsPolygonCCW

描述

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

函数声明

  1. boolean ST_IsPolygonCCW(geometry geom);

使用示例

  1. None

ST_IsPolygonCW

描述

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

函数声明

  1. boolean ST_IsPolygonCW(geometry geom);

使用示例

  1. None

ST_IsClosed

描述

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

函数声明

  1. boolean ST_IsClosed(geometry  g);

使用示例

  1. postgis=# SELECT ST_IsClosed('LINESTRING(0 0, 1 1)'::geometry);
  2.  st_isclosed
  3. -------------
  4.  f
  5. (1 row)
  7. postgis=# SELECT ST_IsClosed('LINESTRING(0 0, 0 1, 1 1, 0 0)'::geometry);
  8.  st_isclosed
  9. -------------
  10.  t
  11. (1 row)
  13. postgis=# SELECT ST_IsClosed('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))'::geometry);
  14.  st_isclosed
  15. -------------
  16.  f
  17. (1 row)
  19. postgis=# SELECT ST_IsClosed('POINT(0 0)'::geometry);
  20.  st_isclosed
  21. -------------
  22.  t
  23. (1 row)
  25. postgis=# SELECT ST_IsClosed('MULTIPOINT((0 0), (1 1))'::geometry);
  26.  st_isclosed
  27. -------------
  28.  t
  29. (1 row)

ST_IsCollection

描述

如果输入参数是如下类型时，返回true：

  • GEOMETRYCOLLECTION
  • MULTI{POINT,POLYGON,LINESTRING,CURVE,SURFACE}
  • COMPOUNDCURVE

函数声明

  1. boolean ST_IsCollection(geometry  g);

使用示例

  1. postgis=# SELECT ST_IsCollection('LINESTRING(0 0, 1 1)'::geometry);
  2.  st_iscollection
  3. -------------
  4.  f
  5. (1 row)
  7. postgis=# SELECT ST_IsCollection('MULTIPOINT EMPTY'::geometry);
  8.  st_iscollection
  9. -------------
  10.  t
  11. (1 row)
  13. postgis=# SELECT ST_IsCollection('MULTIPOINT((0 0))'::geometry);
  14.  st_iscollection
  15. -------------
  16.  t
  17. (1 row)
  19. postgis=# SELECT ST_IsCollection('MULTIPOINT((0 0), (42 42))'::geometry);
  20.  st_iscollection
  21. -------------
  22.  t
  23. (1 row)
  25. postgis=# SELECT ST_IsCollection('GEOMETRYCOLLECTION(POINT(0 0))'::geometry);
  26.  st_iscollection
  27. -------------
  28.  t
  29. (1 row)

ST_IsEmpty

描述

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

函数声明

  1. boolean ST_IsEmpty(geometry  geomA);

使用示例

  2. SELECT ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
  3.  st_isempty
  4. ------------
  5.  t
  6. (1 row)
  8.  SELECT ST_IsEmpty(ST_GeomFromText('POLYGON EMPTY'));
  9.  st_isempty
  10. ------------
  11.  t
  12. (1 row)
  14. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
  16.  st_isempty
  17. ------------
  18.  f
  19. (1 row)
  21.  SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))')) = false;
  22.  ?column?
  23. ----------
  24.  t
  25. (1 row)
  27.  SELECT ST_IsEmpty(ST_GeomFromText('CIRCULARSTRING EMPTY'));
  28.   st_isempty
  29. ------------
  30.  t
  31. (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 | st_isclosed | st_issimple
  4. -----------+-------------+-------------
  5.  t         | t           | t
  6. (1 row)
  8. SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
  9. FROM (SELECT 'LINESTRING(0 0, 0 1, 1 0, 1 1, 0 0)'::geometry AS the_geom) AS foo;
  10.  st_isring | st_isclosed | st_issimple
  11. -----------+-------------+-------------
  12.  f         | t           | f
  13. (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, 1 2))'));
  2.  st_issimple
  3. -------------
  4.  t
  5. (1 row)
  7.  SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
  8.  st_issimple
  9. -------------
  10.  f
  11. (1 row)

ST_IsValid

描述

判断输入参数是否满足OGC标准中的Valid判定条件。Valid的判定标准请参考OGC标准文档。

函数声明

  1. boolean ST_IsValid(geometry  g);
  2. boolean ST_IsValid(geometry  g, integer  flags);

使用示例

  1. SELECT ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) As good_line,
  2.     ST_IsValid(ST_GeomFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) As bad_poly
  3. --results
  4. NOTICE:  Self-intersection at or near point 0 0
  5.  good_line | bad_poly
  6. -----------+----------
  7.  t         | f

ST_IsValidReason

描述

返回一段文本描述输入参数是否为Valid对象，如果不是，则输出原因。

函数声明

  1. text ST_IsValidReason(geometry  geomA);
  2. text ST_IsValidReason(geometry  geomA, integer  flags);

使用示例

  2. --First 3 Rejects from a successful quintuplet experiment
  3. SELECT gid, ST_IsValidReason(the_geom) as validity_info
  4. FROM
  5. (SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), ST_Accum(f.line)) As the_geom, gid
  6. FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
  7.     FROM generate_series(-4,6) x1
  8.     CROSS JOIN generate_series(2,5) y1
  9.     CROSS JOIN generate_series(1,8) z1
  10.     WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
  11.     INNER JOIN (SELECT ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1*1, z1*2) As line
  12.     FROM generate_series(-3,6) x1
  13.     CROSS JOIN generate_series(2,5) y1
  14.     CROSS JOIN generate_series(1,10) z1
  15.     WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
  16. ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
  17. GROUP BY gid, e.buff) As quintuplet_experiment
  18. WHERE ST_IsValid(the_geom) = false
  19. ORDER BY gid
  20. LIMIT 3;
  22.  gid  |      validity_info
  23. ------+--------------------------
  24.  5330 | Self-intersection [32 5]
  25.  5340 | Self-intersection [42 5]
  26.  5350 | Self-intersection [52 5]
  28.  --simple example
  29. SELECT ST_IsValidReason('LINESTRING(220227 150406,2220227 150407,222020 150410)');
  31.  st_isvalidreason
  32. ------------------
  33.  Valid Geometry

ST_IsValidDetail

描述

返回关于传入几何对象是否为Valid的详细的信息，如果不是，则输出不是的原因，及其所在位置。

函数声明

  1. valid_detail ST_IsValidDetail(geometry  geom);
  2. valid_detail ST_IsValidDetail(geometry  geom, integer  flags);

使用示例

  2. --First 3 Rejects from a successful quintuplet experiment
  3. SELECT gid, reason(ST_IsValidDetail(the_geom)), ST_AsText(location(ST_IsValidDetail(the_geom))) as location
  4. FROM
  5. (SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), ST_Accum(f.line)) As the_geom, gid
  6. FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
  7.     FROM generate_series(-4,6) x1
  8.     CROSS JOIN generate_series(2,5) y1
  9.     CROSS JOIN generate_series(1,8) z1
  10.     WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
  11.     INNER JOIN (SELECT ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1*1, z1*2) As line
  12.     FROM generate_series(-3,6) x1
  13.     CROSS JOIN generate_series(2,5) y1
  14.     CROSS JOIN generate_series(1,10) z1
  15.     WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
  16. ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
  17. GROUP BY gid, e.buff) As quintuplet_experiment
  18. WHERE ST_IsValid(the_geom) = false
  19. ORDER BY gid
  20. LIMIT 3;
  22.  gid  |      reason       |  location
  23. ------+-------------------+-------------
  24.  5330 | Self-intersection | POINT(32 5)
  25.  5340 | Self-intersection | POINT(42 5)
  26.  5350 | Self-intersection | POINT(52 5)
  28.  --simple example
  29. SELECT * FROM ST_IsValidDetail('LINESTRING(220227 150406,2220227 150407,222020 150410)');
  31.  valid | reason | location
  32. -------+--------+----------
  33.  t     |        |

ST_M

描述

返回传入点的M坐标值。传入参数必须是一个坐标点。

函数声明

  1. float ST_M(geometry  a_point);

使用示例

  1. SELECT ST_M(ST_GeomFromEWKT('POINT(1 2 3 4)'));
  2.  st_m
  3. ------
  4.     4
  5. (1 row)

ST_NDims

描述

返回传入几何对象的坐标维度值。

函数声明

  1. integer ST_NDims(geometry  g1);

使用示例

  1. SELECT ST_NDims(ST_GeomFromText('POINT(1 1)')) As d2point,
  2.     ST_NDims(ST_GeomFromEWKT('POINT(1 1 2)')) As d3point,
  3.     ST_NDims(ST_GeomFromEWKT('POINTM(1 1 0.5)')) As d2pointm;
  5.      d2point | d3point | d2pointm
  6. ---------+---------+----------
  7.        2 |       3 |        3

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. --result
  3. 4
  5. --Polygon in 3D space
  6. SELECT ST_NPoints(ST_GeomFromEWKT('LINESTRING(77.29 29.07 1,77.42 29.26 0,77.27 29.31 -1,77.29 29.07 3)'))
  7. --result
  8. 4

ST_NRings

描述

如果输入对象是一个Polygon或MultiPolygon，返回其中环的个数。

函数声明

  1. integer ST_NRings(geometry  geomA);

使用示例

  1. SELECT ST_NRings(the_geom) As Nrings, ST_NumInteriorRings(the_geom) As ninterrings
  2.                     FROM (SELECT ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))') As the_geom) As foo;
  3.      nrings | ninterrings
  4. --------+-------------
  5.       1 |           0
  6. (1 row)

ST_NumGeometries

描述

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

函数声明

  1. integer ST_NumGeometries(geometry  geom);

使用示例

  2. --Prior versions would have returned NULL for this -- in 2.0.0 this returns 1
  3. SELECT ST_NumGeometries(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
  4. --result
  5. 1
  7. --Geometry Collection Example - multis count as one geom in a collection
  8. SELECT ST_NumGeometries(ST_GeomFromEWKT('GEOMETRYCOLLECTION(MULTIPOINT(-2 3 , -2 2),
  9. LINESTRING(5 5 ,10 10),
  10. POLYGON((-7 4.2,-7.1 5,-7.1 4.3,-7 4.2)))'));
  11. --result
  12. 3

ST_NumInteriorRings

描述

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

函数声明

  1. integer ST_NumInteriorRings(geometry  a_polygon);

使用示例

  2. --If you have a regular polygon
  3. SELECT gid, field1, field2, ST_NumInteriorRings(the_geom) AS numholes
  4. FROM sometable;
  6. --If you have multipolygons
  7. --And you want to know the total number of interior rings in the MULTIPOLYGON
  8. SELECT gid, field1, field2, SUM(ST_NumInteriorRings(the_geom)) AS numholes
  9. FROM (SELECT gid, field1, field2, (ST_Dump(the_geom)).geom As the_geom
  10.     FROM sometable) As foo
  11. GROUP BY gid, field1,field2;

ST_NumInteriorRing

描述

同ST_NumInteriorRings。

函数声明

  1. integer ST_NumInteriorRing(geometry  a_polygon);

使用示例

  1. None

ST_NumPatches

描述

返回一个Polyhedral曲面的平面数。

函数声明

  1. integer ST_NumPatches(geometry  g1);

使用示例

  1. SELECT ST_NumPatches(ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
  2.         ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
  3.         ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
  4.         ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )'));
  5.  st_numpatches 
  6. ---------------
  7.              6

ST_NumPoints

描述

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

函数声明

  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. --result
  3. 4

ST_PatchN

描述

返回输入几何对象的第n个平面（从1开始编号），如果输入对象是： POLYHEDRALSURFACE, POLYHEDRALSURFACEM。其它情况下，返回NULL。

函数声明

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

使用示例

  2. --Extract the 2nd face of the polyhedral surface
  3. SELECT ST_AsEWKT(ST_PatchN(geom, 2)) As geomewkt
  4. FROM (
  5. VALUES (ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
  6.     ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
  7.     ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
  8.     ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)), ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )')) ) As foo(geom);
  10.               geomewkt
  11. ---+-----------------------------------------
  12.  POLYGON((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0))

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(
  3.    ST_PointN(
  4.       column1,
  5.       generate_series(1, ST_NPoints(column1))
  6.    ))
  7. FROM ( VALUES ('LINESTRING(0 0, 1 1, 2 2)'::geometry) ) AS foo;
  9.  st_astext
  10. ------------
  11.  POINT(0 0)
  12.  POINT(1 1)
  13.  POINT(2 2)
  14. (3 rows)
  16. --Example circular string
  17. SELECT ST_AsText(ST_PointN(ST_GeomFromText('CIRCULARSTRING(1 2, 3 2, 1 2)'),2));
  19. st_astext
  20. ----------
  21. POINT(3 2)
  23. SELECT st_astext(f)
  24. FROM ST_GeometryFromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)') as g
  25.     ,ST_PointN(g, -2) AS f -- 1 based index
  27. st_astext
  28. ----------
  29. "POINT Z (1 1 1)"

ST_Points

描述

返回一个MultiPoint其中包含了输入几何对象的所有的顶点。

函数声明

  1. geometry ST_Points(geometry geom);

使用示例

  1. SELECT ST_AsText(ST_Points('POLYGON Z ((30 10 4,10 30 5,40 40 6, 30 10))'));
  3. --result
  4. MULTIPOINT Z (30 10 4,10 30 5,40 40 6, 30 10 4)

ST_SRID

描述

返回给定几何对象的SRID（坐标参考系标识）。SRID的定义在系统表spatial_ref_sys中。

函数声明

  1. integer ST_SRID(geometry  g1);

使用示例

  1. SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
  2. --result
  3. 4326

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
  3. ------------
  4.  POINT(0 1)
  5. (1 row)
  7. SELECT ST_StartPoint('POINT(0 1)'::geometry) IS NULL AS is_null;
  8.   is_null
  9. ----------
  10.  t
  11. (1 row)
  13. --3d line
  14. SELECT ST_AsEWKT(ST_StartPoint('LINESTRING(0 1 1, 0 2 2)'::geometry));
  15.  st_asewkt
  16. ------------
  17.  POINT(0 1 1)
  18. (1 row)
  20. -- circular linestring --
  21. SELECT ST_AsText(ST_StartPoint('CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 5 2)'::geometry));
  22.  st_astext
  23. ------------
  24.  POINT(5 2)

ST_Summary

描述

返回输入几何对象的一段文本摘要。

函数声明

  1. text ST_Summary(geometry  g);
  2. text ST_Summary(geography  g);

使用示例

  2. =# SELECT ST_Summary(ST_GeomFromText('LINESTRING(0 0, 1 1)')) as geom,
  3.         ST_Summary(ST_GeogFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) geog;
  4.             geom             |          geog
  5. -----------------------------+--------------------------
  6.  LineString[B] with 2 points | Polygon[BGS] with 1 rings
  7.                              | ring 0 has 5 points
  8.                              :
  9. (1 row)
  12. =# SELECT ST_Summary(ST_GeogFromText('LINESTRING(0 0 1, 1 1 1)')) As geog_line,
  13.         ST_Summary(ST_GeomFromText('SRID=4326;POLYGON((0 0 1, 1 1 2, 1 2 3, 1 1 1, 0 0 1))')) As geom_poly;
  14. ;
  15.            geog_line             |        geom_poly
  16. -------------------------------- +--------------------------
  17.  LineString[ZBGS] with 2 points | Polygon[ZBS] with 1 rings
  18.                                 :    ring 0 has 5 points
  19.                                 :
  20. (1 row)

ST_X

描述

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

函数声明

  1. float ST_X(geometry  a_point);

使用示例

  1. SELECT ST_X(ST_GeomFromEWKT('POINT(1 2 3 4)'));
  2.  st_x
  3. ------
  4.     1
  5. (1 row)
  7. SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  8.  st_y
  9. ------
  10.   1.5
  11. (1 row)

ST_XMax

描述

返回输入几何对象的边界框的X坐标的最大值。

函数声明

  1. float ST_XMax(box3d  aGeomorBox2DorBox3D);

使用示例

  1. SELECT ST_XMax('BOX3D(1 2 3, 4 5 6)');
  2. st_xmax
  3. -------
  4. 4
  6. SELECT ST_XMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  7. st_xmax
  8. -------
  9. 5
  11. SELECT ST_XMax(CAST('BOX(-3 2, 3 4)' As box2d));
  12. st_xmax
  13. -------
  14. 3
  15. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  16. SELECT ST_XMax('LINESTRING(1 3, 5 6)');
  18. --ERROR:  BOX3D parser - doesn't start with BOX3D(
  20. SELECT ST_XMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  21. st_xmax
  22. --------
  23. 220288.248780547

ST_XMin

描述

返回输入几何对象的边界框的X坐标的最小值。

函数声明

  1. float ST_XMin(box3d  aGeomorBox2DorBox3D);

使用示例

  1. SELECT ST_XMin('BOX3D(1 2 3, 4 5 6)');
  2. st_xmin
  3. -------
  4. 1
  6. SELECT ST_XMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  7. st_xmin
  8. -------
  9. 1
  11. SELECT ST_XMin(CAST('BOX(-3 2, 3 4)' As box2d));
  12. st_xmin
  13. -------
  14. -3
  15. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  16. SELECT ST_XMin('LINESTRING(1 3, 5 6)');
  18. --ERROR:  BOX3D parser - doesn't start with BOX3D(
  20. SELECT ST_XMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  21. st_xmin
  22. --------
  23. 220186.995121892

ST_Y

描述

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

函数声明

  1. float ST_Y(geometry  a_point);

使用示例

  1. SELECT ST_Y(ST_GeomFromEWKT('POINT(1 2 3 4)'));
  2.  st_y
  3. ------
  4.     2
  5. (1 row)
  7. SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  8.  st_y
  9. ------
  10.   1.5
  11. (1 row)

ST_YMax

描述

返回输入几何对象的边界框的Y坐标的最大值。

函数声明

  1. float ST_YMax(box3d  aGeomorBox2DorBox3D);

使用示例

  1. SELECT ST_YMax('BOX3D(1 2 3, 4 5 6)');
  2. st_ymax
  3. -------
  4. 5
  6. SELECT ST_YMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  7. st_ymax
  8. -------
  9. 6
  11. SELECT ST_YMax(CAST('BOX(-3 2, 3 4)' As box2d));
  12. st_ymax
  13. -------
  14. 4
  15. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  16. SELECT ST_YMax('LINESTRING(1 3, 5 6)');
  18. --ERROR:  BOX3D parser - doesn't start with BOX3D(
  20. SELECT ST_YMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  21. st_ymax
  22. --------
  23. 150506.126829327

ST_YMin

描述

返回输入几何对象的边界框的Y坐标的最小值。

函数声明

  1. float ST_YMin(box3d  aGeomorBox2DorBox3D);

使用示例

  1. SELECT ST_YMin('BOX3D(1 2 3, 4 5 6)');
  2. st_ymin
  3. -------
  4. 2
  6. SELECT ST_YMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  7. st_ymin
  8. -------
  9. 3
  11. SELECT ST_YMin(CAST('BOX(-3 2, 3 4)' As box2d));
  12. st_ymin
  13. -------
  14. 2
  15. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  16. SELECT ST_YMin('LINESTRING(1 3, 5 6)');
  18. --ERROR:  BOX3D parser - doesn't start with BOX3D(
  20. SELECT ST_YMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  21. st_ymin
  22. --------
  23. 150406

ST_Z

描述

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

函数声明

  1. float ST_Z(geometry  a_point);

使用示例

  1. SELECT ST_Z(ST_GeomFromEWKT('POINT(1 2 3 4)'));
  2.  st_z
  3. ------
  4.     3
  5. (1 row)

ST_ZMax

描述

返回输入几何对象的边界框的Z坐标的最大值。

函数声明

  1. float ST_ZMax(box3d  aGeomorBox2DorBox3D);

使用示例

  1. SELECT ST_ZMax('BOX3D(1 2 3, 4 5 6)');
  2. st_zmax
  3. -------
  4. 6
  6. SELECT ST_ZMax(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
  7. st_zmax
  8. -------
  9. 7
  11. SELECT ST_ZMax('BOX3D(-3 2 1, 3 4 1)' );
  12. st_zmax
  13. -------
  14. 1
  15. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  16. SELECT ST_ZMax('LINESTRING(1 3 4, 5 6 7)');
  18. --ERROR:  BOX3D parser - doesn't start with BOX3D(
  20. SELECT ST_ZMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  21. st_zmax
  22. --------
  23. 3

ST_Zmflag

描述

返回一个用整数表示的输入几何对象的Z/M标志。数字值的含义如下: 0=2d, 1=3dm, 2=3dz, 3=4d。

函数声明

  1. smallint ST_Zmflag(geometry  geomA);

使用示例

  1. SELECT ST_Zmflag(ST_GeomFromEWKT('LINESTRING(1 2, 3 4)'));
  2.  st_zmflag
  3. -----------
  4.          0
  6. SELECT ST_Zmflag(ST_GeomFromEWKT('LINESTRINGM(1 2 3, 3 4 3)'));
  7.  st_zmflag
  8. -----------
  9.          1
  11. SELECT ST_Zmflag(ST_GeomFromEWKT('CIRCULARSTRING(1 2 3, 3 4 3, 5 6 3)'));
  12.  st_zmflag
  13. -----------
  14.          2
  15. SELECT ST_Zmflag(ST_GeomFromEWKT('POINT(1 2 3 4)'));
  16.  st_zmflag
  17. -----------
  18.          3

ST_ZMin

描述

返回输入几何对象的边界框的Z坐标的最小值。

函数声明

  1. float ST_ZMin(box3d  aGeomorBox2DorBox3D);

使用示例

  1. SELECT ST_ZMin('BOX3D(1 2 3, 4 5 6)');
  2. st_zmin
  3. -------
  4. 3
  6. SELECT ST_ZMin(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
  7. st_zmin
  8. -------
  9. 4
  11. SELECT ST_ZMin('BOX3D(-3 2 1, 3 4 1)' );
  12. st_zmin
  13. -------
  14. 1
  15. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  16. SELECT ST_ZMin('LINESTRING(1 3 4, 5 6 7)');
  18. --ERROR:  BOX3D parser - doesn't start with BOX3D(
  20. SELECT ST_ZMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  21. st_zmin
  22. --------
  23. 1
版权声明

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

评论

-----