空间对象关系函数
ST_Area
描述
返回给定Polygon或MultiPolygon的表面积。如果是一个几何对象,则返回其SRID指定的坐标参考系下的笛卡尔面积。对于地理对象,返回其在球面上的面积,以平方米为单位。
函数声明
float ST_Area(geometry g1);
使用示例
SELECT ST_Area(the_geom) FROM (SELECT ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))') ) As foo(the_geom);
st_area(the_geom)
-------------------
1.0
(1 row)
ST_Centroid
描述
返回给定几何对象或地理对象的形心。
函数声明
geometry ST_Centroid(geometry g1);
使用示例
SELECT ST_AsText(ST_Centroid('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )'));
st_astext(st_centroid('MULTIPOINT ( -1 0, -1 2, -1 3, -1 4, -1 7, 0 1, 0 3, 1 1, 2 0, 6 0, 7 8, 9 8, 10 6 )'))
----------------------------------------------------------------------------------------------------------------
POINT (2.3076923076923075 3.3076923076923075)
(1 row)
ST_ClosestPoint
描述
返回几何对象g1之中,离几何对象g2最近的坐标点。
函数声明
geometry ST_ClosestPoint(geometry g1, geometry g2);
使用示例
SELECT ST_AsText(ST_ClosestPoint(pt,line)) AS cp_pt_line,
ST_AsText(ST_ClosestPoint(line,pt)) As cp_line_pt
FROM (SELECT 'POINT(100 10)'::geometry As pt,
'LINESTRING (20 80, 98 90, 110 80, 50 75 )'::geometry As line
) As foo;
cp_pt_line | cp_line_pt
----------------+--------------------------------------------
POINT (100 10) | POINT (94.27586206896552 78.6896551724138)
(1 row)
ST_Contains
描述
如果几何对象A包含几何对象B,则返回true。
函数声明
boolean ST_Contains(geometry geomA, geometry geomB);
使用示例
-- A circle within a circle
SELECT ST_Contains(smallc, bigc) As smallcontainsbig,
ST_Contains(bigc,smallc) As bigcontainssmall,
ST_Contains(bigc, ST_Union(smallc, bigc)) as bigcontainsunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
smallcontainsbig | bigcontainssmall | bigcontainsunion | bigisunion | bigcontainsexterior
------------------+------------------+------------------+------------+---------------------
f | t | t | t | f
(1 row)
SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa,
ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba
FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5) ),
( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),
( ST_Point(1,1) )
) As foo(geomA);
geoma | acontainsa | acontainsba
---------------+------------+-------------
ST_Polygon | t | f
ST_LineString | t | f
ST_Point | t | f
(3 rows)
ST_Crosses
描述
如果给定的两个几何对象相交,则返回true。
函数声明
boolean ST_Crosses(geometry g1, geometry g2);
使用示例
SELECT ST_Crosses('LINESTRING (0 0, 3 3)'::geometry, 'LINESTRING (3 0, 0 3)' :: geometry);
st_crosses(CAST('LINESTRING (0 0, 3 3)' AS geometry), CAST('LINESTRING (3 0, 0 3)' AS geometry))
--------------------------------------------------------------------------------------------------
t
(1 row)
ST_Disjoint
描述
如果给定的两个几何对象不相接,返回TRUE。
函数声明
boolean ST_Disjoint(geometry A, geometry B);
使用示例
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
st_disjoint(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 2 0, 0 2 )' AS geometry))
------------------------------------------------------------------------------------------
t
(1 row)
doc=> SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
st_disjoint(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 0 0, 0 2 )' AS geometry))
------------------------------------------------------------------------------------------
f
(1 row)
ST_Distance
描述
对于几何对象,返回2维基于参考坐标系的笛卡尔距离。对于地理对象,返回两个对象的最小球面距离,以米为单位。
函数声明
float ST_Distance(geometry g1, geometry g2);
使用示例
--Geometry example - units in planar degrees 4326 is WGS 84 long lat unit=degrees
SELECT ST_Distance(
'POINT(-72.1235 42.3521)'::geometry,
'LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry
);
st_distance(CAST('POINT(-72.1235 42.3521)' AS geometry), CAST('LINESTRING(-72.1260 42.45, -72.123 42.1546)' AS geometry))
---------------------------------------------------------------------------------------------------------------------------
0.0015056772638228177
(1 row)
ST_Equals
描述
如果给定的两个几何对象代表了同一个对象,那么返回true。
函数声明
boolean ST_Equals(geometry A, geometry B);
使用示例
SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
st_equals(st_geomfromtext('LINESTRING(0 0, 10 10)'), st_geomfromtext('LINESTRING(0 0, 5 5, 10 10)'))
------------------------------------------------------------------------------------------------------
t
(1 row)
ST_Intersects
描述
如果给定的几何对象在2维空间内相交,则返回true。如果两个坐标点距离小于0.00001米,则视为相交。
函数声明
boolean ST_Intersects( geometry geomA , geometry geomB );
使用示例
SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
st_intersects(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 2 0, 0 2 )' AS geometry))
--------------------------------------------------------------------------------------------
f
(1 row)
SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
st_intersects(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 0 0, 0 2 )' AS geometry))
--------------------------------------------------------------------------------------------
t
(1 row)
ST_Length
描述
对于给定的LineString或MultiLineString,返回其2维空间内的长度。
函数声明
float ST_Length(geometry a_2dlinestring);
使用示例
SELECT ST_Length(ST_GeomFromText('LINESTRING(43 29.67416,74.3238 29.67450,74.3265 29.67450,74.3265625 29.67416,74.3238 29.67416)'));
st_length(st_geomfromtext('LINESTRING(43 29.67416,74.3238 29.67450,74.3265 29.67450,74.3265625 29.67416,74.3238 29.67416)'))
------------------------------------------------------------------------------------------------------------------------------
31.329608198605403
(1 row)
SELECT ST_Length(
ST_GeomFromWKT('LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)')
);
st_length(st_geomfromwkt('LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)'))
---------------------------------------------------------------------------------------------
0.30901547439030225
(1 row)
ST_Overlaps
描述
如果两个几何对象相交,返回TRUE。
函数声明
boolean ST_Overlaps(geometry A, geometry B);
使用示例
--a point on a line is contained by the line and is of a lower dimension, and therefore does not overlap the line
nor crosses
SELECT ST_Overlaps(a,b) As a_overlap_b, ST_Crosses(a,b) As a_crosses_b, ST_Intersects(a, b) As a_intersects_b, ST_Contains(b,a) As b_contains_a
FROM (SELECT ST_GeomFromText('POINT(1 0.5)') As a, ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') As b) a; ;
a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
-------------+-------------+----------------+--------------
f | f | t | t
(1 row)
--a line that is partly contained by circle, but not fully is defined as intersecting and crossing,
-- but since of different dimension it does not overlap
SELECT ST_Overlaps(a,b) As a_overlap_b, ST_Crosses(a,b) As a_crosses_b,
ST_Intersects(a, b) As a_intersects_b,
ST_Contains(a,b) As a_contains_b
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 0.5)'), 3) As a, ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') As b)
As foo;
a_overlap_b | a_crosses_b | a_intersects_b | a_contains_b
-------------+-------------+----------------+--------------
f | t | t | f
-- a 2-dimensional bent hot dog (aka buffered line string) that intersects a circle,
-- but is not fully contained by the circle is defined as overlapping since they are of the same dimension,
-- but it does not cross, because the intersection of the 2 is of the same dimension
-- as the maximum dimension of the 2
SELECT ST_Overlaps(a,b) As a_overlap_b, ST_Crosses(a,b) As a_crosses_b, ST_Intersects(a, b) As a_intersects_b,
ST_Contains(b,a) As b_contains_a,
ST_Dimension(a) As dim_a, ST_Dimension(b) as dim_b, ST_Dimension(ST_Intersection(a,b)) As dima_intersection_b
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 0.5)'), 3) As a,
ST_Buffer(ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)'),0.5) As b)
As foo;
a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a | dim_a | dim_b | dima_intersection_b
-------------+-------------+----------------+--------------+-------+-------+---------------------
t | f | t | f | 2 | 2 | 2
ST_Perimeter
描述
返回给定几何对象或地理对象的周长。
函数声明
float ST_Perimeter(geometry g1);
使用示例
SELECT ST_Perimeter('POLYGON ((0 0, 2 2, 0 2, 0 0))'::geometry);
st_perimeter(CAST('POLYGON ((0 0, 2 2, 0 2, 0 0))' AS geometry))
------------------------------------------------------------------
6.82842712474619
(1 row)
ST_Relate
描述
如果给定的几何对象与另一个给定的几何对象相关,则返回true。
函数声明
text ST_Relate(geometry geomA, geometry geomB);
使用示例
SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2));
st_relate(st_geometryfromtext('POINT(1 2)'), st_buffer(st_geometryfromtext('POINT(1 2)'), 2))
-----------------------------------------------------------------------------------------------
0FFFFF212
(1 row)
SELECT ST_Relate(ST_GeometryFromText('LINESTRING(1 2, 3 4)'), ST_GeometryFromText('LINESTRING(5 6, 7 8)'));
st_relate(st_geometryfromtext('LINESTRING(1 2, 3 4)'), st_geometryfromtext('LINESTRING(5 6, 7 8)'))
-----------------------------------------------------------------------------------------------------
FF1FF0102
(1 row)
SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
st_relate(st_geometryfromtext('POINT(1 2)'), st_buffer(st_geometryfromtext('POINT(1 2)'), 2), '0FFFFF212')
------------------------------------------------------------------------------------------------------------
t
(1 row)
SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '*FF*FF212');
st_relate(st_geometryfromtext('POINT(1 2)'), st_buffer(st_geometryfromtext('POINT(1 2)'), 2), '*FF*FF212')
------------------------------------------------------------------------------------------------------------
t
(1 row)
ST_Touches
描述
返回给定的两个几何对象是否相接。
函数声明
boolean ST_Touches(geometry g1, geometry g2);
使用示例
SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(1 1)'::geometry);
st_touches(CAST('LINESTRING(0 0, 1 1, 0 2)' AS geometry), CAST('POINT(1 1)' AS geometry))
-------------------------------------------------------------------------------------------
f
(1 row)
SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);
st_touches(CAST('LINESTRING(0 0, 1 1, 0 2)' AS geometry), CAST('POINT(0 2)' AS geometry))
-------------------------------------------------------------------------------------------
t
(1 row)
ST_Within
描述
如果给定的几何对象A完全在几何对象B之内,返回true。
函数声明
boolean ST_Within(geometry A, geometry B);
使用示例
--a circle within a circle
SELECT ST_Within(smallc,smallc) As smallinsmall,
ST_Within(smallc, bigc) As smallinbig,
ST_Within(bigc,smallc) As biginsmall,
ST_Within(ST_Union(smallc, bigc), bigc) as unioninbig,
ST_Within(bigc, ST_Union(smallc, bigc)) as biginunion,
ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion
FROM
(
SELECT ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) As smallc,
ST_Buffer(ST_GeomFromText('POINT(50 50)'), 40) As bigc) As foo;
--Result
smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
--------------+------------+------------+------------+------------+------------
t | t | f | t | t | t
(1 row)
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论