文档

空间对象处理函数

更新时间:
一键部署

ST_Buffer

描述

对于给定的几何对象或地理对象,返回一个覆盖了所有的到它的距离小于给定值的坐标点的相应几何对象或地理对象。

函数声明

geometry ST_Buffer(geometry  g1, float  radius_of_buffer);
geometry ST_Buffer(geometry  g1, float  radius_of_buffer, integer  num_seg_quarter_circle);
geometry ST_Buffer(geometry  g1, float  radius_of_buffer, text  buffer_style_parameters);
geography ST_Buffer(geography  g1, float  radius_of_buffer_in_meters);
geography ST_Buffer(geography  g1, float  radius_of_buffer, integer  num_seg_quarter_circle);
geography ST_Buffer(geography  g1, float  radius_of_buffer, text  buffer_style_parameters);

使用示例

SELECT ST_Buffer(
 ST_GeomFromText('POINT(100 90)'),
 50, 'quad_segs=8');

ST_BuildArea

描述

返回由传入的几何对象围成的几何对象。

函数声明

geometry ST_BuildArea(geometry  A);

使用示例

SELECT ST_BuildArea(ST_Collect(smallc,bigc))
FROM (SELECT
    ST_Buffer(
      ST_GeomFromText('POINT(100 90)'), 25) As smallc,
    ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As bigc) As foo;

ST_ClipByBox2D

描述

返回给定的几何对象之中落在给定的2维框中的部分。

函数声明

geometry ST_ClipByBox2D(geometry geom, box2d box);

使用示例

-- Rely on implicit cast from geometry to box2d for the second parameter
SELECT ST_ClipByBox2D(the_geom, ST_MakeEnvelope(0,0,10,10)) FROM mytab;

ST_Collect

描述

返回由若干个输入几何对象组合构成的一个几何对象的集合:Multi* 或 GeometryCollection。

本函数也可以当做聚合函数来使用。

函数声明

geometry ST_Collect(geometry set g1field);
geometry ST_Collect(geometry g1, geometry g2);
geometry ST_Collect(geometry[] g1_array);

使用示例

SELECT stusps, ST_Collect(f.the_geom) as singlegeom
     FROM (SELECT stusps, (ST_Dump(the_geom)).geom As the_geom
                FROM
                somestatetable ) As f
GROUP BY stusps

ST_ConcaveHull

描述

返回给定几何对象的凹包。

函数声明

geometry ST_ConcaveHull(geometry  geomA, float  target_percent, boolean  allow_holes=false);

使用示例

--Get estimate of infected area based on point observations
SELECT d.disease_type,
    ST_ConcaveHull(ST_Collect(d.pnt_geom), 0.99) As geom
    FROM disease_obs As d
    GROUP BY d.disease_type;

ST_ConvexHull

描述

返回给定几何对象的最小凸包。

函数声明

geometry ST_ConvexHull(geometry  geomA);

使用示例

--Get estimate of infected area based on point observations
SELECT d.disease_type,
    ST_ConvexHull(ST_Collect(d.the_geom)) As the_geom
    FROM disease_obs As d
    GROUP BY d.disease_type;

ST_CurveToLine

描述

将一个给定的 CIRCULARSTRING/CURVEPOLYGON/MULTISURFACE 几何对象转换为对应的 LINESTRING/POLYGON/MULTIPOLYGON 类型。

函数声明

geometry ST_CurveToLine(geometry curveGeom, float tolerance, integer tolerance_type, integer flags);

使用示例

