全部产品
云市场

空间对象关系函数

更新时间:2019-07-08 18:09:58

ST_3DClosestPoint

描述

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

函数声明

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

使用示例

  1. SELECT ST_AsEWKT(ST_3DClosestPoint(line,pt)) AS cp3d_line_pt,
  2. ST_AsEWKT(ST_ClosestPoint(line,pt)) As cp2d_line_pt
  3. FROM (SELECT 'POINT(100 100 30)'::geometry As pt,
  4. 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 1000)'::geometry As line
  5. ) As foo;
  6. cp3d_line_pt | cp2d_line_pt
  7. -----------------------------------------------------------+------------------------------------------
  8. POINT(54.6993798867619 128.935022917228 11.5475869506606) | POINT(73.0769230769231 115.384615384615)

ST_3DDistance

描述

返回给定两个几何对象的3维笛卡尔积距离(基于参考坐标系的)。

函数声明

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

使用示例

  1. -- Geometry example - units in meters (SRID: 2163 US National Atlas Equal area) (3D point and line compared 2D point and line)
  2. -- Note: currently no vertical datum support so Z is not transformed and assumed to be same units as final.
  3. SELECT ST_3DDistance(
  4. ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(-72.1235 42.3521 4)'),2163),
  5. ST_Transform(ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45 15, -72.123 42.1546 20)'),2163)
  6. ) As dist_3d,
  7. ST_Distance(
  8. ST_Transform(ST_GeomFromText('POINT(-72.1235 42.3521)',4326),2163),
  9. ST_Transform(ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.123 42.1546)', 4326),2163)
  10. ) As dist_2d;
  11. dist_3d | dist_2d
  12. ------------------+-----------------
  13. 127.295059324629 | 126.66425605671

ST_3DDWithin

描述

对于两个3D(Z)几何对象,如果两者的距离在给定的单位之内,则返回true。

函数声明

  1. boolean ST_3DDWithin(geometry g1, geometry g2, double precision distance_of_srid);

使用示例

  1. -- Geometry example - units in meters (SRID: 2163 US National Atlas Equal area) (3D point and line compared 2D point and line)
  2. -- Note: currently no vertical datum support so Z is not transformed and assumed to be same units as final.
  3. SELECT ST_3DDWithin(
  4. ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(-72.1235 42.3521 4)'),2163),
  5. ST_Transform(ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45 15, -72.123 42.1546 20)'),2163),
  6. 126.8
  7. ) As within_dist_3d,
  8. ST_DWithin(
  9. ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(-72.1235 42.3521 4)'),2163),
  10. ST_Transform(ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45 15, -72.123 42.1546 20)'),2163),
  11. 126.8
  12. ) As within_dist_2d;
  13. within_dist_3d | within_dist_2d
  14. ----------------+----------------
  15. f | t

ST_3DDFullyWithin

描述

如果两个3D几何对象相互距离完全落在给定的范围之内,则返回true。

函数声明

  1. boolean ST_3DDFullyWithin(geometry g1, geometry g2, double precision distance);

使用示例

  1. -- This compares the difference between fully within and distance within as well
  2. -- as the distance fully within for the 2D footprint of the line/point vs. the 3d fully within
  3. SELECT ST_3DDFullyWithin(geom_a, geom_b, 10) as D3DFullyWithin10, ST_3DDWithin(geom_a, geom_b, 10) as D3DWithin10,
  4. ST_DFullyWithin(geom_a, geom_b, 20) as D2DFullyWithin20,
  5. ST_3DDFullyWithin(geom_a, geom_b, 20) as D3DFullyWithin20 from
  6. (select ST_GeomFromEWKT('POINT(1 1 2)') as geom_a,
  7. ST_GeomFromEWKT('LINESTRING(1 5 2, 2 7 20, 1 9 100, 14 12 3)') as geom_b) t1;
  8. d3dfullywithin10 | d3dwithin10 | d2dfullywithin20 | d3dfullywithin20
  9. ------------------+-------------+------------------+------------------
  10. f | t | t | f

ST_3DIntersects

描述

如果给定的几何对象在3D空间内相交,则返回true。

函数声明

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

使用示例

  1. SELECT ST_3DIntersects(pt, line), ST_Intersects(pt,line)
  2. FROM (SELECT 'POINT(0 0 2)'::geometry As pt,
  3. 'LINESTRING (0 0 1, 0 2 3 )'::geometry As line) As foo;
  4. st_3dintersects | st_intersects
  5. -----------------+---------------
  6. f | t
  7. (1 row)

ST_3DLongestLine

描述

返回两个几何对象在3维空间中的最长线段。

函数声明

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

使用示例

  1. SELECT ST_AsEWKT(ST_3DLongestLine(line,pt)) AS lol3d_line_pt,
  2. ST_AsEWKT(ST_LongestLine(line,pt)) As lol2d_line_pt
  3. FROM (SELECT 'POINT(100 100 30)'::geometry As pt,
  4. 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 1000)'::geometry As line
  5. ) As foo;
  6. lol3d_line_pt | lol2d_line_pt
  7. -----------------------------------+----------------------------
  8. LINESTRING(50 75 1000,100 100 30) | LINESTRING(98 190,100 100)

ST_3DMaxDistance

描述

返回给定的两个几何对象在3维空间中的最远距离。

函数声明

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

