空间对象访问函数
GeometryType
描述
返回几何对象的类型名,比如: ‘LINESTRING’, ‘POLYGON’, ‘MULTIPOINT’, 等等.
函数声明
text GeometryType(geometry geomA);
使用示例
SELECT GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
geometrytype
--------------
LINESTRING
ST_Boundary
描述
返回给定几何对象的边界。
函数声明
geometry ST_Boundary(geometry geomA);
使用示例
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')));
st_astext
-----------
MULTIPOINT(1 1,-1 1)
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')));
st_astext
----------
LINESTRING(1 1,0 0,-1 1,1 1)
--Using a 3d polygon
SELECT ST_AsEWKT(ST_Boundary(ST_GeomFromEWKT('POLYGON((1 1 1,0 0 1, -1 1 1, 1 1 1))')));
st_asewkt
-----------------------------------
LINESTRING(1 1 1,0 0 1,-1 1 1,1 1 1)
--Using a 3d multilinestring
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) )')));
st_asewkt
----------
MULTIPOINT(-1 1 1,1 1 0.75)
ST_CoordDim
描述
返回给定几何对象的维度数。
函数声明
integer ST_CoordDim(geometry geomA);
使用示例
SELECT ST_CoordDim('CIRCULARSTRING(1 2 3, 1 3 4, 5 6 7, 8 9 10, 11 12 13)');
---result--
3
SELECT ST_CoordDim(ST_Point(1,2));
--result--
2
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
-----------
1
ST_EndPoint
描述
返回给定LineString参数的最后一个点,如果参数不是一个LineString,那么返回NULL。
函数声明
boolean ST_EndPoint(geometry g);
使用示例
postgis=# SELECT ST_AsText(ST_EndPoint('LINESTRING(1 1, 2 2, 3 3)'::geometry));
st_astext
------------
POINT(3 3)
(1 row)
postgis=# SELECT ST_EndPoint('POINT(1 1)'::geometry) IS NULL AS is_null;
is_null
----------
t
(1 row)
--3d endpoint
SELECT ST_AsEWKT(ST_EndPoint('LINESTRING(1 1 2, 1 2 3, 0 0 5)'));
st_asewkt
--------------
POINT(0 0 5)
(1 row)
ST_Envelope
描述
返回给定几何对象的边界框。
函数声明
geometry ST_Envelope(geometry g1);
使用示例
SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
st_astext
------------
POINT(1 3)
(1 row)
SELECT ST_AsText(ST_Envelope('LINESTRING(0 0, 1 3)'::geometry));
st_astext
--------------------------------
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
--------------------------------------------------------------
POLYGON((0 0,0 1,1.00000011920929 1,1.00000011920929 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
--------------------------------------------------------------
POLYGON((0 0,0 1,1.00000011920929 1,1.00000011920929 0,0 0))
(1 row)
SELECT Box3D(geom), Box2D(geom), ST_AsText(ST_Envelope(geom)) As envelopewkt
FROM (SELECT 'POLYGON((0 0, 0 1000012333334.34545678, 1.0000001 1, 1.0000001 0, 0 0))'::geometry As geom) As foo;
ST_BoundingDiagonal
描述
返回一个LineString,代表给定几何对象的边界框的对角线。
函数声明
geometry ST_BoundingDiagonal(geometry geom, boolean fits=false);
使用示例
-- Get the minimum X in a buffer around a point
SELECT ST_X(ST_StartPoint(ST_BoundingDiagonal(
ST_Buffer(ST_MakePoint(0,0),10)
)));
st_x
------
-10
ST_ExteriorRing
描述
返回一个LineString,代表给定的Polygon参数的外边界。如果参数不是一个Polygon,就返回NULL。
函数声明
geometry ST_ExteriorRing(geometry a_polygon);
使用示例
--If you have a table of polygons
SELECT gid, ST_ExteriorRing(the_geom) AS ering
FROM sometable;
--If you have a table of MULTIPOLYGONs
--and want to return a MULTILINESTRING composed of the exterior rings of each polygon
SELECT gid, ST_Collect(ST_ExteriorRing(the_geom)) AS erings
FROM (SELECT gid, (ST_Dump(the_geom)).geom As the_geom
FROM sometable) As foo
GROUP BY gid;
--3d Example
SELECT ST_AsEWKT(
ST_ExteriorRing(
ST_GeomFromEWKT('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))')
)
);
st_asewkt
---------
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。
函数声明
geometry ST_GeometryN(geometry geomA, integer n);
使用示例
--Extracting a subset of points from a 3d multipoint
SELECT n, ST_AsEWKT(ST_GeometryN(the_geom, n)) As geomewkt
FROM (
VALUES (ST_GeomFromEWKT('MULTIPOINT(1 2 7, 3 4 7, 5 6 7, 8 9 10)') ),
( ST_GeomFromEWKT('MULTICURVE(CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (10 11, 12 11))') )
)As foo(the_geom)
CROSS JOIN generate_series(1,100) n
WHERE n <= ST_NumGeometries(the_geom);
n | geomewkt
---+-----------------------------------------
1 | POINT(1 2 7)
2 | POINT(3 4 7)
3 | POINT(5 6 7)
4 | POINT(8 9 10)
1 | CIRCULARSTRING(2.5 2.5,4.5 2.5,3.5 3.5)
2 | LINESTRING(10 11,12 11)
--Extracting all geometries (useful when you want to assign an id)
SELECT gid, n, ST_GeometryN(the_geom, n)
FROM sometable CROSS JOIN generate_series(1,100) n
WHERE n <= ST_NumGeometries(the_geom);
ST_GeometryType
描述
返回给定几何对象的类型。
函数声明
text ST_GeometryType(geometry g1);
使用示例
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_LineString
ST_InteriorRingN
描述
返回一个LineString代表输入Polygon参数的第n条内部边界圈(从1开始计数)。如果输入参数不是一个Polygon或者n超出了范围,则返回NULL。
函数声明
geometry ST_InteriorRingN(geometry a_polygon, integer n);
使用示例
SELECT ST_AsText(ST_InteriorRingN(the_geom, 1)) As the_geom
FROM (SELECT ST_BuildArea(
ST_Collect(ST_Buffer(ST_Point(1,2), 20,3),
ST_Buffer(ST_Point(1, 2), 10,3))) As the_geom
) as foo;
the_geom
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------
LINESTRING(11 2,9.66025403784436 7.00000000000005,5.99999999999995 10.6602540378444,0.999999999999954 12,-4.00000000000003 10.6
602540378444,-7.66025403784441 6.99999999999997,-9 1.99999999999997,-7.66025403784437 -3.00000000000002,-3.99999999999998 -6.660
2540378444,1.00000000000001 -8,6.00000000000001 -6.66025403784438,9.66025403784439 -3,11 2)
ST_IsPolygonCCW
描述
如果输入Polygon参数的所有外边界都是逆时针的,所有内边界都是顺时针的,则返回true。
函数声明
boolean ST_IsPolygonCCW(geometry geom);
使用示例
None
ST_IsPolygonCW
描述
如果输入Polygon参数的所有外边界都是顺时针的,所有内边界都是逆时针的,则返回true。
函数声明
boolean ST_IsPolygonCW(geometry geom);
使用示例
None
ST_IsClosed
描述
如果输入几何对象是封闭的,则返回true。
函数声明
boolean ST_IsClosed(geometry g);
使用示例
postgis=# SELECT ST_IsClosed('LINESTRING(0 0, 1 1)'::geometry);
st_isclosed
-------------
f
(1 row)
postgis=# SELECT ST_IsClosed('LINESTRING(0 0, 0 1, 1 1, 0 0)'::geometry);
st_isclosed
-------------
t
(1 row)
postgis=# SELECT ST_IsClosed('MULTILINESTRING((0 0, 0 1, 1 1, 0 0),(0 0, 1 1))'::geometry);
st_isclosed
-------------
f
(1 row)
postgis=# SELECT ST_IsClosed('POINT(0 0)'::geometry);
st_isclosed
-------------
t
(1 row)
postgis=# SELECT ST_IsClosed('MULTIPOINT((0 0), (1 1))'::geometry);
st_isclosed
-------------
t
(1 row)
ST_IsCollection
描述
如果输入参数是如下类型时,返回true:
- GEOMETRYCOLLECTION
- MULTI{POINT,POLYGON,LINESTRING,CURVE,SURFACE}
- COMPOUNDCURVE
函数声明
boolean ST_IsCollection(geometry g);
使用示例
postgis=# SELECT ST_IsCollection('LINESTRING(0 0, 1 1)'::geometry);
st_iscollection
-------------
f
(1 row)
postgis=# SELECT ST_IsCollection('MULTIPOINT EMPTY'::geometry);
st_iscollection
-------------
t
(1 row)
postgis=# SELECT ST_IsCollection('MULTIPOINT((0 0))'::geometry);
st_iscollection
-------------
t
(1 row)
postgis=# SELECT ST_IsCollection('MULTIPOINT((0 0), (42 42))'::geometry);
st_iscollection
-------------
t
(1 row)
postgis=# SELECT ST_IsCollection('GEOMETRYCOLLECTION(POINT(0 0))'::geometry);
st_iscollection
-------------
t
(1 row)
ST_IsEmpty
描述
如果输入参数是一个空的GeometryCollection、Polygon或Point等,返回true。
函数声明
boolean ST_IsEmpty(geometry geomA);
使用示例
SELECT ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
st_isempty
------------
t
(1 row)
SELECT ST_IsEmpty(ST_GeomFromText('POLYGON EMPTY'));
st_isempty
------------
t
(1 row)
SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
st_isempty
------------
f
(1 row)
SELECT ST_IsEmpty(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))')) = false;
?column?
----------
t
(1 row)
SELECT ST_IsEmpty(ST_GeomFromText('CIRCULARSTRING EMPTY'));
st_isempty
------------
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 | st_isclosed | st_issimple
-----------+-------------+-------------
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 | st_isclosed | st_issimple
-----------+-------------+-------------
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, 1 2))'));
st_issimple
-------------
t
(1 row)
SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)'));
st_issimple
-------------
f
(1 row)
ST_IsValid
描述
判断输入参数是否满足OGC标准中的Valid判定条件。Valid的判定标准请参考OGC标准文档。
函数声明
boolean ST_IsValid(geometry g);
boolean ST_IsValid(geometry g, integer flags);
使用示例
SELECT ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) As good_line,
ST_IsValid(ST_GeomFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) As bad_poly
--results
NOTICE: Self-intersection at or near point 0 0
good_line | bad_poly
-----------+----------
t | f
ST_IsValidReason
描述
返回一段文本描述输入参数是否为Valid对象,如果不是,则输出原因。
函数声明
text ST_IsValidReason(geometry geomA);
text ST_IsValidReason(geometry geomA, integer flags);
使用示例
--First 3 Rejects from a successful quintuplet experiment
SELECT gid, ST_IsValidReason(the_geom) as validity_info
FROM
(SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), ST_Accum(f.line)) As the_geom, gid
FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
FROM generate_series(-4,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,8) z1
WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
INNER JOIN (SELECT ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1*1, z1*2) As line
FROM generate_series(-3,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,10) z1
WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
GROUP BY gid, e.buff) As quintuplet_experiment
WHERE ST_IsValid(the_geom) = false
ORDER BY gid
LIMIT 3;
gid | validity_info
------+--------------------------
5330 | Self-intersection [32 5]
5340 | Self-intersection [42 5]
5350 | Self-intersection [52 5]
--simple example
SELECT ST_IsValidReason('LINESTRING(220227 150406,2220227 150407,222020 150410)');
st_isvalidreason
------------------
Valid Geometry
ST_IsValidDetail
描述
返回关于传入几何对象是否为Valid的详细的信息,如果不是,则输出不是的原因,及其所在位置。
函数声明
valid_detail ST_IsValidDetail(geometry geom);
valid_detail ST_IsValidDetail(geometry geom, integer flags);
使用示例
--First 3 Rejects from a successful quintuplet experiment
SELECT gid, reason(ST_IsValidDetail(the_geom)), ST_AsText(location(ST_IsValidDetail(the_geom))) as location
FROM
(SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), ST_Accum(f.line)) As the_geom, gid
FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
FROM generate_series(-4,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,8) z1
WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
INNER JOIN (SELECT ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1*1, z1*2) As line
FROM generate_series(-3,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,10) z1
WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
GROUP BY gid, e.buff) As quintuplet_experiment
WHERE ST_IsValid(the_geom) = false
ORDER BY gid
LIMIT 3;
gid | reason | location
------+-------------------+-------------
5330 | Self-intersection | POINT(32 5)
5340 | Self-intersection | POINT(42 5)
5350 | Self-intersection | POINT(52 5)
--simple example
SELECT * FROM ST_IsValidDetail('LINESTRING(220227 150406,2220227 150407,222020 150410)');
valid | reason | location
-------+--------+----------
t | |
ST_M
描述
返回传入点的M坐标值。传入参数必须是一个坐标点。
函数声明
float ST_M(geometry a_point);
使用示例
SELECT ST_M(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_m
------
4
(1 row)
ST_NDims
描述
返回传入几何对象的坐标维度值。
函数声明
integer ST_NDims(geometry g1);
使用示例
SELECT ST_NDims(ST_GeomFromText('POINT(1 1)')) As d2point,
ST_NDims(ST_GeomFromEWKT('POINT(1 1 2)')) As d3point,
ST_NDims(ST_GeomFromEWKT('POINTM(1 1 0.5)')) As d2pointm;
d2point | d3point | d2pointm
---------+---------+----------
2 | 3 | 3
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)'));
--result
4
--Polygon in 3D space
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)'))
--result
4
ST_NRings
描述
如果输入对象是一个Polygon或MultiPolygon,返回其中环的个数。
函数声明
integer ST_NRings(geometry geomA);
使用示例
SELECT ST_NRings(the_geom) As Nrings, ST_NumInteriorRings(the_geom) As ninterrings
FROM (SELECT ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))') As the_geom) As foo;
nrings | ninterrings
--------+-------------
1 | 0
(1 row)
ST_NumGeometries
描述
如果输入对象是几何集合,如:GEOMETRYCOLLECTION (或者 MULTI*),则返回其中几何对象的个数。
函数声明
integer ST_NumGeometries(geometry geom);
使用示例
--Prior versions would have returned NULL for this -- in 2.0.0 this returns 1
SELECT ST_NumGeometries(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
--result
1
--Geometry Collection Example - multis count as one geom in a collection
SELECT ST_NumGeometries(ST_GeomFromEWKT('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)))'));
--result
3
ST_NumInteriorRings
描述
返回输入Polygon对象的内边界环的个数。
函数声明
integer ST_NumInteriorRings(geometry a_polygon);
使用示例
--If you have a regular polygon
SELECT gid, field1, field2, ST_NumInteriorRings(the_geom) AS numholes
FROM sometable;
--If you have multipolygons
--And you want to know the total number of interior rings in the MULTIPOLYGON
SELECT gid, field1, field2, SUM(ST_NumInteriorRings(the_geom)) AS numholes
FROM (SELECT gid, field1, field2, (ST_Dump(the_geom)).geom As the_geom
FROM sometable) As foo
GROUP BY gid, field1,field2;
ST_NumInteriorRing
描述
同ST_NumInteriorRings。
函数声明
integer ST_NumInteriorRing(geometry a_polygon);
使用示例
None
ST_NumPatches
描述
返回一个Polyhedral曲面的平面数。
函数声明
integer ST_NumPatches(geometry g1);
使用示例
SELECT ST_NumPatches(ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((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)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((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)) )'));
st_numpatches
---------------
6
ST_NumPoints
描述
返回一个LineString或CircularString中包含的点的个数。
函数声明
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)'));
--result
4
ST_PatchN
描述
返回输入几何对象的第n个平面(从1开始编号),如果输入对象是: POLYHEDRALSURFACE, POLYHEDRALSURFACEM。其它情况下,返回NULL。
函数声明
geometry ST_PatchN(geometry geomA, integer n);
使用示例
--Extract the 2nd face of the polyhedral surface
SELECT ST_AsEWKT(ST_PatchN(geom, 2)) As geomewkt
FROM (
VALUES (ST_GeomFromEWKT('POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((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)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((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);
geomewkt
---+-----------------------------------------
POLYGON((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0))
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(
column1,
generate_series(1, ST_NPoints(column1))
))
FROM ( VALUES ('LINESTRING(0 0, 1 1, 2 2)'::geometry) ) AS foo;
st_astext
------------
POINT(0 0)
POINT(1 1)
POINT(2 2)
(3 rows)
--Example circular string
SELECT ST_AsText(ST_PointN(ST_GeomFromText('CIRCULARSTRING(1 2, 3 2, 1 2)'),2));
st_astext
----------
POINT(3 2)
SELECT st_astext(f)
FROM ST_GeometryFromtext('LINESTRING(0 0 0, 1 1 1, 2 2 2)') as g
,ST_PointN(g, -2) AS f -- 1 based index
st_astext
----------
"POINT Z (1 1 1)"
ST_Points
描述
返回一个MultiPoint其中包含了输入几何对象的所有的顶点。
函数声明
geometry ST_Points(geometry geom);
使用示例
SELECT ST_AsText(ST_Points('POLYGON Z ((30 10 4,10 30 5,40 40 6, 30 10))'));
--result
MULTIPOINT Z (30 10 4,10 30 5,40 40 6, 30 10 4)
ST_SRID
描述
返回给定几何对象的SRID(坐标参考系标识)。SRID的定义在系统表spatial_ref_sys中。
函数声明
integer ST_SRID(geometry g1);
使用示例
SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
--result
4326
ST_StartPoint
描述
返回一个LineString或环形LineString的起点,如果输入对象不是这两个类型的,则返回NULL。
函数声明
geometry ST_StartPoint(geometry geomA);
使用示例
SELECT ST_AsText(ST_StartPoint('LINESTRING(0 1, 0 2)'::geometry));
st_astext
------------
POINT(0 1)
(1 row)
SELECT ST_StartPoint('POINT(0 1)'::geometry) IS NULL AS is_null;
is_null
----------
t
(1 row)
--3d line
SELECT ST_AsEWKT(ST_StartPoint('LINESTRING(0 1 1, 0 2 2)'::geometry));
st_asewkt
------------
POINT(0 1 1)
(1 row)
-- circular linestring --
SELECT ST_AsText(ST_StartPoint('CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 5 2)'::geometry));
st_astext
------------
POINT(5 2)
ST_Summary
描述
返回输入几何对象的一段文本摘要。
函数声明
text ST_Summary(geometry g);
text ST_Summary(geography g);
使用示例
=# SELECT ST_Summary(ST_GeomFromText('LINESTRING(0 0, 1 1)')) as geom,
ST_Summary(ST_GeogFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) geog;
geom | geog
-----------------------------+--------------------------
LineString[B] with 2 points | Polygon[BGS] with 1 rings
| ring 0 has 5 points
:
(1 row)
=# SELECT ST_Summary(ST_GeogFromText('LINESTRING(0 0 1, 1 1 1)')) As geog_line,
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;
;
geog_line | geom_poly
-------------------------------- +--------------------------
LineString[ZBGS] with 2 points | Polygon[ZBS] with 1 rings
: ring 0 has 5 points
:
(1 row)
ST_X
描述
返回一个几何坐标点的X坐标值。
函数声明
float ST_X(geometry a_point);
使用示例
SELECT ST_X(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_x
------
1
(1 row)
SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
st_y
------
1.5
(1 row)
ST_XMax
描述
返回输入几何对象的边界框的X坐标的最大值。
函数声明
float ST_XMax(box3d aGeomorBox2DorBox3D);
使用示例
SELECT ST_XMax('BOX3D(1 2 3, 4 5 6)');
st_xmax
-------
4
SELECT ST_XMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_xmax
-------
5
SELECT ST_XMax(CAST('BOX(-3 2, 3 4)' As box2d));
st_xmax
-------
3
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
SELECT ST_XMax('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_XMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
st_xmax
--------
220288.248780547
ST_XMin
描述
返回输入几何对象的边界框的X坐标的最小值。
函数声明
float ST_XMin(box3d aGeomorBox2DorBox3D);
使用示例
SELECT ST_XMin('BOX3D(1 2 3, 4 5 6)');
st_xmin
-------
1
SELECT ST_XMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_xmin
-------
1
SELECT ST_XMin(CAST('BOX(-3 2, 3 4)' As box2d));
st_xmin
-------
-3
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
SELECT ST_XMin('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_XMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
st_xmin
--------
220186.995121892
ST_Y
描述
返回输入坐标点的Y坐标值。
函数声明
float ST_Y(geometry a_point);
使用示例
SELECT ST_Y(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_y
------
2
(1 row)
SELECT ST_Y(ST_Centroid(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)')));
st_y
------
1.5
(1 row)
ST_YMax
描述
返回输入几何对象的边界框的Y坐标的最大值。
函数声明
float ST_YMax(box3d aGeomorBox2DorBox3D);
使用示例
SELECT ST_YMax('BOX3D(1 2 3, 4 5 6)');
st_ymax
-------
5
SELECT ST_YMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_ymax
-------
6
SELECT ST_YMax(CAST('BOX(-3 2, 3 4)' As box2d));
st_ymax
-------
4
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
SELECT ST_YMax('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_YMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
st_ymax
--------
150506.126829327
ST_YMin
描述
返回输入几何对象的边界框的Y坐标的最小值。
函数声明
float ST_YMin(box3d aGeomorBox2DorBox3D);
使用示例
SELECT ST_YMin('BOX3D(1 2 3, 4 5 6)');
st_ymin
-------
2
SELECT ST_YMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_ymin
-------
3
SELECT ST_YMin(CAST('BOX(-3 2, 3 4)' As box2d));
st_ymin
-------
2
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
SELECT ST_YMin('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_YMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
st_ymin
--------
150406
ST_Z
描述
返回输入坐标点的Z坐标值。
函数声明
float ST_Z(geometry a_point);
使用示例
SELECT ST_Z(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_z
------
3
(1 row)
ST_ZMax
描述
返回输入几何对象的边界框的Z坐标的最大值。
函数声明
float ST_ZMax(box3d aGeomorBox2DorBox3D);
使用示例
SELECT ST_ZMax('BOX3D(1 2 3, 4 5 6)');
st_zmax
-------
6
SELECT ST_ZMax(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
st_zmax
-------
7
SELECT ST_ZMax('BOX3D(-3 2 1, 3 4 1)' );
st_zmax
-------
1
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
SELECT ST_ZMax('LINESTRING(1 3 4, 5 6 7)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_ZMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
st_zmax
--------
3
ST_Zmflag
描述
返回一个用整数表示的输入几何对象的Z/M标志。数字值的含义如下: 0=2d, 1=3dm, 2=3dz, 3=4d。
函数声明
smallint ST_Zmflag(geometry geomA);
使用示例
SELECT ST_Zmflag(ST_GeomFromEWKT('LINESTRING(1 2, 3 4)'));
st_zmflag
-----------
0
SELECT ST_Zmflag(ST_GeomFromEWKT('LINESTRINGM(1 2 3, 3 4 3)'));
st_zmflag
-----------
1
SELECT ST_Zmflag(ST_GeomFromEWKT('CIRCULARSTRING(1 2 3, 3 4 3, 5 6 3)'));
st_zmflag
-----------
2
SELECT ST_Zmflag(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_zmflag
-----------
3
ST_ZMin
描述
返回输入几何对象的边界框的Z坐标的最小值。
函数声明
float ST_ZMin(box3d aGeomorBox2DorBox3D);
使用示例
SELECT ST_ZMin('BOX3D(1 2 3, 4 5 6)');
st_zmin
-------
3
SELECT ST_ZMin(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
st_zmin
-------
4
SELECT ST_ZMin('BOX3D(-3 2 1, 3 4 1)' );
st_zmin
-------
1
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a BOX3D
SELECT ST_ZMin('LINESTRING(1 3 4, 5 6 7)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_ZMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)'));
st_zmin
--------
1
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论