SELECT ST_AsText(ST_CurveToLine(ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)')));

--Result --
 LINESTRING(220268 150415,220269.95064912 150416.539364228,220271.823415575 150418.17258804,220273.613787707 150419.895736857,
 220275.317452352 150421.704659462,220276.930305234 150423.594998003,220278.448460847 150425.562198489,
 220279.868261823 150427.60152176,220281.186287736 150429.708054909,220282.399363347 150431.876723113,
 220283.50456625 150434.10230186,220284.499233914 150436.379429536,220285.380970099 150438.702620341,220286.147650624 150441.066277505,
 220286.797428488 150443.464706771,220287.328738321 150445.892130112,220287.740300149 150448.342699654,
 220288.031122486 150450.810511759,220288.200504713 150453.289621251,220288.248038775 150455.77405574,
 220288.173610157 150458.257830005,220287.977398166 150460.734960415,220287.659875492 150463.199479347,
 220287.221807076 150465.64544956,220286.664248262 150468.066978495,220285.988542259 150470.458232479,220285.196316903 150472.81345077,
 220284.289480732 150475.126959442,220283.270218395 150477.39318505,220282.140985384 150479.606668057,
 220280.90450212 150481.762075989,220279.5637474 150483.85421628,220278.12195122 150485.87804878,
 220276.582586992 150487.828697901,220274.949363179 150489.701464356,220273.226214362 150491.491836488,
 220271.417291757 150493.195501133,220269.526953216 150494.808354014,220267.559752731 150496.326509628,
 220265.520429459 150497.746310603,220263.41389631 150499.064336517,220261.245228106 150500.277412127,
 220259.019649359 150501.38261503,220256.742521683 150502.377282695,220254.419330878 150503.259018879,
 220252.055673714 150504.025699404,220249.657244448 150504.675477269,220247.229821107 150505.206787101,
 220244.779251566 150505.61834893,220242.311439461 150505.909171266,220239.832329968 150506.078553494,
 220237.347895479 150506.126087555,220234.864121215 150506.051658938,220232.386990804 150505.855446946,
 220229.922471872 150505.537924272,220227.47650166 150505.099855856,220225.054972724 150504.542297043,
 220222.663718741 150503.86659104,220220.308500449 150503.074365683,
 220217.994991777 150502.167529512,220215.72876617 150501.148267175,
 220213.515283163 150500.019034164,220211.35987523 150498.7825509,
 220209.267734939 150497.441796181,220207.243902439 150496,
 220205.293253319 150494.460635772,220203.420486864 150492.82741196,220201.630114732 150491.104263143,
 220199.926450087 150489.295340538,220198.313597205 150487.405001997,220196.795441592 150485.437801511,
 220195.375640616 150483.39847824,220194.057614703 150481.291945091,220192.844539092 150479.123276887,220191.739336189 150476.89769814,
 220190.744668525 150474.620570464,220189.86293234 150472.297379659,220189.096251815 150469.933722495,
 220188.446473951 150467.535293229,220187.915164118 150465.107869888,220187.50360229 150462.657300346,
 220187.212779953 150460.189488241,220187.043397726 150457.710378749,220186.995863664 150455.22594426,
 220187.070292282 150452.742169995,220187.266504273 150450.265039585,220187.584026947 150447.800520653,
 220188.022095363 150445.35455044,220188.579654177 150442.933021505,220189.25536018 150440.541767521,
 220190.047585536 150438.18654923,220190.954421707 150435.873040558,220191.973684044 150433.60681495,
 220193.102917055 150431.393331943,220194.339400319 150429.237924011,220195.680155039 150427.14578372,220197.12195122 150425.12195122,
 220198.661315447 150423.171302099,220200.29453926 150421.298535644,220202.017688077 150419.508163512,220203.826610682 150417.804498867,
 220205.716949223 150416.191645986,220207.684149708 150414.673490372,220209.72347298 150413.253689397,220211.830006129 150411.935663483,
 220213.998674333 150410.722587873,220216.22425308 150409.61738497,220218.501380756 150408.622717305,220220.824571561 150407.740981121,
 220223.188228725 150406.974300596,220225.586657991 150406.324522731,220227 150406)

--3d example
SELECT ST_AsEWKT(ST_CurveToLine(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)')));
Output
------
 LINESTRING(220268 150415 1,220269.95064912 150416.539364228 1.0181172856673,
 220271.823415575 150418.17258804 1.03623457133459,220273.613787707 150419.895736857 1.05435185700189,....AD INFINITUM ....
    220225.586657991 150406.324522731 1.32611114201132,220227 150406 3)

--use only 2 segments to approximate quarter circle
SELECT ST_AsText(ST_CurveToLine(ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)'),2));
st_astext
------------------------------
 LINESTRING(220268 150415,220287.740300149 150448.342699654,220278.12195122 150485.87804878,
 220244.779251566 150505.61834893,220207.243902439 150496,220187.50360229 150462.657300346,
 220197.12195122 150425.12195122,220227 150406)

-- Ensure approximated line is no further than 20 units away from
-- original curve, and make the result direction-neutral
SELECT ST_AsText(ST_CurveToLine(
 'CIRCULARSTRING(0 0,100 -100,200 0)'::geometry,
    20, -- Tolerance
    1, -- Above is max distance between curve and line
    1  -- Symmetric flag
));
st_astext
-------------------------------------------------------------------------------------------
 LINESTRING(0 0,50 -86.6025403784438,150 -86.6025403784439,200 -1.1331077795296e-13,200 0)

ST_DelaunayTriangles

描述

返回给定几何对象的德罗内三角形剖分生成结果,结果的类型可能是:

  • 如果flags=0,是GeometryCollection;

  • 如果flags=1,是MultiLinestring;

  • 如果flags=2,是TIN。

函数声明

geometry ST_DelaunayTriangles(geometry  g1, float  tolerance, int4  flags);

使用示例

-- geometries overlaid multilinestring triangles
SELECT
    ST_DelaunayTriangles(
        ST_Union(ST_GeomFromText('POLYGON((175 150, 20 40,
            50 60, 125 100, 175 150))'),
        ST_Buffer(ST_GeomFromText('POINT(110 170)'), 20)
        ))
     As  dtriag;

ST_Difference

描述

返回一个几何对象,代表给定几何对象A中与几何对象B不相交的部分。

函数声明

geometry ST_Difference(geometry  geomA, geometry  geomB);

使用示例

--Safe for 2d. This is same geometries as what is shown for st_symdifference
SELECT ST_AsText(
    ST_Difference(
            ST_GeomFromText('LINESTRING(50 100, 50 200)'),
            ST_GeomFromText('LINESTRING(50 50, 50 150)')
        )
    );

st_astext
---------
LINESTRING(50 150,50 200)

ST_Dump

描述

对于给定的几何对象,以(geom,path)记录的形式返回其中包含的几何对象,其中geom是被包含的几何对象,path是其在传入对象中的位置。

函数声明

geometry_dump[] ST_Dump(geometry  g1);

使用示例

SELECT sometable.field1, sometable.field1,
      (ST_Dump(sometable.the_geom)).geom AS the_geom
FROM sometable;

-- Break a compound curve into its constituent linestrings and circularstrings
SELECT ST_AsEWKT(a.geom), ST_HasArc(a.geom)
  FROM ( SELECT (ST_Dump(p_geom)).geom AS geom
         FROM (SELECT ST_GeomFromEWKT('COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))') AS p_geom) AS b
        ) AS a;
          st_asewkt          | st_hasarc
-----------------------------+----------
 CIRCULARSTRING(0 0,1 1,1 0) | t
 LINESTRING(1 0,0 1)         | f
(2 rows)

ST_DumpPoints

描述

以(geom,path)记录的形式返回给定几何对象中包含的所有的坐标点。

函数声明

geometry_dump[] ST_DumpPoints(geometry geom);

使用示例

SELECT edge_id, (dp).path[1] As index, ST_AsText((dp).geom) As wktnode
FROM (SELECT 1 As edge_id
    , ST_DumpPoints(ST_GeomFromText('LINESTRING(1 2, 3 4, 10 10)')) AS dp
     UNION ALL
     SELECT 2 As edge_id
    , ST_DumpPoints(ST_GeomFromText('LINESTRING(3 5, 5 6, 9 10)')) AS dp
   ) As foo;
 edge_id | index |    wktnode
---------+-------+--------------
       1 |     1 | POINT(1 2)
       1 |     2 | POINT(3 4)
       1 |     3 | POINT(10 10)
       2 |     1 | POINT(3 5)
       2 |     2 | POINT(5 6)
       2 |     3 | POINT(9 10)

ST_DumpRings

描述

以(geom,path)记录的形式返回给定几何对象中包含的所有的环。其中path中包含了环的序号,0代表外壳,大于0的序号代表了内部环。geom中包含了相应的环,是用Polygon类型来表示的。

函数声明

geometry_dump[] ST_DumpRings(geometry  a_polygon);

使用示例

SELECT sometable.field1, sometable.field1,
      (ST_DumpRings(sometable.the_geom)).geom As the_geom
FROM sometableOfpolys;

SELECT ST_AsEWKT(geom) As the_geom, path
    FROM ST_DumpRings(
        ST_GeomFromEWKT('POLYGON((-8149064 5133092 1,-8149064 5132986 1,-8148996 5132839 1,-8148972 5132767 1,-8148958 5132508 1,-8148941 5132466 1,-8148924 5132394 1,
        -8148903 5132210 1,-8148930 5131967 1,-8148992 5131978 1,-8149237 5132093 1,-8149404 5132211 1,-8149647 5132310 1,-8149757 5132394 1,
        -8150305 5132788 1,-8149064 5133092 1),
        (-8149362 5132394 1,-8149446 5132501 1,-8149548 5132597 1,-8149695 5132675 1,-8149362 5132394 1))')
        )  as foo;
 path |                                            the_geom
----------------------------------------------------------------------------------------------------------------
  {0} | POLYGON((-8149064 5133092 1,-8149064 5132986 1,-8148996 5132839 1,-8148972 5132767 1,-8148958 5132508 1,
      |          -8148941 5132466 1,-8148924 5132394 1,
      |          -8148903 5132210 1,-8148930 5131967 1,
      |          -8148992 5131978 1,-8149237 5132093 1,
      |          -8149404 5132211 1,-8149647 5132310 1,-8149757 5132394 1,-8150305 5132788 1,-8149064 5133092 1))
  {1} | POLYGON((-8149362 5132394 1,-8149446 5132501 1,
      |          -8149548 5132597 1,-8149695 5132675 1,-8149362 5132394 1))

ST_FlipCoordinates

描述

交换给定几何对象的经纬度坐标。

函数声明

geometry ST_FlipCoordinates(geometry geom);

使用示例

SELECT ST_AsEWKT(ST_FlipCoordinates(GeomFromEWKT('POINT(1 2)')));
 st_asewkt
------------
POINT(2 1)

ST_GeneratePoints

描述

将一个给定的Polygon或MultiPolygon几何对象,转换为其内部随机选取的若干个坐标点。

函数声明

geometry ST_GeneratePoints(geometry g, numeric npoints);

使用示例

SELECT ST_GeneratePoints(
    ST_Buffer(
        ST_GeomFromText(
        'LINESTRING(50 50,150 150,150 50)'
        ), 10, 'endcap=round join=round'), 12);

ST_Intersection

描述

返回一个代表了给定几何对象或地理对象A与B的重叠部分的几何对象或地理对象。

函数声明

geometry ST_Intersection( geometry geomA , geometry geomB );
geography ST_Intersection( geography geogA , geography geogB );

使用示例

SELECT ST_AsText(ST_Intersection('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry));
 st_astext
---------------
GEOMETRYCOLLECTION EMPTY
(1 row)
SELECT ST_AsText(ST_Intersection('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry));
 st_astext
---------------
POINT(0 0)
(1 row)

---Clip all lines (trails) by country (here we assume country geom are POLYGON or MULTIPOLYGONS)
-- NOTE: we are only keeping intersections that result in a LINESTRING or MULTILINESTRING because we don't
-- care about trails that just share a point
-- the dump is needed to expand a geometry collection into individual single MULT* parts
-- the below is fairly generic and will work for polys, etc. by just changing the where clause
SELECT clipped.gid, clipped.f_name, clipped_geom
FROM (SELECT trails.gid, trails.f_name, (ST_Dump(ST_Intersection(country.the_geom, trails.the_geom))).geom As clipped_geom
FROM country
    INNER JOIN trails
    ON ST_Intersects(country.the_geom, trails.the_geom))  As clipped
    WHERE ST_Dimension(clipped.clipped_geom) = 1 ;

--For polys e.g. polygon landmarks, you can also use the sometimes faster hack that buffering anything by 0.0
-- except a polygon results in an empty geometry collection
--(so a geometry collection containing polys, lines and points)
-- buffered by 0.0 would only leave the polygons and dissolve the collection shell
SELECT poly.gid,  ST_Multi(ST_Buffer(
                ST_Intersection(country.the_geom, poly.the_geom),
                0.0)
                ) As clipped_geom
FROM country
    INNER JOIN poly
    ON ST_Intersects(country.the_geom, poly.the_geom)
    WHERE Not ST_IsEmpty(ST_Buffer(ST_Intersection(country.the_geom, poly.the_geom),0.0));

ST_LineToCurve

描述

将给定的LINESTRING/POLYGON几何对象转换为对应的CIRCULARSTRING或CURVEPOLYGON对象。

函数声明

geometry ST_LineToCurve(geometry  geomANoncircular);

使用示例

 -- 2D Example
SELECT ST_AsText(ST_LineToCurve(foo.the_geom)) As curvedastext,ST_AsText(foo.the_geom) As non_curvedastext
    FROM (SELECT ST_Buffer('POINT(1 3)'::geometry, 3) As the_geom) As foo;

curvedatext                                                            non_curvedastext
--------------------------------------------------------------------|-----------------------------------------------------------------
CURVEPOLYGON(CIRCULARSTRING(4 3,3.12132034355964 0.878679656440359, | POLYGON((4 3,3.94235584120969 2.41472903395162,3.77163859753386 1.85194970290473,
1 0,-1.12132034355965 5.12132034355963,4 3))                        |  3.49440883690764 1.33328930094119,3.12132034355964 0.878679656440359,
                                                                    |  2.66671069905881 0.505591163092366,2.14805029709527 0.228361402466141,
                                                                    |  1.58527096604839 0.0576441587903094,1 0,
                                                                    |  0.414729033951621 0.0576441587903077,-0.148050297095264 0.228361402466137,
                                                                    |  -0.666710699058802 0.505591163092361,-1.12132034355964 0.878679656440353,
                                                                    |  -1.49440883690763 1.33328930094119,-1.77163859753386 1.85194970290472
                                                                    |  --ETC-- ,3.94235584120969 3.58527096604839,4 3))

--3D example
SELECT ST_AsText(ST_LineToCurve(geom)) As curved, ST_AsText(geom) AS not_curved
FROM (SELECT ST_Translate(ST_Force3D(ST_Boundary(ST_Buffer(ST_Point(1,3), 2,2))),0,0,3) AS geom) AS foo;

                        curved                        |               not_curved
------------------------------------------------------+---------------------------------------------------------------------
 CIRCULARSTRING Z (3 3 3,-1 2.99999999999999 3,3 3 3) | LINESTRING Z (3 3 3,2.4142135623731 1.58578643762691 3,1 1 3,
                                                      |    -0.414213562373092 1.5857864376269 3,-1 2.99999999999999 3,
                                                      | -0.414213562373101 4.41421356237309 3,
                                                      |    0.999999999999991 5 3,2.41421356237309 4.4142135623731 3,3 3 3)
(1 row)

ST_MakeValid

描述

尝试将一个无效的几何对象,在不丢失顶点的前提下,转换为一个有效的几何对象。

函数声明

geometry ST_MakeValid(geometry input);

使用示例

None

ST_MemUnion

描述

功能与ST_Union相同,是其内存友好的版本(使用更少的内存,但需要更多的处理器时间)。

函数声明

geometry ST_MemUnion(geometry set geomfield);

使用示例

参见ST_Union

ST_MinimumBoundingCircle

描述

返回给定几何对象的最小外接圆。默认情况下此圆的四分之一圆弧包括48个线段。

函数声明

geometry ST_MinimumBoundingCircle(geometry  geomA, integer  num_segs_per_qt_circ=48);

使用示例

SELECT d.disease_type,
    ST_MinimumBoundingCircle(ST_Collect(d.the_geom)) As the_geom
    FROM disease_obs As d
    GROUP BY d.disease_type;

ST_MinimumBoundingRadius

描述

返回给定几何对象最小外接圆的圆心坐标和半径。

函数声明

(geometry, double precision) ST_MinimumBoundingRadius(geometry geom);

使用示例

SELECT ST_AsText(center), radius FROM ST_MinimumBoundingRadius('POLYGON((26426 65078,26531 65242,26075 65136,26096 65427,26426 65078))');

                st_astext                 |      radius
------------------------------------------+------------------
 POINT(26284.8418027133 65267.1145090825) | 247.436045591407

ST_OrientedEnvelope

描述

返回给定几何对象的最小外接框。

函数声明

geometry ST_OrientedEnvelope(geometry geom);

使用示例

SELECT ST_AsText(ST_OrientedEnvelope('MULTIPOINT ((0 0), (-1 -1), (3 2))'));

st_astext
------------------------------------------------
POLYGON((3 2,2.88 2.16,-1.12 -0.84,-1 -1,3 2))

ST_Polygonize

描述

聚合函数。返回一个GeometryCollection,其中包含了由给定的几何对象中的组成线条构成的Polygon。

函数声明

geometry ST_Polygonize(geometry set geomfield);
geometry ST_Polygonize(geometry[] geom_array);

使用示例

SELECT ST_AsEWKT(ST_Polygonize(the_geom_4269)) As geomtextrep
FROM (SELECT the_geom_4269 FROM ma.suffolk_edges ORDER BY tlid LIMIT 45) As foo;

geomtextrep
-------------------------------------
 SRID=4269;GEOMETRYCOLLECTION(POLYGON((-71.040878 42.285678,-71.040943 42.2856,-71.04096 42.285752,-71.040878 42.285678)),
 POLYGON((-71.17166 42.353675,-71.172026 42.354044,-71.17239 42.354358,-71.171794 42.354971,-71.170511 42.354855,
 -71.17112 42.354238,-71.17166 42.353675)))
(1 row)

--Use ST_Dump to dump out the polygonize geoms into individual polygons
SELECT ST_AsEWKT((ST_Dump(foofoo.polycoll)).geom) As geomtextrep
FROM (SELECT ST_Polygonize(the_geom_4269) As polycoll
    FROM (SELECT the_geom_4269 FROM ma.suffolk_edges
        ORDER BY tlid LIMIT 45) As foo) As foofoo;

geomtextrep
------------------------
 SRID=4269;POLYGON((-71.040878 42.285678,-71.040943 42.2856,-71.04096 42.285752,
-71.040878 42.285678))
 SRID=4269;POLYGON((-71.17166 42.353675,-71.172026 42.354044,-71.17239 42.354358
,-71.171794 42.354971,-71.170511 42.354855,-71.17112 42.354238,-71.17166 42.353675))
(2 rows)

ST_OffsetCurve

描述

从输入几何对象(LineString)构造给定距离和侧边的偏移线并返回。通常用于计算围绕中心线的平行线。

函数声明

geometry ST_OffsetCurve(geometry line, float signed_distance, text style_parameters='');

使用示例

SELECT ST_Union(
 ST_OffsetCurve(f.the_geom,  f.width/2, 'quad_segs=4 join=round'),
 ST_OffsetCurve(f.the_geom, -f.width/2, 'quad_segs=4 join=round')
) as track
FROM someroadstable;

ST_RemoveRepeatedPoints

描述

返回一个从给定几何对象中去掉重复的坐标点之后生成的几何对象。

函数声明

geometry ST_RemoveRepeatedPoints(geometry geom, float8 tolerance);

使用示例

None

ST_SharedPaths

描述

返回两个给定的几何对象中重叠的线段。

函数声明

geometry ST_SharedPaths(geometry lineal1, geometry lineal2);

使用示例

 SELECT ST_AsText(
  ST_SharedPaths(
    ST_GeomFromText('MULTILINESTRING((26 125,26 200,126 200,126 125,26 125),
       (51 150,101 150,76 175,51 150))'),
    ST_GeomFromText('LINESTRING(151 100,126 156.25,126 125,90 161, 76 175)')
    )
  ) As wkt

                                wkt
-------------------------------------------------------------
GEOMETRYCOLLECTION(MULTILINESTRING((126 156.25,126 125),
 (101 150,90 161),(90 161,76 175)),MULTILINESTRING EMPTY)

ST_ShiftLongitude

描述

将给定的几何对象的经度范围在-180..180和0..360两种模式之间切换!

函数声明

geometry ST_ShiftLongitude(geometry  geomA);

使用示例

--3d points
SELECT ST_AsEWKT(ST_ShiftLongitude(ST_GeomFromEWKT('SRID=4326;POINT(-118.58 38.38 10)'))) As geomA,
    ST_AsEWKT(ST_ShiftLongitude(ST_GeomFromEWKT('SRID=4326;POINT(241.42 38.38 10)'))) As geomb
geomA                              geomB
----------                          -----------
SRID=4326;POINT(241.42 38.38 10) SRID=4326;POINT(-118.58 38.38 10)

--regular line string
SELECT ST_AsText(ST_ShiftLongitude(ST_GeomFromText('LINESTRING(-118.58 38.38, -118.20 38.45)')))

st_astext
----------
LINESTRING(241.42 38.38,241.8 38.45)

ST_Simplify

描述

返回给定几何对象的简化版本,使用道格拉斯-普克算法生成该几何对象的近似表示。

函数声明

geometry ST_Simplify(geometry geomA, float tolerance, boolean preserveCollapsed);

使用示例

SELECT ST_Npoints(the_geom) As np_before, ST_NPoints(ST_Simplify(the_geom,0.1)) As np01_notbadcircle, ST_NPoints(ST_Simplify(the_geom,0.5)) As np05_notquitecircle,
ST_NPoints(ST_Simplify(the_geom,1)) As np1_octagon, ST_NPoints(ST_Simplify(the_geom,10)) As np10_triangle,
(ST_Simplify(the_geom,100) is null) As  np100_geometrygoesaway
FROM (SELECT ST_Buffer('POINT(1 3)', 10,12) As the_geom) As foo;
-result
 np_before | np01_notbadcircle | np05_notquitecircle | np1_octagon | np10_triangle | np100_geometrygoesaway
-----------+-------------------+---------------------+-------------+---------------+------------------------
        49 |                33 |                  17 |           9 |             4 | t

ST_SimplifyPreserveTopology

描述

返回给定几何对象的简化版本,使用道格拉斯-普克算法生成该几何对象的近似表示。同时避免构造无效(Invalid)的几何对象(特别是Polygon)。

函数声明

geometry ST_SimplifyPreserveTopology(geometry geomA, float tolerance);

使用示例

SELECT ST_Npoints(the_geom) As np_before, ST_NPoints(ST_SimplifyPreserveTopology(the_geom,0.1)) As np01_notbadcircle, ST_NPoints(ST_SimplifyPreserveTopology(the_geom,0.5)) As np05_notquitecircle,
ST_NPoints(ST_SimplifyPreserveTopology(the_geom,1)) As np1_octagon, ST_NPoints(ST_SimplifyPreserveTopology(the_geom,10)) As np10_square,
ST_NPoints(ST_SimplifyPreserveTopology(the_geom,100)) As  np100_stillsquare
FROM (SELECT ST_Buffer('POINT(1 3)', 10,12) As the_geom) As foo;

--result--
 np_before | np01_notbadcircle | np05_notquitecircle | np1_octagon | np10_square | np100_stillsquare
-----------+-------------------+---------------------+-------------+---------------+-------------------
        49 |                33 |                  17 |           9 |             5 |                 5

ST_SimplifyVW

描述

返回给定几何对象的简化版本,使用Visvalingam-Whyatt算法生成该几何对象的简化表示。

函数声明

geometry ST_SimplifyVW(geometry geomA, float tolerance);

使用示例

select ST_AsText(ST_SimplifyVW(geom,30)) simplified
FROM (SELECT  'LINESTRING(5 2, 3 8, 6 20, 7 25, 10 10)'::geometry geom) As foo;
-result
 simplified
------------------------------
LINESTRING(5 2,7 25,10 10)

ST_ChaikinSmoothing

描述

使用Chaikin算法对给定的几何对象进行平滑处理,返回处理结果。

函数声明

geometry ST_ChaikinSmoothing(geometry geom, integer nIterations = 1, boolean preserveEndPoints = false);

使用示例

select ST_AsText(ST_ChaikinSmoothing(geom)) smoothed
FROM (SELECT  'POLYGON((0 0, 8 8, 0 16, 0 0))'::geometry geom) As foo;
┌───────────────────────────────────────────┐
│                 smoothed                  │
├───────────────────────────────────────────┤
│ POLYGON((2 2,6 6,6 10,2 14,0 12,0 4,2 2)) │
└───────────────────────────────────────────┘

ST_FilterByM

描述

将给定几何对象的顶点按M值过滤,返回满足过滤条件的顶点构成的几何对象。

函数声明

geometry ST_FilterByM(geometry geom, double precision min, double precision max = null, boolean returnM = false);

使用示例

SELECT ST_AsText(ST_FilterByM(geom,30)) simplified
FROM (SELECT  ST_SetEffectiveArea('LINESTRING(5 2, 3 8, 6 20, 7 25, 10 10)'::geometry) geom) As foo;
-result
         simplified
----------------------------
 LINESTRING(5 2,7 25,10 10)

ST_SetEffectiveArea

描述

计算给定几何对象每个顶点的有效面积,并将结果存储为此顶点的M坐标值。

函数声明

geometry ST_SetEffectiveArea(geometry geomA, float threshold = 0, integer set_area = 1);

使用示例

select ST_AsText(ST_SetEffectiveArea(geom)) all_pts, ST_AsText(ST_SetEffectiveArea(geom,30) ) thrshld_30
FROM (SELECT  'LINESTRING(5 2, 3 8, 6 20, 7 25, 10 10)'::geometry geom) As foo;
-result
 all_pts | thrshld_30
-----------+-------------------+
LINESTRING M (5 2 3.40282346638529e+38,3 8 29,6 20 1.5,7 25 49.5,10 10 3.40282346638529e+38) | LINESTRING M (5 2 3.40282346638529e+38,7 25 49.5,10 10 3.40282346638529e+38)

ST_Split

描述

返回给定几何对象input,被另一个几何对象blade切割产生的结果,用GeometryCollection表示。

函数声明

geometry ST_Split(geometry input, geometry blade);

使用示例

-- this creates a geometry collection consisting of the 2 halves of the polygon
-- this is similar to the example we demonstrated in ST_BuildArea
SELECT ST_Split(circle, line)
FROM (SELECT
    ST_MakeLine(ST_MakePoint(10, 10),ST_MakePoint(190, 190)) As line,
    ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As circle) As foo;

-- result --
 GEOMETRYCOLLECTION(POLYGON((150 90,149.039264020162 80.2454838991936,146.193976625564 70.8658283817455,..), POLYGON(..)))

-- To convert to individual polygons, you can use ST_Dump or ST_GeometryN
SELECT ST_AsText((ST_Dump(ST_Split(circle, line))).geom) As wkt
FROM (SELECT
    ST_MakeLine(ST_MakePoint(10, 10),ST_MakePoint(190, 190)) As line,
    ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As circle) As foo;

-- result --
wkt
---------------
POLYGON((150 90,149.039264020162 80.2454838991936,..))
POLYGON((60.1371179574584 60.1371179574584,58.4265193848728 62.2214883490198,53.8060233744357 ..))

ST_SymDifference

描述

返回给定几何对象A和B中不相交的部分。

函数声明

geometry ST_SymDifference(geometry  geomA, geometry  geomB);

使用示例

--Safe for 2d - symmetric difference of 2 linestrings
SELECT ST_AsText(
    ST_SymDifference(
        ST_GeomFromText('LINESTRING(50 100, 50 200)'),
        ST_GeomFromText('LINESTRING(50 50, 50 150)')
    )
);

st_astext
---------
MULTILINESTRING((50 150,50 200),(50 50,50 100))

ST_Subdivide

描述

将给定的几何对象切分成一系列几何对象,使得每个一对象都可以用不多于给定数目的顶点来表示。

函数声明

setof geometry ST_Subdivide(geometry geom, integer max_vertices=256);

使用示例

-- Subdivide complex geometries in table, in place
with complex_areas_to_subdivide as (
    delete from polygons_table
    where ST_NPoints(geom) > 255
    returning id, column1, column2, column3, geom
)
insert into polygons_table (fid, column1, column2, column3, geom)
    select
        fid, column1, column2, column3,
        ST_Subdivide(geom, 255) as geom
    from complex_areas_to_subdivide;

ST_SwapOrdinates

描述

将给定的几何对象中坐标点的指定坐标值交换,ords参数是两个字符,代表要交换的坐标值,包含:x, y, z和m。

函数声明

geometry ST_SwapOrdinates(geometry geom, cstring ords);

使用示例

-- Scale M value by 2
SELECT ST_AsText(
  ST_SwapOrdinates(
    ST_Scale(
      ST_SwapOrdinates(g,'xm'),
      2, 1
    ),
  'xm')
) FROM ( SELECT 'POINT ZM (0 0 0 2)'::geometry g ) foo;
     st_astext
--------------------
 POINT ZM (0 0 0 4)

ST_Union

描述

返回给定几何对象的并集。

函数声明

geometry ST_Union(geometry set g1field);
geometry ST_Union(geometry g1, geometry g2);
geometry ST_Union(geometry[] g1_array);

使用示例

-- Aggregate example
SELECT stusps,
       ST_Multi(ST_Union(f.the_geom)) as singlegeom
     FROM sometable As f
GROUP BY stusps

SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(1 2)'),
    ST_GeomFromText('POINT(-2 3)') ) )

st_astext
----------
MULTIPOINT(-2 3,1 2)


SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(1 2)'),
        ST_GeomFromText('POINT(1 2)') ) );
st_astext
----------
POINT(1 2)

--3d example - sort of supports 3d (and with mixed dimensions!)
SELECT ST_AsEWKT(st_union(the_geom))
FROM
(SELECT ST_GeomFromEWKT('POLYGON((-7 4.2,-7.1 4.2,-7.1 4.3,
-7 4.2))') as the_geom
UNION ALL
SELECT ST_GeomFromEWKT('POINT(5 5 5)') as the_geom
UNION ALL
    SELECT ST_GeomFromEWKT('POINT(-2 3 1)') as the_geom
UNION ALL
SELECT ST_GeomFromEWKT('LINESTRING(5 5 5, 10 10 10)') as the_geom ) as foo;

