空间对象访问函数
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)
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论