文档

空间对象访问函数

更新时间:
一键部署

ST_GeometryType

描述

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

函数声明

text ST_GeometryType(geometry  geomA);

使用示例

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

ST_Boundary

描述

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

函数声明

geometry ST_Boundary(geometry  geomA);

使用示例

SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')));
 st_astext(st_boundary(st_geomfromtext('LINESTRING(1 1,0 0, -1 1)'))) 
----------------------------------------------------------------------
 MULTIPOINT ((1 1), (-1 1))
(1 row)


SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')));
 st_astext(st_boundary(st_geomfromtext('POLYGON((1 1,0 0, -1 1, 1 1))'))) 
--------------------------------------------------------------------------
 LINESTRING (1 1, 0 0, -1 1, 1 1)
(1 row)

ST_CoordDim

描述

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

函数声明

integer ST_CoordDim(geometry  geomA);

使用示例

SELECT ST_CoordDim(ST_Point(1,2));
 st_coorddim(st_point(1, 2)) 
-----------------------------
                           2
(1 row)

ST_Dimension

描述

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

函数声明

integer ST_Dimension(geometry  g);

使用示例

SELECT ST_Dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0))');
 st_dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0))') 
--------------------------------------------------------------------
                                                                  1
(1 row)

ST_EndPoint

描述

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

函数声明

boolean ST_EndPoint(geometry  g);

使用示例

SELECT ST_AsText(ST_EndPoint('LINESTRING(1 1, 2 2, 3 3)'::geometry));
 st_astext(st_endpoint(CAST('LINESTRING(1 1, 2 2, 3 3)' AS geometry))) 
-----------------------------------------------------------------------
 POINT (3 3)
(1 row)

SELECT ST_EndPoint('POINT(1 1)'::geometry) IS NULL AS is_null;
 is_null 
---------
 t
(1 row)

ST_Envelope

描述

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

函数声明

geometry ST_Envelope(geometry  g1);

使用示例

SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
 st_astext(st_envelope(CAST('POINT(1 3)' AS geometry))) 
--------------------------------------------------------
 POINT (1 3)
(1 row)


SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'::geometry));
 st_astext(st_envelope(CAST('LINESTRING(0 0, 1 3)' AS geometry))) 
------------------------------------------------------------------
 POLYGON ((0 0, 0 3, 1 3, 1 0, 0 0))
(1 row)


SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))'::geometry));
 st_astext(st_envelope(CAST('POLYGON((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))' AS geometry))) 
------------------------------------------------------------------------------------------------
 POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))
(1 row)


SELECT ST_AsText(ST_Envelope('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))'::geometry));
 st_astext(st_envelope(CAST('POLYGON((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))' AS geometry))) 
------------------------------------------------------------------------------------------------------
 POLYGON ((0 0, 0 1, 1.0000000001 1, 1.0000000001 0, 0 0))
(1 row)

ST_ExteriorRing

描述

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

函数声明

geometry ST_ExteriorRing(geometry  a_polygon);

使用示例

select ST_ExteriorRing('POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))' :: geometry);
 st_exteriorring(CAST('POLYGON ((0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0))' AS geometry)) 
------------------------------------------------------------------------------------------
 LINESTRING (0 0, 0 1, 1.0000001 1, 1.0000001 0, 0 0)
(1 row)

ST_GeometryN

描述

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

函数声明

geometry ST_GeometryN(geometry  geomA, integer  n);

使用示例

