空间对象关系函数

ST_3DClosestPoint

描述

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

函数声明

geometry ST_3DClosestPoint(geometry g1, geometry g2);

使用示例

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

ST_3DDistance

描述

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

函数声明

float ST_3DDistance(geometry g1, geometry g2);

使用示例

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

ST_3DDWithin

描述

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

函数声明

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

使用示例

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

ST_3DDFullyWithin

描述

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

函数声明

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

使用示例

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

ST_3DIntersects

描述

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

函数声明

boolean ST_3DIntersects(geometry geomA, geometry geomB);

使用示例

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

ST_3DLongestLine

描述

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

函数声明

geometry ST_3DLongestLine(geometry g1, geometry g2);

使用示例

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

ST_3DMaxDistance

描述

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

函数声明

float ST_3DMaxDistance(geometry g1, geometry g2);

使用示例

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

     dist_3d      |     dist_2d
------------------+------------------
 24383.7467488441 | 22247.8472107251

ST_3DShortestLine

描述

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

函数声明

geometry ST_3DShortestLine(geometry g1, geometry g2);

使用示例

SELECT ST_AsEWKT(ST_3DShortestLine(line,pt)) AS shl3d_line_pt,
        ST_AsEWKT(ST_ShortestLine(line,pt)) As shl2d_line_pt
    FROM (SELECT 'POINT(100 100 30)'::geometry As pt,
            'LINESTRING (20 80 20, 98 190 1, 110 180 3, 50 75 1000)'::geometry As line
        ) As foo;

 shl3d_line_pt                                         |               shl2d_line_pt
----------------------------------------------------------------------------+------------------------------------------------------
 LINESTRING(54.6993798867619 128.935022917228 11.5475869506606,100 100 30)  | LINESTRING(73.0769230769231 115.384615384615,100 100)

ST_Area

描述

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

函数声明

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

使用示例

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

ST_Azimuth

描述

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

函数声明

float ST_Azimuth(geometry pointA, geometry pointB);
float ST_Azimuth(geography pointA, geography pointB);

使用示例

SELECT degrees(ST_Azimuth(ST_Point(25, 45), ST_Point(75, 100))) AS degA_B,
        degrees(ST_Azimuth(ST_Point(75, 100), ST_Point(25, 45))) AS degB_A;
      dega_b       |     degb_a
------------------+------------------
 42.2736890060937 | 222.273689006094

ST_Angle

描述

返回三个坐标点，或两个向量（四个坐标点或两条线）之间的夹角。

函数声明

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

使用示例

WITH rand AS (
    SELECT s, random() * 2 * PI() AS rad1
        , random() * 2 * PI() AS rad2
    FROM  generate_series(1,2,2) AS s
)
 , points AS (
    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
    FROM rand
        ,cos(rad1) cos1, sin(rad1) sin1
        ,cos(rad2) cos2, sin(rad2) sin2
)
SELECT s, ST_AsText(ST_SnapToGrid(ST_MakeLine(ARRAY[p1,p2,p3]),0.001)) AS line
        , degrees(ST_Angle(p1,p2,p3)) as computed_angle
        , round(degrees(2*PI()-rad2 -2*PI()+rad1+2*PI()))::int%360 AS reference
        , round(degrees(2*PI()-rad2 -2*PI()+rad1+2*PI()))::int%360 AS reference
    FROM points ;

1 | line | computed_angle | reference
------------------+------------------
1 | LINESTRING(1.511 1.86,1 1,0.896 0.005) | 155.27033848688 | 155

ST_Centroid

描述

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

函数声明

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

使用示例

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
------------------------------------------
 POINT(2.30769230769231 3.30769230769231)
(1 row)

SELECT ST_AsText(ST_centroid(g))
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 ;
------------------------------------------
POINT(0.5 1)

SELECT ST_AsText(ST_centroid(g))
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;
------------------------------------------
POINT(0.5 1)

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 100)'::geometry As pt,
        'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry As line
    ) As foo;

   cp_pt_line   |                cp_line_pt
----------------+------------------------------------------
 POINT(100 100) | POINT(73.0769230769231 115.384615384615)

ST_ClusterDBSCAN

描述

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

函数声明

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

使用示例

ST_ClusterIntersecting

描述

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

函数声明

geometry[] ST_ClusterIntersecting(geometry set g);

使用示例