使用示例

  1. -- Geometry example - units in meters (SRID: 2163 US National Atlas Equal area) (3D point and line compared 2D point and line)
  2. -- Note: currently no vertical datum support so Z is not transformed and assumed to be same units as final.
  3. SELECT ST_3DMaxDistance(
  4. ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(-72.1235 42.3521 10000)'),2163),
  5. ST_Transform(ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45 15, -72.123 42.1546 20)'),2163)
  6. ) As dist_3d,
  7. ST_MaxDistance(
  8. ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(-72.1235 42.3521 10000)'),2163),
  9. ST_Transform(ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45 15, -72.123 42.1546 20)'),2163)
  10. ) As dist_2d;
  11. dist_3d | dist_2d
  12. ------------------+------------------
  13. 24383.7467488441 | 22247.8472107251

ST_3DShortestLine

描述

返回两个几何对象在3维空间中的最短线段。

函数声明

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

使用示例

  1. SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt,
  2. ST_AsEWKT(ST_ShortestLine(line,pt)) As shl2d_line_pt
  3. FROM (SELECT 'POINT(100 100 30)'::geometry As pt,
  4. 'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 1000)'::geometry As line
  5. ) As foo;
  6. shl3d_line_pt | shl2d_line_pt
  7. ----------------------------------------------------------------------------+------------------------------------------------------
  8. LINESTRING(54.6993798867619 128.935022917228 11.5475869506606,100 100 30) | LINESTRING(73.0769230769231 115.384615384615,100 100)

ST_Area

描述

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

函数声明

  1. float ST_Area(geometry g1);
  2. float ST_Area(geography geog, boolean use_spheroid=true);

使用示例

  1. SELECT ST_Area(the_geom) As sqft, ST_Area(the_geom)*POWER(0.3048,2) As sqm
  2. FROM (SELECT
  3. ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
  4. 743265 2967450,743265.625 2967416,743238 2967416))',2249) ) As foo(the_geom);
  5. sqft | sqm
  6. ---------+-------------
  7. 928.625 | 86.27208552

ST_Azimuth

描述

返回从坐标点A到坐标点B的方位角,以北方为基准往顺时针方向旋转。

函数声明

  1. float ST_Azimuth(geometry pointA, geometry pointB);
  2. float ST_Azimuth(geography pointA, geography pointB);

使用示例

  1. SELECT degrees(ST_Azimuth(ST_Point(25, 45), ST_Point(75, 100))) AS degA_B,
  2. degrees(ST_Azimuth(ST_Point(75, 100), ST_Point(25, 45))) AS degB_A;
  3. dega_b | degb_a
  4. ------------------+------------------
  5. 42.2736890060937 | 222.273689006094

ST_Angle

描述

返回三个坐标点,或两个向量(四个坐标点或两条线)之间的夹角。

函数声明

  1. float ST_Angle(geometry point1, geometry point2, geometry point3, geometry point4);
  2. float ST_Angle(geometry line1, geometry line2);

使用示例

  1. WITH rand AS (
  2. SELECT s, random() * 2 * PI() AS rad1
  3. , random() * 2 * PI() AS rad2
  4. FROM generate_series(1,2,2) AS s
  5. )
  6. , points AS (
  7. SELECT s, rad1,rad2, ST_MakePoint(cos1+s,sin1+s) as p1, ST_MakePoint(s,s) AS p2, ST_MakePoint(cos2+s,sin2+s) as p3
  8. FROM rand
  9. ,cos(rad1) cos1, sin(rad1) sin1
  10. ,cos(rad2) cos2, sin(rad2) sin2
  11. )
  12. SELECT s, ST_AsText(ST_SnapToGrid(ST_MakeLine(ARRAY[p1,p2,p3]),0.001)) AS line
  13. , degrees(ST_Angle(p1,p2,p3)) as computed_angle
  14. , round(degrees(2*PI()-rad2 -2*PI()+rad1+2*PI()))::int%360 AS reference
  15. , round(degrees(2*PI()-rad2 -2*PI()+rad1+2*PI()))::int%360 AS reference
  16. FROM points ;
  17. 1 | line | computed_angle | reference
  18. ------------------+------------------
  19. 1 | LINESTRING(1.511 1.86,1 1,0.896 0.005) | 155.27033848688 | 155

ST_Centroid

描述

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

函数声明

  1. geometry ST_Centroid(geometry g1);
  2. geography ST_Centroid(geography g1, boolean use_spheroid=true);

使用示例

  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
  3. ------------------------------------------
  4. POINT(2.30769230769231 3.30769230769231)
  5. (1 row)
  6. SELECT ST_AsText(ST_centroid(g))
  7. FROM ST_GeomFromText('CIRCULARSTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)') AS g ;
  8. ------------------------------------------
  9. POINT(0.5 1)
  10. SELECT ST_AsText(ST_centroid(g))
  11. FROM ST_GeomFromText('COMPOUNDCURVE(CIRCULARSTRING(0 2, -1 1,0 0),(0 0, 0.5 0, 1 0),CIRCULARSTRING( 1 0, 2 1, 1 2),(1 2, 0.5 2, 0 2))' ) AS g;
  12. ------------------------------------------
  13. POINT(0.5 1)

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 100)'::geometry As pt,
  4. 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry As line
  5. ) As foo;
  6. cp_pt_line | cp_line_pt
  7. ----------------+------------------------------------------
  8. POINT(100 100) | POINT(73.0769230769231 115.384615384615)

