全部产品
云市场

空间对象关系函数

更新时间:2019-12-30 16:03:36

ST_Area

描述

返回给定Polygon或MultiPolygon的表面积。如果是一个几何对象,则返回其SRID指定的坐标参考系下的笛卡尔面积。对于地理对象,返回其在球面上的面积,以平方米为单位。

函数声明

  1. float ST_Area(geometry g1);

使用示例

  1. SELECT ST_Area(the_geom) FROM (SELECT ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))') ) As foo(the_geom);
  2. st_area(the_geom)
  3. -------------------
  4. 1.0
  5. (1 row)

ST_Centroid

描述

返回给定几何对象或地理对象的形心。

函数声明

  1. geometry ST_Centroid(geometry g1);

使用示例

  1. 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 )'));
  2. 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 )'))
  3. ----------------------------------------------------------------------------------------------------------------
  4. POINT (2.3076923076923075 3.3076923076923075)
  5. (1 row)

ST_ClosestPoint

描述

返回几何对象g1之中,离几何对象g2最近的坐标点。

函数声明

  1. geometry ST_ClosestPoint(geometry g1, geometry g2);

使用示例

  1. SELECT ST_AsText(ST_ClosestPoint(pt,line)) AS cp_pt_line,
  2. ST_AsText(ST_ClosestPoint(line,pt)) As cp_line_pt
  3. FROM (SELECT 'POINT(100 10)'::geometry As pt,
  4. 'LINESTRING (20 80, 98 90, 110 80, 50 75 )'::geometry As line
  5. ) As foo;
  6. cp_pt_line | cp_line_pt
  7. ----------------+--------------------------------------------
  8. POINT (100 10) | POINT (94.27586206896552 78.6896551724138)
  9. (1 row)

ST_Contains

描述

如果几何对象A包含几何对象B,则返回true。

函数声明

  1. boolean ST_Contains(geometry geomA, geometry geomB);

使用示例

  1. -- A circle within a circle
  2. SELECT ST_Contains(smallc, bigc) As smallcontainsbig,
  3. ST_Contains(bigc,smallc) As bigcontainssmall,
  4. ST_Contains(bigc, ST_Union(smallc, bigc)) as bigcontainsunion,
  5. ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
  6. ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
  7. FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
  8. ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
  9. smallcontainsbig | bigcontainssmall | bigcontainsunion | bigisunion | bigcontainsexterior
  10. ------------------+------------------+------------------+------------+---------------------
  11. f | t | t | t | f
  12. (1 row)
  13. SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa,
  14. ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba
  15. FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5) ),
  16. ( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),
  17. ( ST_Point(1,1) )
  18. ) As foo(geomA);
  19. geoma | acontainsa | acontainsba
  20. ---------------+------------+-------------
  21. ST_Polygon | t | f
  22. ST_LineString | t | f
  23. ST_Point | t | f
  24. (3 rows)

ST_Crosses

描述

如果给定的两个几何对象相交,则返回true。

函数声明

  1. boolean ST_Crosses(geometry g1, geometry g2);

使用示例

  1. SELECT ST_Crosses('LINESTRING (0 0, 3 3)'::geometry, 'LINESTRING (3 0, 0 3)' :: geometry);
  2. st_crosses(CAST('LINESTRING (0 0, 3 3)' AS geometry), CAST('LINESTRING (3 0, 0 3)' AS geometry))
  3. --------------------------------------------------------------------------------------------------
  4. t
  5. (1 row)

ST_Disjoint

描述

如果给定的两个几何对象不相接,返回TRUE。

函数声明

  1. boolean ST_Disjoint(geometry A, geometry B);

使用示例

  1. SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
  2. st_disjoint(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 2 0, 0 2 )' AS geometry))
  3. ------------------------------------------------------------------------------------------
  4. t
  5. (1 row)
  6. doc=> SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
  7. st_disjoint(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 0 0, 0 2 )' AS geometry))
  8. ------------------------------------------------------------------------------------------
  9. f
  10. (1 row)

ST_Distance

描述

对于几何对象,返回2维基于参考坐标系的笛卡尔距离。对于地理对象,返回两个对象的最小球面距离,以米为单位。

