全部产品
云市场

空间对象访问函数

更新时间:2019-07-08 18:09:58

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)
  5. SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')));
  6. st_astext
  7. ----------
  8. LINESTRING(1 1,0 0,-1 1,1 1)
  9. --Using a 3d polygon
  10. SELECT ST_AsEWKT(ST_Boundary(ST_GeomFromEWKT('POLYGON((1 1 1,0 0 1, -1 1 1, 1 1 1))')));
  11. st_asewkt
  12. -----------------------------------
  13. LINESTRING(1 1 1,0 0 1,-1 1 1,1 1 1)
  14. --Using a 3d multilinestring
  15. 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) )')));
  16. st_asewkt
  17. ----------
  18. 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
  4. SELECT ST_CoordDim(ST_Point(1,2));
  5. --result--
  6. 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)
  6. postgis=# SELECT ST_EndPoint('POINT(1 1)'::geometry) IS NULL AS is_null;
  7. is_null
  8. ----------
  9. t
  10. (1 row)
  11. --3d endpoint
  12. SELECT ST_AsEWKT(ST_EndPoint('LINESTRING(1 1 2, 1 2 3, 0 0 5)'));
  13. st_asewkt
  14. --------------
  15. POINT(0 0 5)
  16. (1 row)

ST_Envelope

描述

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

函数声明

  1. geometry ST_Envelope(geometry g1);

使用示例

  1. SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
  2. st_astext
  3. ------------
  4. POINT(1 3)
  5. (1 row)
  6. SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'::geometry));
  7. st_astext
  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
  13. --------------------------------------------------------------
  14. POLYGON((0 0,0 1,1.00000011920929 1,1.00000011920929 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
  18. --------------------------------------------------------------
  19. POLYGON((0 0,0 1,1.00000011920929 1,1.00000011920929 0,0 0))
  20. (1 row)
  21. SELECT Box3D(geom), Box2D(geom), ST_AsText(ST_Envelope(geom)) As envelopewkt
  22. 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);

使用示例

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

ST_ExteriorRing

描述

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

函数声明

  1. geometry ST_ExteriorRing(geometry a_polygon);

使用示例

  1. --If you have a table of polygons
  2. SELECT gid, ST_ExteriorRing(the_geom) AS ering
  3. FROM sometable;
  4. --If you have a table of MULTIPOLYGONs
  5. --and want to return a MULTILINESTRING composed of the exterior rings of each polygon
  6. SELECT gid, ST_Collect(ST_ExteriorRing(the_geom)) AS erings
  7. FROM (SELECT gid, (ST_Dump(the_geom)).geom As the_geom
  8. FROM sometable) As foo
  9. GROUP BY gid;
  10. --3d Example
  11. SELECT ST_AsEWKT(
  12. ST_ExteriorRing(
  13. ST_GeomFromEWKT('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))')
  14. )
  15. );
  16. st_asewkt
  17. ---------
  18. 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);
  9. n | geomewkt
  10. ---+-----------------------------------------
  11. 1 | POINT(1 2 7)
  12. 2 | POINT(3 4 7)
  13. 3 | POINT(5 6 7)
  14. 4 | POINT(8 9 10)
  15. 1 | CIRCULARSTRING(2.5 2.5,4.5 2.5,3.5 3.5)
  16. 2 | LINESTRING(10 11,12 11)
  17. --Extracting all geometries (useful when you want to assign an id)
  18. SELECT gid, n, ST_GeometryN(the_geom, n)
  19. FROM sometable CROSS JOIN generate_series(1,100) n
  20. 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
  7. --------------------------------------------------------------------------------------------------------------------------------
  8. --------------------------------------------------------------------------------------------------------------------------------
  9. --------------------------------------------------------------------------------------------
  10. LINESTRING(11 2,9.66025403784436 7.00000000000005,5.99999999999995 10.6602540378444,0.999999999999954 12,-4.00000000000003 10.6
  11. 602540378444,-7.66025403784441 6.99999999999997,-9 1.99999999999997,-7.66025403784437 -3.00000000000002,-3.99999999999998 -6.660
  12. 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)
  6. postgis=# SELECT ST_IsClosed('LINESTRING(0 0, 0 1, 1 1, 0 0)'::geometry);
  7. st_isclosed
  8. -------------
  9. t
  10. (1 row)
  11. postgis=# SELECT ST_IsClosed('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))'::geometry);
  12. st_isclosed
  13. -------------
  14. f
  15. (1 row)
  16. postgis=# SELECT ST_IsClosed('POINT(0 0)'::geometry);
  17. st_isclosed
  18. -------------
  19. t
  20. (1 row)
  21. postgis=# SELECT ST_IsClosed('MULTIPOINT((0 0), (1 1))'::geometry);
  22. st_isclosed
  23. -------------
  24. t
  25. (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)
  6. postgis=# SELECT ST_IsCollection('MULTIPOINT EMPTY'::geometry);
  7. st_iscollection
  8. -------------
  9. t
  10. (1 row)
  11. postgis=# SELECT ST_IsCollection('MULTIPOINT((0 0))'::geometry);
  12. st_iscollection
  13. -------------
  14. t
  15. (1 row)
  16. postgis=# SELECT ST_IsCollection('MULTIPOINT((0 0), (42 42))'::geometry);
  17. st_iscollection
  18. -------------
  19. t
  20. (1 row)
  21. postgis=# SELECT ST_IsCollection('GEOMETRYCOLLECTION(POINT(0 0))'::geometry);
  22. st_iscollection
  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
  3. ------------
  4. t
  5. (1 row)
  6. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON EMPTY'));
  7. st_isempty
  8. ------------
  9. t
  10. (1 row)
  11. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
  12. st_isempty
  13. ------------
  14. f
  15. (1 row)
  16. SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))')) = false;
  17. ?column?
  18. ----------
  19. t
  20. (1 row)
  21. SELECT ST_IsEmpty(ST_GeomFromText('CIRCULARSTRING EMPTY'));
  22. st_isempty
  23. ------------
  24. t
  25. (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)
  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 | st_isclosed | st_issimple
  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, 1 2))'));
  2. st_issimple
  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
  8. -------------
  9. f
  10. (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);