ST_ClusterDBSCAN

描述

返回聚类结果ID的窗口函数,基于2维的Density-based spatial clustering of applications with noise (DBSCAN)算法计算聚类的。

函数声明

  1. integer ST_ClusterDBSCAN(geometry winset geom, float8 eps, integer minpoints);

使用示例

clusterDBSCAN

ST_ClusterIntersecting

描述

聚合函数。给定一个几何对象的集合,返回由相连组件构成的结果数组。

函数声明

  1. geometry[] ST_ClusterIntersecting(geometry set g);

使用示例

  1. WITH testdata AS
  2. (SELECT unnest(ARRAY['LINESTRING (0 0, 1 1)'::geometry,
  3. 'LINESTRING (5 5, 4 4)'::geometry,
  4. 'LINESTRING (6 6, 7 7)'::geometry,
  5. 'LINESTRING (0 0, -1 -1)'::geometry,
  6. 'POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0))'::geometry]) AS geom)
  7. SELECT ST_AsText(unnest(ST_ClusterIntersecting(geom))) FROM testdata;
  8. st_astext
  9. ---------
  10. GEOMETRYCOLLECTION(LINESTRING(0 0,1 1),LINESTRING(5 5,4 4),LINESTRING(0 0,-1 -1),POLYGON((0 0,4 0,4 4,0 4,0 0)))
  11. GEOMETRYCOLLECTION(LINESTRING(6 6,7 7))

ST_ClusterKMeans

描述

返回每个几何对象所在的聚类结果的ID的窗口函数,聚类由K均值算法生成。

函数声明

  1. integer ST_ClusterKMeans(geometry winset geom, integer number_of_clusters);

使用示例

  1. CREATE TABLE parcels AS
  2. SELECT lpad((row_number() over())::text,3,'0') As parcel_id, geom,
  3. ('{residential, commercial}'::text[])[1 + mod(row_number()OVER(),2)] As type
  4. FROM
  5. ST_Subdivide(ST_Buffer('LINESTRING(40 100, 98 100, 100 150, 60 90)'::geometry,
  6. 40, 'endcap=square'),12) As geom;

ST_ClusterWithin

描述

聚合函数。给定一个几何对象的集合,返回由距离不大于给定数值的几何对象构成的GeometryCollections组成的结果数组。

函数声明

  1. geometry[] ST_ClusterWithin(geometry set g, float8 distance);

使用示例

  1. WITH testdata AS
  2. (SELECT unnest(ARRAY['LINESTRING (0 0, 1 1)'::geometry,
  3. 'LINESTRING (5 5, 4 4)'::geometry,
  4. 'LINESTRING (6 6, 7 7)'::geometry,
  5. 'LINESTRING (0 0, -1 -1)'::geometry,
  6. 'POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0))'::geometry]) AS geom)
  7. SELECT ST_AsText(unnest(ST_ClusterWithin(geom, 1.4))) FROM testdata;
  8. st_astext
  9. ---------
  10. GEOMETRYCOLLECTION(LINESTRING(0 0,1 1),LINESTRING(5 5,4 4),LINESTRING(0 0,-1 -1),POLYGON((0 0,4 0,4 4,0 4,0 0)))
  11. GEOMETRYCOLLECTION(LINESTRING(6 6,7 7))

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_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
  7. ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
  8. FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
  9. ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
  10. smallcontainsbig | bigcontainssmall | bigcontainsunion | bigisunion | bigcoversexterior | bigcontainsexterior
  11. ------------------+------------------+------------------+------------+-------------------+---------------------
  12. f | t | t | t | t | f
  13. -- Example demonstrating difference between contains and contains properly
  14. SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa, ST_ContainsProperly(geomA, geomA) AS acontainspropa,
  15. ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba, ST_ContainsProperly(geomA, ST_Boundary(geomA)) As acontainspropba
  16. FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5,1) ),
  17. ( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),
  18. ( ST_Point(1,1) )
  19. ) As foo(geomA);
  20. geomtype | acontainsa | acontainspropa | acontainsba | acontainspropba
  21. --------------+------------+----------------+-------------+-----------------
  22. ST_Polygon | t | f | f | f
  23. ST_LineString | t | f | f | f
  24. ST_Point | t | t | f | f

ST_ContainsProperly

描述

如果几何对象B完全在几何对象A的内部,则返回true。

函数声明

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

使用示例

  1. --a circle within a circle
  2. SELECT ST_ContainsProperly(smallc, bigc) As smallcontainspropbig,
  3. ST_ContainsProperly(bigc,smallc) As bigcontainspropsmall,
  4. ST_ContainsProperly(bigc, ST_Union(smallc, bigc)) as bigcontainspropunion,
  5. ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
  6. ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
  7. ST_ContainsProperly(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
  8. FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
  9. ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
  10. smallcontainspropbig | bigcontainspropsmall | bigcontainspropunion | bigisunion | bigcoversexterior | bigcontainsexterior
  11. ------------------+------------------+------------------+------------+-------------------+---------------------
  12. f | t | f | t | t | f
  13. --example demonstrating difference between contains and contains properly
  14. SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa, ST_ContainsProperly(geomA, geomA) AS acontainspropa,
  15. ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba, ST_ContainsProperly(geomA, ST_Boundary(geomA)) As acontainspropba
  16. FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5,1) ),
  17. ( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),
  18. ( ST_Point(1,1) )
  19. ) As foo(geomA);
  20. geomtype | acontainsa | acontainspropa | acontainsba | acontainspropba
  21. --------------+------------+----------------+-------------+-----------------
  22. ST_Polygon | t | f | f | f
  23. ST_LineString | t | f | f | f
  24. ST_Point | t | t | f | f