函数声明

  1. float ST_Distance(geometry g1, geometry g2);

使用示例

  1. --Geometry example - units in planar degrees 4326 is WGS 84 long lat unit=degrees
  2. SELECT ST_Distance(
  3. 'POINT(-72.1235 42.3521)'::geometry,
  4. 'LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry
  5. );
  6. st_distance(CAST('POINT(-72.1235 42.3521)' AS geometry), CAST('LINESTRING(-72.1260 42.45, -72.123 42.1546)' AS geometry))
  7. ---------------------------------------------------------------------------------------------------------------------------
  8. 0.0015056772638228177
  9. (1 row)

ST_Equals

描述

如果给定的两个几何对象代表了同一个对象,那么返回true。

函数声明

  1. boolean ST_Equals(geometry A, geometry B);

使用示例

  1. SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
  2. ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
  3. st_equals(st_geomfromtext('LINESTRING(0 0, 10 10)'), st_geomfromtext('LINESTRING(0 0, 5 5, 10 10)'))
  4. ------------------------------------------------------------------------------------------------------
  5. t
  6. (1 row)

ST_Intersects

描述

如果给定的几何对象在2维空间内相交,则返回true。如果两个坐标点距离小于0.00001米,则视为相交。

函数声明

  1. boolean ST_Intersects( geometry geomA , geometry geomB );

使用示例

  1. SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
  2. st_intersects(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 2 0, 0 2 )' AS geometry))
  3. --------------------------------------------------------------------------------------------
  4. f
  5. (1 row)
  6. SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
  7. st_intersects(CAST('POINT(0 0)' AS geometry), CAST('LINESTRING ( 0 0, 0 2 )' AS geometry))
  8. --------------------------------------------------------------------------------------------
  9. t
  10. (1 row)

ST_Length

描述

对于给定的LineString或MultiLineString,返回其2维空间内的长度。

函数声明

  1. float ST_Length(geometry a_2dlinestring);

使用示例

  1. 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)'));
  2. st_length(st_geomfromtext('LINESTRING(43 29.67416,74.3238 29.67450,74.3265 29.67450,74.3265625 29.67416,74.3238 29.67416)'))
  3. ------------------------------------------------------------------------------------------------------------------------------
  4. 31.329608198605403
  5. (1 row)
  6. SELECT ST_Length(
  7. ST_GeomFromWKT('LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)')
  8. );
  9. st_length(st_geomfromwkt('LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)'))
  10. ---------------------------------------------------------------------------------------------
  11. 0.30901547439030225
  12. (1 row)

ST_Overlaps

描述

如果两个几何对象相交,返回TRUE。

函数声明

  1. boolean ST_Overlaps(geometry A, geometry B);

使用示例

  1. --a point on a line is contained by the line and is of a lower dimension, and therefore does not overlap the line
  2. nor crosses
  3. 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
  4. FROM (SELECT ST_GeomFromText('POINT(1 0.5)') As a, ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') As b) a; ;
  5. a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
  6. -------------+-------------+----------------+--------------
  7. f | f | t | t
  8. (1 row)
  9. --a line that is partly contained by circle, but not fully is defined as intersecting and crossing,
  10. -- but since of different dimension it does not overlap
  11. SELECT ST_Overlaps(a,b) As a_overlap_b, ST_Crosses(a,b) As a_crosses_b,
  12. ST_Intersects(a, b) As a_intersects_b,
  13. ST_Contains(a,b) As a_contains_b
  14. FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 0.5)'), 3) As a, ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)') As b)
  15. As foo;
  16. a_overlap_b | a_crosses_b | a_intersects_b | a_contains_b
  17. -------------+-------------+----------------+--------------
  18. f | t | t | f
  19. -- a 2-dimensional bent hot dog (aka buffered line string) that intersects a circle,
  20. -- but is not fully contained by the circle is defined as overlapping since they are of the same dimension,
  21. -- but it does not cross, because the intersection of the 2 is of the same dimension
  22. -- as the maximum dimension of the 2
  23. 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,
  24. ST_Contains(b,a) As b_contains_a,
  25. ST_Dimension(a) As dim_a, ST_Dimension(b) as dim_b, ST_Dimension(ST_Intersection(a,b)) As dima_intersection_b
  26. FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 0.5)'), 3) As a,
  27. ST_Buffer(ST_GeomFromText('LINESTRING(1 0, 1 1, 3 5)'),0.5) As b)
  28. As foo;
  29. a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a | dim_a | dim_b | dima_intersection_b
  30. -------------+-------------+----------------+--------------+-------+-------+---------------------
  31. t | f | t | f | 2 | 2 | 2