WITH testdata AS
  (SELECT unnest(ARRAY['LINESTRING (0 0, 1 1)'::geometry,
               'LINESTRING (5 5, 4 4)'::geometry,
               'LINESTRING (6 6, 7 7)'::geometry,
               'LINESTRING (0 0, -1 -1)'::geometry,
               'POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0))'::geometry]) AS geom)

SELECT ST_AsText(unnest(ST_ClusterIntersecting(geom))) FROM testdata;

st_astext
---------
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)))
GEOMETRYCOLLECTION(LINESTRING(6 6,7 7))

ST_ClusterKMeans

描述

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

函数声明

integer ST_ClusterKMeans(geometry winset geom, integer number_of_clusters);

使用示例

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

ST_ClusterWithin

描述

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

函数声明

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

使用示例

WITH testdata AS
  (SELECT unnest(ARRAY['LINESTRING (0 0, 1 1)'::geometry,
               'LINESTRING (5 5, 4 4)'::geometry,
               'LINESTRING (6 6, 7 7)'::geometry,
               'LINESTRING (0 0, -1 -1)'::geometry,
               'POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0))'::geometry]) AS geom)

SELECT ST_AsText(unnest(ST_ClusterWithin(geom, 1.4))) FROM testdata;

st_astext
---------
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)))
GEOMETRYCOLLECTION(LINESTRING(6 6,7 7))

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_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
       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 | bigcoversexterior | bigcontainsexterior
------------------+------------------+------------------+------------+-------------------+---------------------
 f                | t                | t                | t          | t        | f

-- Example demonstrating difference between contains and contains properly
SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa, ST_ContainsProperly(geomA, geomA) AS acontainspropa,
   ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba, ST_ContainsProperly(geomA, ST_Boundary(geomA)) As acontainspropba
FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5,1) ),
             ( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),
             ( ST_Point(1,1) )
      ) As foo(geomA);

  geomtype    | acontainsa | acontainspropa | acontainsba | acontainspropba
--------------+------------+----------------+-------------+-----------------
ST_Polygon    | t          | f              | f           | f
ST_LineString | t          | f              | f           | f
ST_Point      | t          | t              | f           | f

ST_ContainsProperly

描述

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

函数声明

boolean ST_ContainsProperly(geometry geomA, geometry geomB);

使用示例

--a circle within a circle
SELECT ST_ContainsProperly(smallc, bigc) As smallcontainspropbig,
    ST_ContainsProperly(bigc,smallc) As bigcontainspropsmall,
    ST_ContainsProperly(bigc, ST_Union(smallc, bigc)) as bigcontainspropunion,
    ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
    ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
    ST_ContainsProperly(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;

  smallcontainspropbig | bigcontainspropsmall | bigcontainspropunion | bigisunion | bigcoversexterior | bigcontainsexterior
------------------+------------------+------------------+------------+-------------------+---------------------
 f                     | t                    | f                    | t          | t                 | f

 --example demonstrating difference between contains and contains properly
 SELECT ST_GeometryType(geomA) As geomtype, ST_Contains(geomA,geomA) AS acontainsa, ST_ContainsProperly(geomA, geomA) AS acontainspropa,
 ST_Contains(geomA, ST_Boundary(geomA)) As acontainsba, ST_ContainsProperly(geomA, ST_Boundary(geomA)) As acontainspropba
 FROM (VALUES ( ST_Buffer(ST_Point(1,1), 5,1) ),
          ( ST_MakeLine(ST_Point(1,1), ST_Point(-1,-1) ) ),
          ( ST_Point(1,1) )
    ) As foo(geomA);

  geomtype    | acontainsa | acontainspropa | acontainsba | acontainspropba
--------------+------------+----------------+-------------+-----------------
ST_Polygon    | t          | f              | f           | f
ST_LineString | t          | f              | f           | f
ST_Point      | t          | t              | f           | f

ST_Covers

描述

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

函数声明

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

使用示例

--a circle covering a circle
SELECT ST_Covers(smallc,smallc) As smallinsmall,
    ST_Covers(smallc, bigc) As smallcoversbig,
    ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
    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;

 smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
--------------+----------------+-------------------+---------------------
 t            | f              | t                 | f
(1 row)

ST_CoveredBy

描述

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

函数声明

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

使用示例

--a circle coveredby a circle
SELECT ST_CoveredBy(smallc,smallc) As smallinsmall,
    ST_CoveredBy(smallc, bigc) As smallcoveredbybig,
    ST_CoveredBy(ST_ExteriorRing(bigc), bigc) As exteriorcoveredbybig,
    ST_Within(ST_ExteriorRing(bigc),bigc) As exeriorwithinbig
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
    ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;

 smallinsmall | smallcoveredbybig | exteriorcoveredbybig | exeriorwithinbig
--------------+-------------------+----------------------+------------------
 t            | t                 | t                    | f
(1 row)

ST_Crosses

描述

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

函数声明

boolean ST_Crosses(geometry g1, geometry g2);

使用示例

CREATE TABLE roads (
  id serial NOT NULL,
  the_geom geometry,
  CONSTRAINT roads_pkey PRIMARY KEY (road_id)
);

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

函数声明

integer ST_LineCrossingDirection(geometry  linestringA, geometry  linestringB);

使用示例

SELECT ST_LineCrossingDirection(foo.line1, foo.line2) As l1_cross_l2 ,
      ST_LineCrossingDirection(foo.line2, foo.line1) As l2_cross_l1
FROM (
SELECT
 ST_GeomFromText('LINESTRING(25 169,89 114,40 70,86 43)') As line1,
 ST_GeomFromText('LINESTRING(171 154,20 140,71 74,161 53)') As line2
    ) As foo;

 l1_cross_l2 | l2_cross_l1
-------------+-------------
           3 |          -3

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
---------------
 t
(1 row)
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
 st_disjoint
---------------
 f
(1 row)

ST_Distance

描述

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

函数声明

float ST_Distance(geometry g1, geometry g2);
float ST_Distance(geography gg1, geography gg2);
float ST_Distance(geography gg1, geography gg2, boolean use_spheroid);

使用示例

--Geometry example - units in planar degrees 4326 is WGS 84 long lat unit=degrees
SELECT ST_Distance(
        'SRID=4326;POINT(-72.1235 42.3521)'::geometry,
        'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry
    );
st_distance
-----------------
0.00150567726382282

-- Geometry example - units in meters (SRID: 3857, proportional to pixels on popular web maps)
-- although the value is off, nearby ones can be compared correctly,
-- which makes it a good choice for algorithms like KNN or KMeans.
SELECT ST_Distance(
            ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
            ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)
        );