ST_Covers

描述

如果几何对象或地理对象B没有任何坐标点在几何对象或地理对象A之外,则返回1(TRUE)。

函数声明

  1. boolean ST_Covers(geometry geomA, geometry geomB);
  2. boolean ST_Covers(geography geogpolyA, geography geogpointB);

使用示例

  1. --a circle covering a circle
  2. SELECT ST_Covers(smallc,smallc) As smallinsmall,
  3. ST_Covers(smallc, bigc) As smallcoversbig,
  4. ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
  5. ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
  6. FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
  7. ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
  8. smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
  9. --------------+----------------+-------------------+---------------------
  10. t | f | t | f
  11. (1 row)

ST_CoveredBy

描述

如果几何对象或地理对象A没有任何坐标点在几何对象或地理对象B之外,则返回1(TRUE)。

函数声明

  1. boolean ST_CoveredBy(geometry geomA, geometry geomB);
  2. boolean ST_CoveredBy(geography geogA, geography geogB);

使用示例

  1. --a circle coveredby a circle
  2. SELECT ST_CoveredBy(smallc,smallc) As smallinsmall,
  3. ST_CoveredBy(smallc, bigc) As smallcoveredbybig,
  4. ST_CoveredBy(ST_ExteriorRing(bigc), bigc) As exteriorcoveredbybig,
  5. ST_Within(ST_ExteriorRing(bigc),bigc) As exeriorwithinbig
  6. FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
  7. ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
  8. smallinsmall | smallcoveredbybig | exteriorcoveredbybig | exeriorwithinbig
  9. --------------+-------------------+----------------------+------------------
  10. t | t | t | f
  11. (1 row)

ST_Crosses

描述

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

函数声明

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

使用示例

  1. CREATE TABLE roads (
  2. id serial NOT NULL,
  3. the_geom geometry,
  4. CONSTRAINT roads_pkey PRIMARY KEY (road_id)
  5. );

ST_LineCrossingDirection

描述

给定两个LineString,返回一个-3到3之间的整数,代表不同的相关的方式:

  • 0: LINE NO CROSS
  • -1: LINE CROSS LEFT
  • 1: LINE CROSS RIGHT
  • -2: LINE MULTICROSS END LEFT
  • 2: LINE MULTICROSS END RIGHT
  • -3: LINE MULTICROSS END SAME FIRST LEFT
  • 3: LINE MULTICROSS END SAME FIRST RIGHT

函数声明

  1. integer ST_LineCrossingDirection(geometry linestringA, geometry linestringB);

使用示例

  1. SELECT ST_LineCrossingDirection(foo.line1, foo.line2) As l1_cross_l2 ,
  2. ST_LineCrossingDirection(foo.line2, foo.line1) As l2_cross_l1
  3. FROM (
  4. SELECT
  5. ST_GeomFromText('LINESTRING(25 169,89 114,40 70,86 43)') As line1,
  6. ST_GeomFromText('LINESTRING(171 154,20 140,71 74,161 53)') As line2
  7. ) As foo;
  8. l1_cross_l2 | l2_cross_l1
  9. -------------+-------------
  10. 3 | -3

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
  3. ---------------
  4. t
  5. (1 row)
  6. SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
  7. st_disjoint
  8. ---------------
  9. f
  10. (1 row)

ST_Distance

描述

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

函数声明

  1. float ST_Distance(geometry g1, geometry g2);
  2. float ST_Distance(geography gg1, geography gg2);
  3. float ST_Distance(geography gg1, geography gg2, boolean use_spheroid);

使用示例

  1. --Geometry example - units in planar degrees 4326 is WGS 84 long lat unit=degrees
  2. SELECT ST_Distance(
  3. 'SRID=4326;POINT(-72.1235 42.3521)'::geometry,
  4. 'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry
  5. );
  6. st_distance
  7. -----------------
  8. 0.00150567726382282
  9. -- Geometry example - units in meters (SRID: 3857, proportional to pixels on popular web maps)
  10. -- although the value is off, nearby ones can be compared correctly,
  11. -- which makes it a good choice for algorithms like KNN or KMeans.
  12. SELECT ST_Distance(
  13. ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
  14. ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)
  15. );
  16. st_distance
  17. -----------------
  18. 167.441410065196
  19. -- Geometry example - units in meters (SRID: 3857 as above, but corrected by cos(lat) to account for distortion)
  20. SELECT ST_Distance(
  21. ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
  22. ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)
  23. ) * cosd(42.3521);
  24. st_distance
  25. -----------------
  26. 123.742351254151
  27. -- Geometry example - units in meters (SRID: 26986 Massachusetts state plane meters) (most accurate for Massachusetts)
  28. SELECT ST_Distance(
  29. ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 26986),
  30. ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 26986)
  31. );
  32. st_distance
  33. -----------------
  34. 123.797937878454
  35. -- Geometry example - units in meters (SRID: 2163 US National Atlas Equal area) (least accurate)
  36. SELECT ST_Distance(
  37. ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 2163),
  38. ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 2163)
  39. );
  40. st_distance
  41. ------------------
  42. 126.664256056812