SELECT n.col1, ST_AsText(ST_GeometryN(the_geom, n.col1 :: int)) As geomewkt FROM (
    VALUES (ST_GeomFromWKT('MULTIPOINT(1 2, 3 4, 5 6, 8 9)') ),
    ( 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)
    CROSS JOIN generate_series(1,100) n
    WHERE n.col1 <= ST_NumGeometries(the_geom);
 col1 |             geomewkt             
------+----------------------------------
    1 | POINT (1 2)
    1 | LINESTRING (30 20, 45 40, 10 40)
    2 | POINT (3 4)
    2 | LINESTRING (15 5, 40 10, 10 20)
    3 | POINT (5 6)
    4 | POINT (8 9)
(6 rows)

ST_InteriorRingN

描述

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

函数声明

geometry ST_InteriorRingN(geometry  a_polygon, integer  n);

使用示例

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;
 the_geom 
----------

(1 row)


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;
                    the_geom                     
-------------------------------------------------
 LINESTRING (-104 38, -106 38, -105 39, -104 38)
(1 row)

ST_IsPolygonCCW

描述

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

函数声明

boolean ST_IsPolygonCCW(geometry geom);

使用示例

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

ST_IsClosed

描述

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

函数声明

boolean ST_IsClosed(geometry  g);

使用示例

SELECT ST_IsClosed('LINESTRING(0 0, 1 1)'::geometry);
 st_isclosed(CAST('LINESTRING(0 0, 1 1)' AS geometry)) 
-------------------------------------------------------
 f
(1 row)


SELECT ST_IsClosed('LINESTRING(0 0, 0 1, 1 1, 0 0)'::geometry);
 st_isclosed(CAST('LINESTRING(0 0, 0 1, 1 1, 0 0)' AS geometry)) 
-----------------------------------------------------------------
 t
(1 row)


SELECT ST_IsClosed('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))'::geometry);
 st_isclosed(CAST('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))' AS geometry)) 
-----------------------------------------------------------------------------------
 f
(1 row)


SELECT ST_IsClosed('POINT(0 0)'::geometry);
 st_isclosed(CAST('POINT(0 0)' AS geometry)) 
---------------------------------------------
 t
(1 row)


SELECT ST_IsClosed('MULTIPOINT((0 0), (1 1))'::geometry);
 st_isclosed(CAST('MULTIPOINT((0 0), (1 1))' AS geometry)) 
-----------------------------------------------------------
 t
(1 row)

ST_IsEmpty

描述

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

函数声明

boolean ST_IsEmpty(geometry  geomA);

使用示例

SELECT ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
 st_isempty(st_geomfromtext('GEOMETRYCOLLECTION EMPTY')) 
---------------------------------------------------------
 t
(1 row)


 SELECT ST_IsEmpty(ST_GeomFromText('POLYGON EMPTY'));
 st_isempty(st_geomfromtext('POLYGON EMPTY')) 
----------------------------------------------
 t
(1 row)


SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))'));
 st_isempty(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) 
-------------------------------------------------------------------
 f
(1 row)


SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) = false;
 (st_isempty(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) = false) 
-----------------------------------------------------------------------------
 t
(1 row)

ST_IsRing

描述

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

函数声明

boolean ST_IsRing(geometry  g);

使用示例

SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
       FROM (SELECT 'LINESTRING(0 0, 0 1, 1 1, 1 0, 0 0)'::geometry AS the_geom) AS foo;
 st_isring(the_geom) | st_isclosed(the_geom) | st_issimple(the_geom) 
---------------------+-----------------------+-----------------------
 t                   | t                     | t
(1 row)


SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
    FROM (SELECT 'LINESTRING(0 0, 0 1, 1 0, 1 1, 0 0)'::geometry AS the_geom) AS foo;
 st_isring(the_geom) | st_isclosed(the_geom) | st_issimple(the_geom) 
---------------------+-----------------------+-----------------------
 f                   | t                     | f
(1 row)

ST_IsSimple

描述

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

函数声明

boolean ST_IsSimple(geometry  geomA);

使用示例

SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))'));
 st_issimple(st_geomfromtext('POLYGON((1 2, 3 4, 5 6, 3 3, 1 2))')) 
--------------------------------------------------------------------
 t
(1 row)


SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
 st_issimple(st_geomfromtext('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)')) 
-----------------------------------------------------------------------
 f
(1 row)

ST_IsValid

描述

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

函数声明

boolean ST_IsValid(geometry  g);

使用示例

SELECT ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) As good_line;
 good_line 