st_asewkt
---------
GEOMETRYCOLLECTION(POINT(-2 3 1),LINESTRING(5 5 5,10 10 10),POLYGON((-7 4.2 5,-7.1 4.2 5,-7.1 4.3 5,-7 4.2 5)));

--3d example not mixing dimensions
SELECT ST_AsEWKT(st_union(the_geom))
FROM
(SELECT ST_GeomFromEWKT('POLYGON((-7 4.2 2,-7.1 4.2 3,-7.1 4.3 2,
-7 4.2 2))') as the_geom
UNION ALL
SELECT ST_GeomFromEWKT('POINT(5 5 5)') as the_geom
UNION ALL
    SELECT ST_GeomFromEWKT('POINT(-2 3 1)') as the_geom
UNION ALL
SELECT ST_GeomFromEWKT('LINESTRING(5 5 5, 10 10 10)') as the_geom ) as foo;

st_asewkt
---------
GEOMETRYCOLLECTION(POINT(-2 3 1),LINESTRING(5 5 5,10 10 10),POLYGON((-7 4.2 2,-7.1 4.2 3,-7.1 4.3 2,-7 4.2 2)))

--Examples using new Array construct
SELECT ST_Union(ARRAY(SELECT the_geom FROM sometable));

SELECT ST_AsText(ST_Union(ARRAY[ST_GeomFromText('LINESTRING(1 2, 3 4)'),
            ST_GeomFromText('LINESTRING(3 4, 4 5)')])) As wktunion;