st_distance
-----------------
167.441410065196

-- Geometry example - units in meters (SRID: 3857 as above, but corrected by cos(lat) to account for distortion)
SELECT ST_Distance(
            ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
            ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)
        ) * cosd(42.3521);
st_distance
-----------------
123.742351254151

-- Geometry example - units in meters (SRID: 26986 Massachusetts state plane meters) (most accurate for Massachusetts)
SELECT ST_Distance(
            ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 26986),
            ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 26986)
        );
st_distance
-----------------
123.797937878454

-- Geometry example - units in meters (SRID: 2163 US National Atlas Equal area) (least accurate)
SELECT ST_Distance(
            ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 2163),
            ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 2163)
        );
st_distance
------------------
126.664256056812

ST_MinimumClearance

描述

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

函数声明

float ST_MinimumClearance(geometry g);

使用示例

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

ST_MinimumClearanceLine

描述

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

函数声明

Geometry ST_MinimumClearanceLine(geometry g);

使用示例

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

ST_HausdorffDistance

描述

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

函数声明

float ST_HausdorffDistance(geometry g1, geometry g2);
float ST_HausdorffDistance(geometry g1, geometry g2, float densifyFrac);

使用示例

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

ST_FrechetDistance

描述

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

函数声明

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

使用示例

SELECT st_frechetdistance('LINESTRING (0 0, 100 0)'::geometry, 'LINESTRING (0 0, 50 50, 100 0)'::geometry);
 st_frechetdistance
--------------------
   70.7106781186548
(1 row)

ST_MaxDistance

描述

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

函数声明

float ST_MaxDistance(geometry  g1, geometry  g2);

使用示例