-----------
 t
(1 row)

ST_NPoints

描述

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

函数声明

integer ST_NPoints(geometry  g1);

使用示例

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

ST_NumGeometries

描述

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

函数声明

integer ST_NumGeometries(geometry  geom);

使用示例

SELECT ST_NumGeometries(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
 st_numgeometries(st_geomfromtext('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)')) 
--------------------------------------------------------------------------------------------------
                                                                                                1
(1 row)

--Geometry Collection Example - multis count as one geom in a collection
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)))'));
 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)))')) 
---------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                 3
(1 row)

ST_NumInteriorRings

描述

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

函数声明

integer ST_NumInteriorRings(geometry  a_polygon);

使用示例

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

ST_NumPoints

描述

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

函数声明

integer ST_NumPoints(geometry  g1);

使用示例

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

ST_PointN

描述

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

函数声明

geometry ST_PointN(geometry  a_linestring, integer  n);

使用示例

-- Extract all POINTs from a LINESTRING
SELECT ST_AsText(ST_PointN(col1, generate_series(1, ST_NPoints(col1))))
    FROM ( VALUES ('LINESTRING(0 0, 1 1, 2 2)'::geometry) ) AS foo;
 st_astext(st_pointn(col1, generate_series(1, st_npoints(col1)))) 
------------------------------------------------------------------
 POINT (0 0)
 POINT (1 1)
 POINT (2 2)
(3 rows)


SELECT ST_AsText(ST_PointN(ST_GeometryFromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)'), -2));
 st_astext(st_pointn(st_geometryfromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)'), - 2)) 
-----------------------------------------------------------------------------------
 POINT (1 1)
(1 row)

ST_SRID

描述

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

函数声明

integer ST_SRID(geometry  g1);

使用示例

SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)'));
 st_srid(st_geomfromtext('POINT(-71.1043 42.315)')) 
----------------------------------------------------
                                               4326
(1 row)

ST_StartPoint

描述

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

函数声明

geometry ST_StartPoint(geometry  geomA);

使用示例

SELECT ST_AsText(ST_StartPoint('LINESTRING(0 1, 0 2)'::geometry));
 st_astext(st_startpoint(CAST('LINESTRING(0 1, 0 2)' AS geometry))) 
--------------------------------------------------------------------
 POINT (0 1)
(1 row)


SELECT ST_StartPoint('POINT(0 1)'::geometry) IS NULL AS is_null;
 is_null 
---------
 t
(1 row)

ST_X

描述

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

函数声明

float ST_X(geometry  a_point);

使用示例

SELECT ST_X(ST_GeomFromWKT('POINT(1 2)'));
 st_x(st_geomfromwkt('POINT(1 2)')) 
------------------------------------
                                1.0
(1 row)


SELECT ST_X(ST_Centroid(ST_GeomFromWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
 st_x(st_centroid(st_geomfromwkt('LINESTRING(1 2 3 4, 1 1 1 1)'))) 
-------------------------------------------------------------------
                                                               1.0
(1 row)

ST_Y

描述

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

函数声明

float ST_Y(geometry  a_point);

使用示例

SELECT ST_Y(ST_GeomFromWKT('POINT(1 2 3 4)'));
 st_y(st_geomfromwkt('POINT(1 2 3 4)')) 
----------------------------------------
                                    2.0
(1 row)


SELECT ST_Y(ST_Centroid(ST_GeomFromWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
 st_y(st_centroid(st_geomfromwkt('LINESTRING(1 2 3 4, 1 1 1 1)'))) 
-------------------------------------------------------------------
                                                               1.5
(1 row)

ST_Z

描述

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

函数声明

float ST_Z(geometry  a_point);

使用示例

SELECT ST_Z(ST_GeomFromWKT('POINT(1 2 3 4)')) is null;
 (st_z(st_geomfromwkt('POINT(1 2 3 4)')) IS NULL) 
--------------------------------------------------
 t
(1 row)
  • 本页导读 (1)
文档反馈