--wktunion---
MULTILINESTRING((3 4,4 5),(1 2,3 4))

ST_VoronoiLines

描述

返回由给定几何对象的顶点构造的沃罗诺伊图的边界线。

函数声明

geometry ST_VoronoiLines( g1 geometry , tolerance float8 , extend_to geometry );

使用示例

SELECT ST_VoronoiLines(geom, 30) As geom
FROM (SELECT 'MULTIPOINT (50 30, 60 30, 100 100,10 150, 110 120)'::geometry As geom ) As g
-- ST_AsText output
MULTILINESTRING((135.555555555556 270,36.8181818181818 92.2727272727273),(36.8181818181818 92.2727272727273,-110 43.3333333333333),(230 -45.7142857142858,36.8181818181818 92.2727272727273))

ST_VoronoiPolygons

描述

返回由给定几何对象的顶点构造的沃罗诺伊图的各个单元(作为Polygon类型)。

函数声明

geometry ST_VoronoiPolygons(g1 geometry , tolerance float8 , extend_to geometry);

使用示例

SELECT
    ST_VoronoiPolygons(geom) As geom
FROM (SELECT 'MULTIPOINT (50 30, 60 30, 100 100,10 150, 110 120)'::geometry As geom ) As g;
 -- ST_AsText output
GEOMETRYCOLLECTION(POLYGON((-110 43.3333333333333,-110 270,100.5 270,59.3478260869565 132.826086956522,36.8181818181818 92.2727272727273,-110 43.3333333333333)),
POLYGON((55 -90,-110 -90,-110 43.3333333333333,36.8181818181818 92.2727272727273,55 79.2857142857143,55 -90)),
POLYGON((230 47.5,230 -20.7142857142857,55 79.2857142857143,36.8181818181818 92.2727272727273,59.3478260869565 132.826086956522,230 47.5)),POLYGON((230 -20.7142857142857,230 -90,55 -90,55 79.2857142857143,230 -20.7142857142857)),
POLYGON((100.5 270,230 270,230 47.5,59.3478260869565 132.826086956522,100.5 270)))
  • 本页导读 (1)
文档反馈