ST_MinimumClearance

描述

返回给定几何对象的最小间隙。

函数声明

  1. float ST_MinimumClearance(geometry g);

使用示例

  1. SELECT ST_MinimumClearance('POLYGON ((0 0, 1 0, 1 1, 0.5 3.2e-4, 0 0))');
  2. st_minimumclearance
  3. ---------------------
  4. 0.00032

ST_MinimumClearanceLine

描述

返回一个包含两个点的LineString,代表了给定几何对象的最小间隙。

函数声明

  1. Geometry ST_MinimumClearanceLine(geometry g);

使用示例

  1. SELECT ST_AsText(ST_MinimumClearanceLine('POLYGON ((0 0, 1 0, 1 1, 0.5 3.2e-4, 0 0))'));
  2. st_astext
  3. -------------------------------
  4. LINESTRING(0.5 0.00032,0.5 0)

ST_HausdorffDistance

描述

返回给定的两个几何对象的豪斯多夫距离。

函数声明

  1. float ST_HausdorffDistance(geometry g1, geometry g2);
  2. float ST_HausdorffDistance(geometry g1, geometry g2, float densifyFrac);

使用示例

  1. SELECT DISTINCT ON(buildings.gid) buildings.gid, parcels.parcel_id
  2. FROM buildings INNER JOIN parcels ON ST_Intersects(buildings.geom,parcels.geom)
  3. ORDER BY buildings.gid, ST_HausdorffDistance(buildings.geom, parcels.geom);

ST_FrechetDistance

描述

返回给定的两个几何对象的弗雷歇距离。

函数声明

  1. float ST_FrechetDistance(geometry g1, geometry g2, float densifyFrac = -1);

使用示例

  1. SELECT st_frechetdistance('LINESTRING (0 0, 100 0)'::geometry, 'LINESTRING (0 0, 50 50, 100 0)'::geometry);
  2. st_frechetdistance
  3. --------------------
  4. 70.7106781186548
  5. (1 row)

ST_MaxDistance

描述

返回两个给定的几何对象之间的最远距离。

函数声明

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

使用示例

  1. SELECT ST_MaxDistance('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
  2. st_maxdistance
  3. -----------------
  4. 2
  5. (1 row)
  6. SELECT ST_MaxDistance('POINT(0 0)'::geometry, 'LINESTRING ( 2 2, 2 2 )'::geometry);
  7. st_maxdistance
  8. ------------------
  9. 2.82842712474619
  10. (1 row)

ST_DistanceSphere

描述

返回给定经纬度几何对象的球面距离。比ST_DistanceSpheroid更快。

函数声明

  1. float ST_DistanceSphere(geometry geomlonlatA, geometry geomlonlatB);

使用示例

  1. SELECT round(CAST(ST_DistanceSphere(ST_Centroid(the_geom), ST_GeomFromText('POINT(-118 38)',4326)) As numeric),2) As dist_meters,
  2. round(CAST(ST_Distance(ST_Transform(ST_Centroid(the_geom),32611),
  3. ST_Transform(ST_GeomFromText('POINT(-118 38)', 4326),32611)) As numeric),2) As dist_utm11_meters,
  4. round(CAST(ST_Distance(ST_Centroid(the_geom), ST_GeomFromText('POINT(-118 38)', 4326)) As numeric),5) As dist_degrees,
  5. round(CAST(ST_Distance(ST_Transform(the_geom,32611),
  6. ST_Transform(ST_GeomFromText('POINT(-118 38)', 4326),32611)) As numeric),2) As min_dist_line_point_meters
  7. FROM
  8. (SELECT ST_GeomFromText('LINESTRING(-118.584 38.374,-118.583 38.5)', 4326) As the_geom) as foo;
  9. dist_meters | dist_utm11_meters | dist_degrees | min_dist_line_point_meters
  10. -------------+-------------------+--------------+----------------------------
  11. 70424.47 | 70438.00 | 0.72900 | 65871.18

ST_DistanceSpheroid

描述

返回给定经纬度几何对象的球体距离。比ST_DistanceSpheroid更快。

函数声明

  1. float ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid);

使用示例

  1. SELECT round(CAST(
  2. ST_DistanceSpheroid(ST_Centroid(the_geom), ST_GeomFromText('POINT(-118 38)',4326), 'SPHEROID["WGS 84",6378137,298.257223563]')
  3. As numeric),2) As dist_meters_spheroid,
  4. round(CAST(ST_DistanceSphere(ST_Centroid(the_geom), ST_GeomFromText('POINT(-118 38)',4326)) As numeric),2) As dist_meters_sphere,
  5. round(CAST(ST_Distance(ST_Transform(ST_Centroid(the_geom),32611),
  6. ST_Transform(ST_GeomFromText('POINT(-118 38)', 4326),32611)) As numeric),2) As dist_utm11_meters
  7. FROM
  8. (SELECT ST_GeomFromText('LINESTRING(-118.584 38.374,-118.583 38.5)', 4326) As the_geom) as foo;
  9. dist_meters_spheroid | dist_meters_sphere | dist_utm11_meters
  10. ----------------------+--------------------+-------------------
  11. 70454.92 | 70424.47 | 70438.00