使用示例

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

ST_IsValidDetail

描述

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

函数声明

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

使用示例

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

使用示例

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

ST_NumInteriorRings

描述

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

函数声明

  1. integer ST_NumInteriorRings(geometry a_polygon);

使用示例

  1. --If you have a regular polygon
  2. SELECT gid, field1, field2, ST_NumInteriorRings(the_geom) AS numholes
  3. FROM sometable;
  4. --If you have multipolygons
  5. --And you want to know the total number of interior rings in the MULTIPOLYGON
  6. SELECT gid, field1, field2, SUM(ST_NumInteriorRings(the_geom)) AS numholes
  7. FROM (SELECT gid, field1, field2, (ST_Dump(the_geom)).geom As the_geom
  8. FROM sometable) As foo
  9. 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);

使用示例

  1. --Extract the 2nd face of the polyhedral surface
  2. SELECT ST_AsEWKT(ST_PatchN(geom, 2)) As geomewkt
  3. FROM (
  4. VALUES (ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
  5. ((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)),
  6. ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
  7. ((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);
  8. geomewkt
  9. ---+-----------------------------------------
  10. 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;
  8. st_astext
  9. ------------
  10. POINT(0 0)
  11. POINT(1 1)
  12. POINT(2 2)
  13. (3 rows)
  14. --Example circular string
  15. SELECT ST_AsText(ST_PointN(ST_GeomFromText('CIRCULARSTRING(1 2, 3 2, 1 2)'),2));
  16. st_astext
  17. ----------
  18. POINT(3 2)
  19. SELECT st_astext(f)
  20. FROM ST_GeometryFromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)') as g
  21. ,ST_PointN(g, -2) AS f -- 1 based index
  22. st_astext
  23. ----------
  24. "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))'));
  2. --result
  3. 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)
  6. SELECT ST_StartPoint('POINT(0 1)'::geometry) IS NULL AS is_null;
  7. is_null
  8. ----------
  9. t
  10. (1 row)
  11. --3d line
  12. SELECT ST_AsEWKT(ST_StartPoint('LINESTRING(0 1 1, 0 2 2)'::geometry));
  13. st_asewkt
  14. ------------
  15. POINT(0 1 1)
  16. (1 row)
  17. -- circular linestring --
  18. SELECT ST_AsText(ST_StartPoint('CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 5 2)'::geometry));
  19. st_astext
  20. ------------
  21. POINT(5 2)

ST_Summary

描述

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

函数声明

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

使用示例

  1. =# SELECT ST_Summary(ST_GeomFromText('LINESTRING(0 0, 1 1)')) as geom,
  2. ST_Summary(ST_GeogFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) geog;
  3. geom | geog
  4. -----------------------------+--------------------------
  5. LineString[B] with 2 points | Polygon[BGS] with 1 rings
  6. | ring 0 has 5 points
  7. :
  8. (1 row)
  9. =# SELECT ST_Summary(ST_GeogFromText('LINESTRING(0 0 1, 1 1 1)')) As geog_line,
  10. 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;
  11. ;
  12. geog_line | geom_poly
  13. -------------------------------- +--------------------------
  14. LineString[ZBGS] with 2 points | Polygon[ZBS] with 1 rings
  15. : ring 0 has 5 points
  16. :
  17. (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)
  6. SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  7. st_y
  8. ------
  9. 1.5
  10. (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
  5. SELECT ST_XMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  6. st_xmax
  7. -------
  8. 5
  9. SELECT ST_XMax(CAST('BOX(-3 2, 3 4)' As box2d));
  10. st_xmax
  11. -------
  12. 3
  13. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  14. SELECT ST_XMax('LINESTRING(1 3, 5 6)');
  15. --ERROR: BOX3D parser - doesn't start with BOX3D(
  16. SELECT ST_XMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  17. st_xmax
  18. --------
  19. 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
  5. SELECT ST_XMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  6. st_xmin
  7. -------
  8. 1
  9. SELECT ST_XMin(CAST('BOX(-3 2, 3 4)' As box2d));
  10. st_xmin
  11. -------
  12. -3
  13. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  14. SELECT ST_XMin('LINESTRING(1 3, 5 6)');
  15. --ERROR: BOX3D parser - doesn't start with BOX3D(
  16. SELECT ST_XMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  17. st_xmin
  18. --------
  19. 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)
  6. SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
  7. st_y
  8. ------
  9. 1.5
  10. (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
  5. SELECT ST_YMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  6. st_ymax
  7. -------
  8. 6
  9. SELECT ST_YMax(CAST('BOX(-3 2, 3 4)' As box2d));
  10. st_ymax
  11. -------
  12. 4
  13. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  14. SELECT ST_YMax('LINESTRING(1 3, 5 6)');
  15. --ERROR: BOX3D parser - doesn't start with BOX3D(
  16. SELECT ST_YMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  17. st_ymax
  18. --------
  19. 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
  5. SELECT ST_YMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
  6. st_ymin
  7. -------
  8. 3
  9. SELECT ST_YMin(CAST('BOX(-3 2, 3 4)' As box2d));
  10. st_ymin
  11. -------
  12. 2
  13. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  14. SELECT ST_YMin('LINESTRING(1 3, 5 6)');
  15. --ERROR: BOX3D parser - doesn't start with BOX3D(
  16. SELECT ST_YMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  17. st_ymin
  18. --------
  19. 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
  5. SELECT ST_ZMax(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
  6. st_zmax
  7. -------
  8. 7
  9. SELECT ST_ZMax('BOX3D(-3 2 1, 3 4 1)' );
  10. st_zmax
  11. -------
  12. 1
  13. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  14. SELECT ST_ZMax('LINESTRING(1 3 4, 5 6 7)');
  15. --ERROR: BOX3D parser - doesn't start with BOX3D(
  16. SELECT ST_ZMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  17. st_zmax
  18. --------
  19. 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
  5. SELECT ST_Zmflag(ST_GeomFromEWKT('LINESTRINGM(1 2 3, 3 4 3)'));
  6. st_zmflag
  7. -----------
  8. 1
  9. SELECT ST_Zmflag(ST_GeomFromEWKT('CIRCULARSTRING(1 2 3, 3 4 3, 5 6 3)'));
  10. st_zmflag
  11. -----------
  12. 2
  13. SELECT ST_Zmflag(ST_GeomFromEWKT('POINT(1 2 3 4)'));
  14. st_zmflag
  15. -----------
  16. 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
  5. SELECT ST_ZMin(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
  6. st_zmin
  7. -------
  8. 4
  9. SELECT ST_ZMin('BOX3D(-3 2 1, 3 4 1)' );
  10. st_zmin
  11. -------
  12. 1
  13. --Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
  14. SELECT ST_ZMin('LINESTRING(1 3 4, 5 6 7)');
  15. --ERROR: BOX3D parser - doesn't start with BOX3D(
  16. SELECT ST_ZMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
  17. st_zmin
  18. --------
  19. 1