SELECT ST_MaxDistance('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
   st_maxdistance
-----------------
 2
(1 row)

SELECT ST_MaxDistance('POINT(0 0)'::geometry, 'LINESTRING ( 2 2, 2 2 )'::geometry);
  st_maxdistance
------------------
 2.82842712474619
(1 row)

ST_DistanceSphere

描述

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

函数声明

float ST_DistanceSphere(geometry  geomlonlatA, geometry  geomlonlatB);

使用示例

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

ST_DistanceSpheroid

描述

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

函数声明

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

使用示例

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

ST_DFullyWithin

描述

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

函数声明

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

使用示例

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
        (select ST_GeomFromText('POINT(1 1)') as geom_a,ST_GeomFromText('LINESTRING(1 5, 2 7, 1 9, 14 12)') as geom_b) t1;

-----------------
 DFullyWithin10 | DWithin10 | DFullyWithin20 |
---------------+----------+---------------+
 f             | t        | t             |

ST_DWithin

描述

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

函数声明

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

使用示例

-- Find the nearest hospital to each school
-- that is within 3000 units of the school.
--  We do an ST_DWithin search to utilize indexes to limit our search list
--  that the non-indexable ST_Distance needs to process
-- If the units of the spatial reference is meters then units would be meters
SELECT DISTINCT ON (s.gid) s.gid, s.school_name, s.geom, h.hospital_name
    FROM schools s
        LEFT JOIN hospitals h ON ST_DWithin(s.the_geom, h.geom, 3000)
    ORDER BY s.gid, ST_Distance(s.geom, h.geom);

-- The schools with no close hospitals
-- Find all schools with no hospital within 3000 units
-- away from the school.  Units is in units of spatial ref (e.g. meters, feet, degrees)
SELECT s.gid, s.school_name
    FROM schools s
        LEFT JOIN hospitals h ON ST_DWithin(s.geom, h.geom, 3000)
    WHERE h.gid IS NULL;

-- Find broadcasting towers that receiver with limited range can receive.
-- Data is geometry in Spherical Mercator (SRID=3857), ranges are approximate.

-- Create geometry index that will check proximity limit of user to tower
CREATE INDEX ON broadcasting_towers using gist (geom);

-- Create geometry index that will check proximity limit of tower to user
CREATE INDEX ON broadcasting_towers using gist (ST_Expand(geom, sending_range));

-- Query towers that 4-kilometer receiver in Minsk Hackerspace can get
-- Note: two conditions, because shorter LEAST(b.sending_range, 4000) will not use index.
SELECT b.tower_id, b.geom
  FROM broadcasting_towers b
  WHERE ST_DWithin(b.geom, 'SRID=3857;POINT(3072163.4 7159374.1)', 4000)
      AND ST_DWithin(b.geom, 'SRID=3857;POINT(3072163.4 7159374.1)', b.sending_range);

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
-----------
 t
(1 row)

SELECT ST_Equals(ST_Reverse(ST_GeomFromText('LINESTRING(0 0, 10 10)')),
        ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
 st_equals
-----------
 t
(1 row)

ST_GeometricMedian

描述

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

函数声明

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

使用示例

WITH test AS (
SELECT 'MULTIPOINT((0 0), (1 1), (2 2), (200 200))'::geometry geom)
SELECT
  ST_AsText(ST_Centroid(geom)) centroid,
  ST_AsText(ST_GeometricMedian(geom)) median
FROM test;
      centroid      |                 median
--------------------+----------------------------------------
 POINT(50.75 50.75) | POINT(1.9761550281255 1.9761550281255)
(1 row)

ST_HasArc

描述

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

函数声明

boolean ST_HasArc(geometry  geomA);

使用示例

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

ST_Intersects

描述

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

函数声明

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

使用示例

SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
 st_intersects
---------------
 f
(1 row)

SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
 st_intersects
---------------
 t
(1 row)

ST_Length

描述

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

函数声明

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

使用示例

SELECT ST_Length(ST_GeomFromText('LINESTRING(743238 2967416,743238 2967450,743265 2967450,
743265.625 2967416,743238 2967416)',2249));
st_length
---------
 122.630744000095

--Transforming WGS 84 LineString to Massachusetts state plane meters
SELECT ST_Length(
    ST_Transform(
        ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)'),
        26986
    )
);
st_length
---------
34309.4563576191

ST_Length2D

描述

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

函数声明

float ST_Length2D(geometry  a_2dlinestring);

使用示例

None

ST_3DLength

描述

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

函数声明

float ST_3DLength(geometry  a_3dlinestring);

使用示例

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

ST_LengthSpheroid

描述

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

函数声明

float ST_LengthSpheroid(geometry  a_geometry, spheroid  a_spheroid);

使用示例

SELECT ST_LengthSpheroid( geometry_column,
              'SPHEROID["GRS_1980",6378137,298.257222101]' )
              FROM geometry_table;

SELECT ST_LengthSpheroid( the_geom, sph_m ) As tot_len,
ST_LengthSpheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
ST_LengthSpheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
              FROM (SELECT ST_GeomFromText('MULTILINESTRING((-118.584 38.374,-118.583 38.5),
    (-71.05957 42.3589 , -71.061 43))') As the_geom,
CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m)  as foo;
    tot_len      |    len_line1     |    len_line2
------------------+------------------+------------------
 85204.5207562955 | 13986.8725229309 | 71217.6482333646

 --3D
SELECT ST_LengthSpheroid( the_geom, sph_m ) As tot_len,
ST_LengthSpheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
ST_LengthSpheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
              FROM (SELECT ST_GeomFromEWKT('MULTILINESTRING((-118.584 38.374 20,-118.583 38.5 30),
    (-71.05957 42.3589 75, -71.061 43 90))') As the_geom,
CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m)  as foo;
     tot_len      |    len_line1    |    len_line2
------------------+-----------------+------------------
 85204.5259107402 | 13986.876097711 | 71217.6498130292

ST_Length2D_Spheroid

描述

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

函数声明

float ST_Length2D_Spheroid(geometry  a_geometry, spheroid  a_spheroid);

使用示例

SELECT ST_Length2D_Spheroid( geometry_column,
              'SPHEROID["GRS_1980",6378137,298.257222101]' )
              FROM geometry_table;

SELECT ST_Length2D_Spheroid( the_geom, sph_m ) As tot_len,
ST_Length2D_Spheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
ST_Length2D_Spheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
              FROM (SELECT ST_GeomFromText('MULTILINESTRING((-118.584 38.374,-118.583 38.5),
    (-71.05957 42.3589 , -71.061 43))') As the_geom,
CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m)  as foo;
    tot_len      |    len_line1     |    len_line2
------------------+------------------+------------------
 85204.5207562955 | 13986.8725229309 | 71217.6482333646

 --3D Observe same answer
SELECT ST_Length2D_Spheroid( the_geom, sph_m ) As tot_len,
ST_Length2D_Spheroid(ST_GeometryN(the_geom,1), sph_m) As len_line1,
ST_Length2D_Spheroid(ST_GeometryN(the_geom,2), sph_m) As len_line2
              FROM (SELECT ST_GeomFromEWKT('MULTILINESTRING((-118.584 38.374 20,-118.583 38.5 30),
    (-71.05957 42.3589 75, -71.061 43 90))') As the_geom,
CAST('SPHEROID["GRS_1980",6378137,298.257222101]' As spheroid) As sph_m)  as foo;
    tot_len      |    len_line1     |    len_line2
------------------+------------------+------------------
 85204.5207562955 | 13986.8725229309 | 71217.6482333646

ST_LongestLine

描述

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

函数声明

geometry ST_LongestLine(geometry g1, geometry g2);

使用示例

SELECT ST_AsText(
    ST_LongestLine('POINT(100 100)'::geometry,
        'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry)
    ) As lline;
   lline
-----------------
LINESTRING(100 100,98 190)

ST_OrderingEquals

描述

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

函数声明

boolean ST_OrderingEquals(geometry  A, geometry  B);

使用示例

SELECT ST_OrderingEquals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
        ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
 st_orderingequals
-----------
 f
(1 row)

SELECT ST_OrderingEquals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
        ST_GeomFromText('LINESTRING(0 0, 0 0, 10 10)'));
 st_orderingequals
-----------
 t
(1 row)

SELECT ST_OrderingEquals(ST_Reverse(ST_GeomFromText('LINESTRING(0 0, 10 10)')),
        ST_GeomFromText('LINESTRING(0 0, 0 0, 10 10)'));
 st_orderingequals
-----------
 f
(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)
    As foo;
a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
------------+-------------+----------------+--------------
f           | f           | t              | t

--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);
float ST_Perimeter(geography geog, boolean use_spheroid=true);

使用示例

SELECT ST_Perimeter(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,743265 2967450,
743265.625 2967416,743238 2967416))', 2249));
st_perimeter
---------
 122.630744000095