ST_Perimeter

描述

返回给定几何对象或地理对象的周长。

函数声明

  1. float ST_Perimeter(geometry g1);

使用示例

  1. SELECT ST_Perimeter('POLYGON ((0 0, 2 2, 0 2, 0 0))'::geometry);
  2. st_perimeter(CAST('POLYGON ((0 0, 2 2, 0 2, 0 0))' AS geometry))
  3. ------------------------------------------------------------------
  4. 6.82842712474619
  5. (1 row)

ST_Relate

描述

如果给定的几何对象与另一个给定的几何对象相关,则返回true。

函数声明

  1. text ST_Relate(geometry geomA, geometry geomB);

使用示例

  1. SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2));
  2. st_relate(st_geometryfromtext('POINT(1 2)'), st_buffer(st_geometryfromtext('POINT(1 2)'), 2))
  3. -----------------------------------------------------------------------------------------------
  4. 0FFFFF212
  5. (1 row)
  6. SELECT ST_Relate(ST_GeometryFromText('LINESTRING(1 2, 3 4)'), ST_GeometryFromText('LINESTRING(5 6, 7 8)'));
  7. st_relate(st_geometryfromtext('LINESTRING(1 2, 3 4)'), st_geometryfromtext('LINESTRING(5 6, 7 8)'))
  8. -----------------------------------------------------------------------------------------------------
  9. FF1FF0102
  10. (1 row)
  11. SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
  12. st_relate(st_geometryfromtext('POINT(1 2)'), st_buffer(st_geometryfromtext('POINT(1 2)'), 2), '0FFFFF212')
  13. ------------------------------------------------------------------------------------------------------------
  14. t
  15. (1 row)
  16. SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '*FF*FF212');
  17. st_relate(st_geometryfromtext('POINT(1 2)'), st_buffer(st_geometryfromtext('POINT(1 2)'), 2), '*FF*FF212')
  18. ------------------------------------------------------------------------------------------------------------
  19. t
  20. (1 row)

ST_Touches

描述

返回给定的两个几何对象是否相接。

函数声明

  1. boolean ST_Touches(geometry g1, geometry g2);

使用示例

  1. SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(1 1)'::geometry);
  2. st_touches(CAST('LINESTRING(0 0, 1 1, 0 2)' AS geometry), CAST('POINT(1 1)' AS geometry))
  3. -------------------------------------------------------------------------------------------
  4. f
  5. (1 row)
  6. SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);
  7. st_touches(CAST('LINESTRING(0 0, 1 1, 0 2)' AS geometry), CAST('POINT(0 2)' AS geometry))
  8. -------------------------------------------------------------------------------------------
  9. t
  10. (1 row)

ST_Within

描述

如果给定的几何对象A完全在几何对象B之内,返回true。

函数声明

  1. boolean ST_Within(geometry A, geometry B);

使用示例

  1. --a circle within a circle
  2. SELECT ST_Within(smallc,smallc) As smallinsmall,
  3. ST_Within(smallc, bigc) As smallinbig,
  4. ST_Within(bigc,smallc) As biginsmall,
  5. ST_Within(ST_Union(smallc, bigc), bigc) as unioninbig,
  6. ST_Within(bigc, ST_Union(smallc, bigc)) as biginunion,
  7. ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion
  8. FROM
  9. (
  10. SELECT ST_Buffer(ST_GeomFromText('POINT(50 50)'), 20) As smallc,
  11. ST_Buffer(ST_GeomFromText('POINT(50 50)'), 40) As bigc) As foo;
  12. --Result
  13. smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
  14. --------------+------------+------------+------------+------------+------------
  15. t | t | f | t | t | t
  16. (1 row)