ST_DFullyWithin

描述

如果给定的几何对象互相之间的距离都小于给定的数值,则返回true。

函数声明

  1. boolean ST_DFullyWithin(geometry g1, geometry g2, double precision distance);

使用示例

  1. SELECT ST_DFullyWithin(geom_a, geom_b, 10) as DFullyWithin10, ST_DWithin(geom_a, geom_b, 10) as DWithin10, ST_DFullyWithin(geom_a, geom_b, 20) as DFullyWithin20 from
  2. (select ST_GeomFromText('POINT(1 1)') as geom_a,ST_GeomFromText('LINESTRING(1 5, 2 7, 1 9, 14 12)') as geom_b) t1;
  3. -----------------
  4. DFullyWithin10 | DWithin10 | DFullyWithin20 |
  5. ---------------+----------+---------------+
  6. f | t | t |

ST_DWithin

描述

如果给定的几何对象互相之间的距离都在给定的数值之内,则返回true。

函数声明

  1. boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
  2. boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);
  3. boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

使用示例

  1. -- Find the nearest hospital to each school
  2. -- that is within 3000 units of the school.
  3. -- We do an ST_DWithin search to utilize indexes to limit our search list
  4. -- that the non-indexable ST_Distance needs to process
  5. -- If the units of the spatial reference is meters then units would be meters
  6. SELECT DISTINCT ON (s.gid) s.gid, s.school_name, s.geom, h.hospital_name
  7. FROM schools s
  8. LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.geom, 3000)
  9. ORDER BY s.gid, ST_Distance(s.geom, h.geom);
  10. -- The schools with no close hospitals
  11. -- Find all schools with no hospital within 3000 units
  12. -- away from the school. Units is in units of spatial ref (e.g. meters, feet, degrees)
  13. SELECT s.gid, s.school_name
  14. FROM schools s
  15. LEFT JOIN hospitals h ON ST_DWithin(s.geom, h.geom, 3000)
  16. WHERE h.gid IS NULL;
  17. -- Find broadcasting towers that receiver with limited range can receive.
  18. -- Data is geometry in Spherical Mercator (SRID=3857), ranges are approximate.
  19. -- Create geometry index that will check proximity limit of user to tower
  20. CREATE INDEX ON broadcasting_towers using gist (geom);
  21. -- Create geometry index that will check proximity limit of tower to user
  22. CREATE INDEX ON broadcasting_towers using gist (ST_Expand(geom, sending_range));
  23. -- Query towers that 4-kilometer receiver in Minsk Hackerspace can get
  24. -- Note: two conditions, because shorter LEAST(b.sending_range, 4000) will not use index.
  25. SELECT b.tower_id, b.geom
  26. FROM broadcasting_towers b
  27. WHERE ST_DWithin(b.geom, 'SRID=3857;POINT(3072163.4 7159374.1)', 4000)
  28. AND ST_DWithin(b.geom, 'SRID=3857;POINT(3072163.4 7159374.1)', b.sending_range);

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
  4. -----------
  5. t
  6. (1 row)
  7. SELECT ST_Equals(ST_Reverse(ST_GeomFromText('LINESTRING(0 0, 10 10)')),
  8. ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
  9. st_equals
  10. -----------
  11. t
  12. (1 row)

ST_GeometricMedian

描述

返回给定几何对象的几何中位点。

函数声明

  1. geometry ST_GeometricMedian ( geometry g , float8 tolerance , int max_iter , boolean fail_if_not_converged );

使用示例

  1. WITH test AS (
  2. SELECT 'MULTIPOINT((0 0), (1 1), (2 2), (200 200))'::geometry geom)
  3. SELECT
  4. ST_AsText(ST_Centroid(geom)) centroid,
  5. ST_AsText(ST_GeometricMedian(geom)) median
  6. FROM test;
  7. centroid | median
  8. --------------------+----------------------------------------
  9. POINT(50.75 50.75) | POINT(1.9761550281255 1.9761550281255)
  10. (1 row)

ST_HasArc

描述

如果给定几何对象或几何对象的集合包含有环,则返回true。

函数声明

  1. boolean ST_HasArc(geometry geomA);

使用示例

  1. SELECT ST_HasArc(ST_Collect('LINESTRING(1 2, 3 4, 5 6)', 'CIRCULARSTRING(1 1, 2 3, 4 5, 6 7, 5 6)'));
  2. st_hasarc
  3. --------
  4. t

ST_Intersects

描述

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

函数声明

  1. boolean ST_Intersects( geometry geomA , geometry geomB );
  2. boolean ST_Intersects( geography geogA , geography geogB );

使用示例

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

ST_Length

描述

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

函数声明

  1. float ST_Length(geometry a_2dlinestring);
  2. float ST_Length(geography geog, boolean use_spheroid=true);

使用示例

  1. SELECT ST_Length(ST_GeomFromText('LINESTRING(743238 2967416,743238 2967450,743265 2967450,
  2. 743265.625 2967416,743238 2967416)',2249));
  3. st_length
  4. ---------
  5. 122.630744000095
  6. --Transforming WGS 84 LineString to Massachusetts state plane meters
  7. SELECT ST_Length(
  8. ST_Transform(
  9. ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)'),
  10. 26986
  11. )
  12. );
  13. st_length
  14. ---------
  15. 34309.4563576191

ST_Length2D

描述

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

函数声明

  1. float ST_Length2D(geometry a_2dlinestring);

使用示例

  1. None

ST_3DLength

描述

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

函数声明

  1. float ST_3DLength(geometry a_3dlinestring);

使用示例

  1. SELECT ST_3DLength(ST_GeomFromText('LINESTRING(743238 2967416 1,743238 2967450 1,743265 2967450 3,
  2. 743265.625 2967416 3,743238 2967416 3)',2249));
  3. ST_3DLength
  4. -----------
  5. 122.704716741457

ST_LengthSpheroid

描述

计算给定几何对象的2维或3维椭球长度。

函数声明

  1. float ST_LengthSpheroid(geometry a_geometry, spheroid a_spheroid);

使用示例

  1. SELECT ST_LengthSpheroid( geometry_column,
  2. 'SPHEROID["GRS_1980",6378137,298.257222101]' )
  3. FROM geometry_table;
  4. SELECT ST_LengthSpheroid( the_geom, sph_m ) As tot_len,
  5. ST_LengthSpheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
  6. ST_LengthSpheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
  7. FROM (SELECT ST_GeomFromText('MULTILINESTRING((-118.584 38.374,-118.583 38.5),
  8. (-71.05957 42.3589 , -71.061 43))') As the_geom,
  9. CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo;
  10. tot_len | len_line1 | len_line2
  11. ------------------+------------------+------------------
  12. 85204.5207562955 | 13986.8725229309 | 71217.6482333646
  13. --3D
  14. SELECT ST_LengthSpheroid( the_geom, sph_m ) As tot_len,
  15. ST_LengthSpheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
  16. ST_LengthSpheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
  17. FROM (SELECT ST_GeomFromEWKT('MULTILINESTRING((-118.584 38.374 20,-118.583 38.5 30),
  18. (-71.05957 42.3589 75, -71.061 43 90))') As the_geom,
  19. CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo;
  20. tot_len | len_line1 | len_line2
  21. ------------------+-----------------+------------------
  22. 85204.5259107402 | 13986.876097711 | 71217.6498130292

ST_Length2D_Spheroid

描述

计算给定几何对象的2维椭球长度。

函数声明

  1. float ST_Length2D_Spheroid(geometry a_geometry, spheroid a_spheroid);

使用示例

  1. SELECT ST_Length2D_Spheroid( geometry_column,
  2. 'SPHEROID["GRS_1980",6378137,298.257222101]' )
  3. FROM geometry_table;
  4. SELECT ST_Length2D_Spheroid( the_geom, sph_m ) As tot_len,
  5. ST_Length2D_Spheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
  6. ST_Length2D_Spheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
  7. FROM (SELECT ST_GeomFromText('MULTILINESTRING((-118.584 38.374,-118.583 38.5),
  8. (-71.05957 42.3589 , -71.061 43))') As the_geom,
  9. CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo;
  10. tot_len | len_line1 | len_line2
  11. ------------------+------------------+------------------
  12. 85204.5207562955 | 13986.8725229309 | 71217.6482333646
  13. --3D Observe same answer
  14. SELECT ST_Length2D_Spheroid( the_geom, sph_m ) As tot_len,
  15. ST_Length2D_Spheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
  16. ST_Length2D_Spheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
  17. FROM (SELECT ST_GeomFromEWKT('MULTILINESTRING((-118.584 38.374 20,-118.583 38.5 30),
  18. (-71.05957 42.3589 75, -71.061 43 90))') As the_geom,
  19. CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m) as foo;
  20. tot_len | len_line1 | len_line2
  21. ------------------+------------------+------------------
  22. 85204.5207562955 | 13986.8725229309 | 71217.6482333646

ST_LongestLine

描述

返回给定的两个2维几何对象的最远距离的线段。

函数声明

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

使用示例

  1. SELECT ST_AsText(
  2. ST_LongestLine('POINT(100 100)'::geometry,
  3. 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry)
  4. ) As lline;
  5. lline
  6. -----------------
  7. LINESTRING(100 100,98 190)

ST_OrderingEquals

描述

如果两个几何对象相同,而且坐标点的顺序也相同,返回true。

函数声明

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

使用示例

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

ST_Perimeter

描述

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

函数声明

  1. float ST_Perimeter(geometry g1);
  2. float ST_Perimeter(geography geog, boolean use_spheroid=true);

使用示例

  1. SELECT ST_Perimeter(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,743265 2967450,
  2. 743265.625 2967416,743238 2967416))', 2249));
  3. st_perimeter
  4. ---------
  5. 122.630744000095
  6. (1 row)
  7. SELECT ST_Perimeter(ST_GeomFromText('MULTIPOLYGON(((763104.471273676 2949418.44119003,
  8. 763104.477769673 2949418.42538203,
  9. 763104.189609677 2949418.22343004,763104.471273676 2949418.44119003)),
  10. ((763104.471273676 2949418.44119003,763095.804579742 2949436.33850239,
  11. 763086.132105649 2949451.46730207,763078.452329651 2949462.11549407,
  12. 763075.354136904 2949466.17407812,763064.362142565 2949477.64291974,
  13. 763059.953961626 2949481.28983009,762994.637609571 2949532.04103014,
  14. 762990.568508415 2949535.06640477,762986.710889563 2949539.61421415,
  15. 763117.237897679 2949709.50493431,763235.236617789 2949617.95619822,
  16. 763287.718121842 2949562.20592617,763111.553321674 2949423.91664605,
  17. 763104.471273676 2949418.44119003)))', 2249));
  18. st_perimeter
  19. ---------
  20. 845.227713366825
  21. (1 row)

ST_Perimeter2D

描述

返回给定几何对象的2维周长。目前与ST_Perimeter相同。

函数声明

  1. float ST_Perimeter2D(geometry geomA);

使用示例

  1. None

ST_3DPerimeter

描述

返回给定几何对象的3维周长。

函数声明

  1. float ST_3DPerimeter(geometry geomA);

使用示例

  1. SELECT ST_3DPerimeter(the_geom), ST_Perimeter2d(the_geom), ST_Perimeter(the_geom) FROM
  2. (SELECT ST_GeomFromEWKT('SRID=2249;POLYGON((743238 2967416 2,743238 2967450 1,
  3. 743265.625 2967416 1,743238 2967416 2))') As the_geom) As foo;
  4. ST_3DPerimeter | st_perimeter2d | st_perimeter
  5. ------------------+------------------+------------------
  6. 105.465793597674 | 105.432997272188 | 105.432997272188

ST_PointOnSurface

描述

返回与给定几何对象的表面相交的一个坐标点。

函数声明

  1. geometry ST_PointOnSurface(geometry g1);

使用示例

  1. SELECT ST_AsText(ST_PointOnSurface('POINT(0 5)'::geometry));
  2. st_astext
  3. ------------
  4. POINT(0 5)
  5. (1 row)
  6. SELECT ST_AsText(ST_PointOnSurface('LINESTRING(0 5, 0 10)'::geometry));
  7. st_astext
  8. ------------
  9. POINT(0 5)
  10. (1 row)
  11. SELECT ST_AsText(ST_PointOnSurface('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'::geometry));
  12. st_astext
  13. ----------------
  14. POINT(2.5 2.5)
  15. (1 row)
  16. SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 10 2)')));
  17. st_asewkt
  18. ----------------
  19. POINT(0 0 1)
  20. (1 row)

ST_Project

描述

将一个地理坐标点按给定的距离(以米为单位)和角度(北方为0度,东方为90度,南方为180度,西方是270度)做投影变换。

函数声明

  1. geography ST_Project(geography g1, float distance, float azimuth);

使用示例

  1. SELECT ST_AsText(ST_Project('POINT(0 0)'::geography, 100000, radians(45.0)));
  2. st_astext
  3. --------------------------------------------
  4. POINT(0.635231029125537 0.639472334729198)
  5. (1 row)

ST_Relate

描述

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

函数声明

  1. boolean ST_Relate(geometry geomA, geometry geomB, text intersectionMatrixPattern);
  2. text ST_Relate(geometry geomA, geometry geomB);
  3. text ST_Relate(geometry geomA, geometry geomB, integer BoundaryNodeRule);

使用示例

  1. --Find all compounds that intersect and not touch a poly (interior intersects)
  2. SELECT l.* , b.name As poly_name
  3. FROM polys As b
  4. INNER JOIN compounds As l
  5. ON (p.the_geom && b.the_geom
  6. AND ST_Relate(l.the_geom, b.the_geom,'T********'));
  7. SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2));
  8. st_relate
  9. -----------
  10. 0FFFFF212
  11. SELECT ST_Relate(ST_GeometryFromText('LINESTRING(1 2, 3 4)'), ST_GeometryFromText('LINESTRING(5 6, 7 8)'));
  12. st_relate
  13. -----------
  14. FF1FF0102
  15. SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
  16. st_relate
  17. -----------
  18. t
  19. SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '*FF*FF212');
  20. st_relate
  21. -----------
  22. t