(1 row)

SELECT ST_Perimeter(ST_GeomFromText('MULTIPOLYGON(((763104.471273676 2949418.44119003,
763104.477769673 2949418.42538203,
763104.189609677 2949418.22343004,763104.471273676 2949418.44119003)),
((763104.471273676 2949418.44119003,763095.804579742 2949436.33850239,
763086.132105649 2949451.46730207,763078.452329651 2949462.11549407,
763075.354136904 2949466.17407812,763064.362142565 2949477.64291974,
763059.953961626 2949481.28983009,762994.637609571 2949532.04103014,
762990.568508415 2949535.06640477,762986.710889563 2949539.61421415,
763117.237897679 2949709.50493431,763235.236617789 2949617.95619822,
763287.718121842 2949562.20592617,763111.553321674 2949423.91664605,
763104.471273676 2949418.44119003)))', 2249));
st_perimeter
---------
 845.227713366825
(1 row)

ST_Perimeter2D

描述

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

函数声明

float ST_Perimeter2D(geometry  geomA);

使用示例

None

ST_3DPerimeter

描述

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

函数声明

float ST_3DPerimeter(geometry  geomA);

使用示例

SELECT ST_3DPerimeter(the_geom), ST_Perimeter2d(the_geom), ST_Perimeter(the_geom) FROM
            (SELECT ST_GeomFromEWKT('SRID=2249;POLYGON((743238 2967416 2,743238 2967450 1,
743265.625 2967416 1,743238 2967416 2))') As the_geom) As foo;

  ST_3DPerimeter  |  st_perimeter2d  |   st_perimeter
------------------+------------------+------------------
 105.465793597674 | 105.432997272188 | 105.432997272188

ST_PointOnSurface

描述

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

函数声明

geometry ST_PointOnSurface(geometry g1);

使用示例

SELECT ST_AsText(ST_PointOnSurface('POINT(0 5)'::geometry));
 st_astext
------------
 POINT(0 5)
(1 row)

SELECT ST_AsText(ST_PointOnSurface('LINESTRING(0 5, 0 10)'::geometry));
 st_astext
------------
 POINT(0 5)
(1 row)

SELECT ST_AsText(ST_PointOnSurface('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'::geometry));
   st_astext
----------------
 POINT(2.5 2.5)
(1 row)

SELECT ST_AsEWKT(ST_PointOnSurface(ST_GeomFromEWKT('LINESTRING(0 5 1, 0 0 1, 0 10 2)')));
   st_asewkt
----------------
 POINT(0 0 1)
(1 row)

ST_Project

描述

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

函数声明

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

使用示例

SELECT ST_AsText(ST_Project('POINT(0 0)'::geography, 100000, radians(45.0)));

                 st_astext
--------------------------------------------
 POINT(0.635231029125537 0.639472334729198)
(1 row)

ST_Relate

描述

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

函数声明

boolean ST_Relate(geometry  geomA, geometry  geomB, text  intersectionMatrixPattern);
text ST_Relate(geometry  geomA, geometry  geomB);
text ST_Relate(geometry  geomA, geometry  geomB, integer  BoundaryNodeRule);

使用示例

--Find all compounds that intersect and not touch a poly (interior intersects)
SELECT l.* , b.name As poly_name
    FROM polys As b
INNER JOIN compounds As l
ON (p.the_geom && b.the_geom
AND ST_Relate(l.the_geom, b.the_geom,'T********'));

SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2));
st_relate
-----------
0FFFFF212

SELECT ST_Relate(ST_GeometryFromText('LINESTRING(1 2, 3 4)'), ST_GeometryFromText('LINESTRING(5 6, 7 8)'));
st_relate
-----------
FF1FF0102

SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
st_relate
-----------
t

SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '*FF*FF212');
st_relate
-----------
t

ST_RelateMatch

描述

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

函数声明

boolean ST_RelateMatch(text  intersectionMatrix, text  intersectionMatrixPattern);

使用示例

SELECT ST_RelateMatch('101202FFF', 'TTTTTTFFF') ;
-- result --
t
--example of common intersection matrix patterns and example matrices
-- comparing relationships of involving one invalid geometry and ( a line and polygon that intersect at interior and boundary)
SELECT mat.name, pat.name, ST_RelateMatch(mat.val, pat.val) As satisfied
    FROM
        ( VALUES ('Equality', 'T1FF1FFF1'),
                ('Overlaps', 'T*T***T**'),
                ('Within', 'T*F**F***'),
                ('Disjoint', 'FF*FF****') As pat(name,val)
        CROSS JOIN
            (    VALUES ('Self intersections (invalid)', '111111111'),
                    ('IE2_BI1_BB0_BE1_EI1_EE2', 'FF2101102'),
                    ('IB1_IE1_BB0_BE0_EI2_EI1_EE2', 'F11F00212')
            ) As mat(name,val);

ST_ShortestLine

描述

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

函数声明

geometry ST_ShortestLine(geometry g1, geometry g2);

使用示例

SELECT ST_AsText(
    ST_ShortestLine('POINT(100 100)'::geometry,
        'LINESTRING (20 80, 98 190, 110 180, 50 75 )'::geometry)
    ) As sline;
   sline
-----------------
LINESTRING(100 100,73.0769230769231 115.384615384615)

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
------------
 f
(1 row)

SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);
 st_touches
------------
 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)