ST_RelateMatch

描述

返回给定的相交矩阵是否满足给定的相交模式。

函数声明

  1. boolean ST_RelateMatch(text intersectionMatrix, text intersectionMatrixPattern);

使用示例

  1. SELECT ST_RelateMatch('101202FFF', 'TTTTTTFFF') ;
  2. -- result --
  3. t
  4. --example of common intersection matrix patterns and example matrices
  5. -- comparing relationships of involving one invalid geometry and ( a line and polygon that intersect at interior and boundary)
  6. SELECT mat.name, pat.name, ST_RelateMatch(mat.val, pat.val) As satisfied
  7. FROM
  8. ( VALUES ('Equality', 'T1FF1FFF1'),
  9. ('Overlaps', 'T*T***T**'),
  10. ('Within', 'T*F**F***'),
  11. ('Disjoint', 'FF*FF****') As pat(name,val)
  12. CROSS JOIN
  13. ( VALUES ('Self intersections (invalid)', '111111111'),
  14. ('IE2_BI1_BB0_BE1_EI1_EE2', 'FF2101102'),
  15. ('IB1_IE1_BB0_BE0_EI2_EI1_EE2', 'F11F00212')
  16. ) As mat(name,val);

ST_ShortestLine

描述

返回两个几何对象间的2维最短线段。

函数声明

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

使用示例

  1. SELECT ST_AsText(
  2. ST_ShortestLine('POINT(100 100)'::geometry,
  3. 'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry)
  4. ) As sline;
  5. sline
  6. -----------------
  7. LINESTRING(100 100,73.0769230769231 115.384615384615)

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
  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
  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)