› 推特 ›阿里云技术文档›正文

空间对象处理函数_空间函数SQL参考_时空数据库_时序时空数据库TSDB

admin 阿里云技术文档 2020-02-11 180 0
阿里云服务器优惠

空间对象处理函数

ST_Buffer

描述

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

函数声明

  1. geometry ST_Buffer(geometry  g1, float  radius_of_buffer);
  2. geometry ST_Buffer(geometry  g1, float  radius_of_buffer, integer  num_seg_quarter_circle);
  3. geometry ST_Buffer(geometry  g1, float  radius_of_buffer, text  buffer_style_parameters);
  4. geography ST_Buffer(geography  g1, float  radius_of_buffer_in_meters);
  5. geography ST_Buffer(geography  g1, float  radius_of_buffer, integer  num_seg_quarter_circle);
  6. geography ST_Buffer(geography  g1, float  radius_of_buffer, text  buffer_style_parameters);

使用示例

  1. SELECT ST_Buffer(
  2.  ST_GeomFromText('POINT(100 90)'),
  3.  50, 'quad_segs=8');

ST_BuildArea

描述

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

函数声明

  1. geometry ST_BuildArea(geometry  A);

使用示例

  1. SELECT ST_BuildArea(ST_Collect(smallc,bigc))
  2. FROM (SELECT
  3.     ST_Buffer(
  4.       ST_GeomFromText('POINT(100 90)'), 25) As smallc,
  5.     ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As bigc) As foo;

ST_ClipByBox2D

描述

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

函数声明

  1. geometry ST_ClipByBox2D(geometry geom, box2d box);

使用示例

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

ST_Collect

描述

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

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

函数声明

  1. geometry ST_Collect(geometry set g1field);
  2. geometry ST_Collect(geometry g1, geometry g2);
  3. geometry ST_Collect(geometry[] g1_array);

使用示例

  1. SELECT stusps, ST_Collect(f.the_geom) as singlegeom
  2.      FROM (SELECT stusps, (ST_Dump(the_geom)).geom As the_geom
  3.                 FROM
  4.                 somestatetable ) As f
  5. GROUP BY stusps

ST_ConcaveHull

描述

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

函数声明

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

使用示例

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

ST_ConvexHull

描述

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

函数声明

  1. geometry ST_ConvexHull(geometry  geomA);

使用示例

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

ST_CurveToLine

描述

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

函数声明

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

使用示例

  1. SELECT ST_AsText(ST_CurveToLine(ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)')));
  3. --Result --
  4.  LINESTRING(220268 150415,220269.95064912 150416.539364228,220271.823415575 150418.17258804,220273.613787707 150419.895736857,
  5.  220275.317452352 150421.704659462,220276.930305234 150423.594998003,220278.448460847 150425.562198489,
  6.  220279.868261823 150427.60152176,220281.186287736 150429.708054909,220282.399363347 150431.876723113,
  7.  220283.50456625 150434.10230186,220284.499233914 150436.379429536,220285.380970099 150438.702620341,220286.147650624 150441.066277505,
  8.  220286.797428488 150443.464706771,220287.328738321 150445.892130112,220287.740300149 150448.342699654,
  9.  220288.031122486 150450.810511759,220288.200504713 150453.289621251,220288.248038775 150455.77405574,
  10.  220288.173610157 150458.257830005,220287.977398166 150460.734960415,220287.659875492 150463.199479347,
  11.  220287.221807076 150465.64544956,220286.664248262 150468.066978495,220285.988542259 150470.458232479,220285.196316903 150472.81345077,
  12.  220284.289480732 150475.126959442,220283.270218395 150477.39318505,220282.140985384 150479.606668057,
  13.  220280.90450212 150481.762075989,220279.5637474 150483.85421628,220278.12195122 150485.87804878,
  14.  220276.582586992 150487.828697901,220274.949363179 150489.701464356,220273.226214362 150491.491836488,
  15.  220271.417291757 150493.195501133,220269.526953216 150494.808354014,220267.559752731 150496.326509628,
  16.  220265.520429459 150497.746310603,220263.41389631 150499.064336517,220261.245228106 150500.277412127,
  17.  220259.019649359 150501.38261503,220256.742521683 150502.377282695,220254.419330878 150503.259018879,
  18.  220252.055673714 150504.025699404,220249.657244448 150504.675477269,220247.229821107 150505.206787101,
  19.  220244.779251566 150505.61834893,220242.311439461 150505.909171266,220239.832329968 150506.078553494,
  20.  220237.347895479 150506.126087555,220234.864121215 150506.051658938,220232.386990804 150505.855446946,
  21.  220229.922471872 150505.537924272,220227.47650166 150505.099855856,220225.054972724 150504.542297043,
  22.  220222.663718741 150503.86659104,220220.308500449 150503.074365683,
  23.  220217.994991777 150502.167529512,220215.72876617 150501.148267175,
  24.  220213.515283163 150500.019034164,220211.35987523 150498.7825509,
  25.  220209.267734939 150497.441796181,220207.243902439 150496,
  26.  220205.293253319 150494.460635772,220203.420486864 150492.82741196,220201.630114732 150491.104263143,
  27.  220199.926450087 150489.295340538,220198.313597205 150487.405001997,220196.795441592 150485.437801511,
  28.  220195.375640616 150483.39847824,220194.057614703 150481.291945091,220192.844539092 150479.123276887,220191.739336189 150476.89769814,
  29.  220190.744668525 150474.620570464,220189.86293234 150472.297379659,220189.096251815 150469.933722495,
  30.  220188.446473951 150467.535293229,220187.915164118 150465.107869888,220187.50360229 150462.657300346,
  31.  220187.212779953 150460.189488241,220187.043397726 150457.710378749,220186.995863664 150455.22594426,
  32.  220187.070292282 150452.742169995,220187.266504273 150450.265039585,220187.584026947 150447.800520653,
  33.  220188.022095363 150445.35455044,220188.579654177 150442.933021505,220189.25536018 150440.541767521,
  34.  220190.047585536 150438.18654923,220190.954421707 150435.873040558,220191.973684044 150433.60681495,
  35.  220193.102917055 150431.393331943,220194.339400319 150429.237924011,220195.680155039 150427.14578372,220197.12195122 150425.12195122,
  36.  220198.661315447 150423.171302099,220200.29453926 150421.298535644,220202.017688077 150419.508163512,220203.826610682 150417.804498867,
  37.  220205.716949223 150416.191645986,220207.684149708 150414.673490372,220209.72347298 150413.253689397,220211.830006129 150411.935663483,
  38.  220213.998674333 150410.722587873,220216.22425308 150409.61738497,220218.501380756 150408.622717305,220220.824571561 150407.740981121,
  39.  220223.188228725 150406.974300596,220225.586657991 150406.324522731,220227 150406)
  41. --3d example
  42. SELECT ST_AsEWKT(ST_CurveToLine(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)')));
  43. Output
  44. ------
  45.  LINESTRING(220268 150415 1,220269.95064912 150416.539364228 1.0181172856673,
  46.  220271.823415575 150418.17258804 1.03623457133459,220273.613787707 150419.895736857 1.05435185700189,....AD INFINITUM ....
  47.     220225.586657991 150406.324522731 1.32611114201132,220227 150406 3)
  49. --use only 2 segments to approximate quarter circle
  50. SELECT ST_AsText(ST_CurveToLine(ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)'),2));
  51. st_astext
  52. ------------------------------
  53.  LINESTRING(220268 150415,220287.740300149 150448.342699654,220278.12195122 150485.87804878,
  54.  220244.779251566 150505.61834893,220207.243902439 150496,220187.50360229 150462.657300346,
  55.  220197.12195122 150425.12195122,220227 150406)
  57. -- Ensure approximated line is no further than 20 units away from
  58. -- original curve, and make the result direction-neutral
  59. SELECT ST_AsText(ST_CurveToLine(
  60.  'CIRCULARSTRING(0 0,100 -100,200 0)'::geometry,
  61.     20, -- Tolerance
  62.     1, -- Above is max distance between curve and line
  63.     1  -- Symmetric flag
  64. ));
  65. st_astext
  66. -------------------------------------------------------------------------------------------
  67.  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。

函数声明

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

使用示例

  1. -- geometries overlaid multilinestring triangles
  2. SELECT
  3.     ST_DelaunayTriangles(
  4.         ST_Union(ST_GeomFromText('POLYGON((175 150, 20 40,
  5.             50 60, 125 100, 175 150))'),
  6.         ST_Buffer(ST_GeomFromText('POINT(110 170)'), 20)
  7.         ))
  8.      As  dtriag;

ST_Difference

描述

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

函数声明

  1. geometry ST_Difference(geometry  geomA, geometry  geomB);

使用示例

  1. --Safe for 2d. This is same geometries as what is shown for st_symdifference
  2. SELECT ST_AsText(
  3.     ST_Difference(
  4.             ST_GeomFromText('LINESTRING(50 100, 50 200)'),
  5.             ST_GeomFromText('LINESTRING(50 50, 50 150)')
  6.         )
  7.     );
  9. st_astext
  10. ---------
  11. LINESTRING(50 150,50 200)

ST_Dump

描述

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

函数声明

  1. geometry_dump[] ST_Dump(geometry  g1);

使用示例

  1. SELECT sometable.field1, sometable.field1,
  2.       (ST_Dump(sometable.the_geom)).geom AS the_geom
  3. FROM sometable;
  5. -- Break a compound curve into its constituent linestrings and circularstrings
  6. SELECT ST_AsEWKT(a.geom), ST_HasArc(a.geom)
  7.   FROM ( SELECT (ST_Dump(p_geom)).geom AS geom
  8.          FROM (SELECT ST_GeomFromEWKT('COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))') AS p_geom) AS b
  9.         ) AS a;
  10.           st_asewkt          | st_hasarc
  11. -----------------------------+----------
  12.  CIRCULARSTRING(0 0,1 1,1 0) | t
  13.  LINESTRING(1 0,0 1)         | f
  14. (2 rows)

ST_DumpPoints

描述

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

函数声明

  1. geometry_dump[] ST_DumpPoints(geometry geom);

使用示例

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

ST_DumpRings

描述

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

函数声明

  1. geometry_dump[] ST_DumpRings(geometry  a_polygon);

使用示例

  1. SELECT sometable.field1, sometable.field1,
  2.       (ST_DumpRings(sometable.the_geom)).geom As the_geom
  3. FROM sometableOfpolys;
  5. SELECT ST_AsEWKT(geom) As the_geom, path
  6.     FROM ST_DumpRings(
  7.         ST_GeomFromEWKT('POLYGON((-8149064 5133092 1,-8149064 5132986 1,-8148996 5132839 1,-8148972 5132767 1,-8148958 5132508 1,-8148941 5132466 1,-8148924 5132394 1,
  8.         -8148903 5132210 1,-8148930 5131967 1,-8148992 5131978 1,-8149237 5132093 1,-8149404 5132211 1,-8149647 5132310 1,-8149757 5132394 1,
  9.         -8150305 5132788 1,-8149064 5133092 1),
  10.         (-8149362 5132394 1,-8149446 5132501 1,-8149548 5132597 1,-8149695 5132675 1,-8149362 5132394 1))')
  11.         )  as foo;
  12.  path |                                            the_geom
  13. ----------------------------------------------------------------------------------------------------------------
  14.   {0} | POLYGON((-8149064 5133092 1,-8149064 5132986 1,-8148996 5132839 1,-8148972 5132767 1,-8148958 5132508 1,
  15.       |          -8148941 5132466 1,-8148924 5132394 1,
  16.       |          -8148903 5132210 1,-8148930 5131967 1,
  17.       |          -8148992 5131978 1,-8149237 5132093 1,
  18.       |          -8149404 5132211 1,-8149647 5132310 1,-8149757 5132394 1,-8150305 5132788 1,-8149064 5133092 1))
  19.   {1} | POLYGON((-8149362 5132394 1,-8149446 5132501 1,
  20.       |          -8149548 5132597 1,-8149695 5132675 1,-8149362 5132394 1))

ST_FlipCoordinates

描述

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

函数声明

  1. geometry ST_FlipCoordinates(geometry geom);

使用示例

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

ST_GeneratePoints

描述

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

函数声明

  1. geometry ST_GeneratePoints(geometry g, numeric npoints);

使用示例

  1. SELECT ST_GeneratePoints(
  2.     ST_Buffer(
  3.         ST_GeomFromText(
  4.         'LINESTRING(50 50,150 150,150 50)'
  5.         ), 10, 'endcap=round join=round'), 12);

ST_Intersection

描述

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

函数声明

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

使用示例

  1. SELECT ST_AsText(ST_Intersection('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry));
  2.  st_astext
  3. ---------------
  4. GEOMETRYCOLLECTION EMPTY
  5. (1 row)
  6. SELECT ST_AsText(ST_Intersection('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry));
  7.  st_astext
  8. ---------------
  9. POINT(0 0)
  10. (1 row)
  12. ---Clip all lines (trails) by country (here we assume country geom are POLYGON or MULTIPOLYGONS)
  13. -- NOTE: we are only keeping intersections that result in a LINESTRING or MULTILINESTRING because we don't
  14. -- care about trails that just share a point
  15. -- the dump is needed to expand a geometry collection into individual single MULT* parts
  16. -- the below is fairly generic and will work for polys, etc. by just changing the where clause
  17. SELECT clipped.gid, clipped.f_name, clipped_geom
  18. FROM (SELECT trails.gid, trails.f_name, (ST_Dump(ST_Intersection(country.the_geom, trails.the_geom))).geom As clipped_geom
  19. FROM country
  20.     INNER JOIN trails
  21.     ON ST_Intersects(country.the_geom, trails.the_geom))  As clipped
  22.     WHERE ST_Dimension(clipped.clipped_geom) = 1 ;
  24. --For polys e.g. polygon landmarks, you can also use the sometimes faster hack that buffering anything by 0.0
  25. -- except a polygon results in an empty geometry collection
  26. --(so a geometry collection containing polys, lines and points)
  27. -- buffered by 0.0 would only leave the polygons and dissolve the collection shell
  28. SELECT poly.gid,  ST_Multi(ST_Buffer(
  29.                 ST_Intersection(country.the_geom, poly.the_geom),
  30.                 0.0)
  31.                 ) As clipped_geom
  32. FROM country
  33.     INNER JOIN poly
  34.     ON ST_Intersects(country.the_geom, poly.the_geom)
  35.     WHERE Not ST_IsEmpty(ST_Buffer(ST_Intersection(country.the_geom, poly.the_geom),0.0));

ST_LineToCurve

描述

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

函数声明

  1. geometry ST_LineToCurve(geometry  geomANoncircular);

使用示例

  1.  -- 2D Example
  2. SELECT ST_AsText(ST_LineToCurve(foo.the_geom)) As curvedastext,ST_AsText(foo.the_geom) As non_curvedastext
  3.     FROM (SELECT ST_Buffer('POINT(1 3)'::geometry, 3) As the_geom) As foo;
  5. curvedatext                                                            non_curvedastext
  6. --------------------------------------------------------------------|-----------------------------------------------------------------
  7. CURVEPOLYGON(CIRCULARSTRING(4 3,3.12132034355964 0.878679656440359, | POLYGON((4 3,3.94235584120969 2.41472903395162,3.77163859753386 1.85194970290473,
  8. 1 0,-1.12132034355965 5.12132034355963,4 3))                        |  3.49440883690764 1.33328930094119,3.12132034355964 0.878679656440359,
  9.                                                                     |  2.66671069905881 0.505591163092366,2.14805029709527 0.228361402466141,
  10.                                                                     |  1.58527096604839 0.0576441587903094,1 0,
  11.                                                                     |  0.414729033951621 0.0576441587903077,-0.148050297095264 0.228361402466137,
  12.                                                                     |  -0.666710699058802 0.505591163092361,-1.12132034355964 0.878679656440353,
  13.                                                                     |  -1.49440883690763 1.33328930094119,-1.77163859753386 1.85194970290472
  14.                                                                     |  --ETC-- ,3.94235584120969 3.58527096604839,4 3))
  16. --3D example
  17. SELECT ST_AsText(ST_LineToCurve(geom)) As curved, ST_AsText(geom) AS not_curved
  18. FROM (SELECT ST_Translate(ST_Force3D(ST_Boundary(ST_Buffer(ST_Point(1,3), 2,2))),0,0,3) AS geom) AS foo;
  20.                         curved                        |               not_curved
  21. ------------------------------------------------------+---------------------------------------------------------------------
  22.  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,
  23.                                                       |    -0.414213562373092 1.5857864376269 3,-1 2.99999999999999 3,
  24.                                                       | -0.414213562373101 4.41421356237309 3,
  25.                                                       |    0.999999999999991 5 3,2.41421356237309 4.4142135623731 3,3 3 3)
  26. (1 row)

ST_MakeValid

描述

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

函数声明

  1. geometry ST_MakeValid(geometry input);

使用示例

  1. None

ST_MemUnion

描述

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

函数声明

  1. geometry ST_MemUnion(geometry set geomfield);

使用示例

  1. 参见ST_Union

ST_MinimumBoundingCircle

描述

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

函数声明

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

使用示例

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

ST_MinimumBoundingRadius

描述

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

函数声明

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

使用示例

  1. SELECT ST_AsText(center), radius FROM ST_MinimumBoundingRadius('POLYGON((26426 65078,26531 65242,26075 65136,26096 65427,26426 65078))');
  3.                 st_astext                 |      radius
  4. ------------------------------------------+------------------
  5.  POINT(26284.8418027133 65267.1145090825) | 247.436045591407

ST_OrientedEnvelope

描述

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

函数声明

  1. geometry ST_OrientedEnvelope(geometry geom);

使用示例

  1. SELECT ST_AsText(ST_OrientedEnvelope('MULTIPOINT ((0 0), (-1 -1), (3 2))'));
  3. st_astext
  4. ------------------------------------------------
  5. POLYGON((3 2,2.88 2.16,-1.12 -0.84,-1 -1,3 2))

ST_Polygonize

描述

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

函数声明

  1. geometry ST_Polygonize(geometry set geomfield);
  2. geometry ST_Polygonize(geometry[] geom_array);

使用示例

  1. SELECT ST_AsEWKT(ST_Polygonize(the_geom_4269)) As geomtextrep
  2. FROM (SELECT the_geom_4269 FROM ma.suffolk_edges ORDER BY tlid LIMIT 45) As foo;
  4. geomtextrep
  5. -------------------------------------
  6.  SRID=4269;GEOMETRYCOLLECTION(POLYGON((-71.040878 42.285678,-71.040943 42.2856,-71.04096 42.285752,-71.040878 42.285678)),
  7.  POLYGON((-71.17166 42.353675,-71.172026 42.354044,-71.17239 42.354358,-71.171794 42.354971,-71.170511 42.354855,
  8.  -71.17112 42.354238,-71.17166 42.353675)))
  9. (1 row)
  11. --Use ST_Dump to dump out the polygonize geoms into individual polygons
  12. SELECT ST_AsEWKT((ST_Dump(foofoo.polycoll)).geom) As geomtextrep
  13. FROM (SELECT ST_Polygonize(the_geom_4269) As polycoll
  14.     FROM (SELECT the_geom_4269 FROM ma.suffolk_edges
  15.         ORDER BY tlid LIMIT 45) As foo) As foofoo;
  17. geomtextrep
  18. ------------------------
  19.  SRID=4269;POLYGON((-71.040878 42.285678,-71.040943 42.2856,-71.04096 42.285752,
  20. -71.040878 42.285678))
  21.  SRID=4269;POLYGON((-71.17166 42.353675,-71.172026 42.354044,-71.17239 42.354358
  22. ,-71.171794 42.354971,-71.170511 42.354855,-71.17112 42.354238,-71.17166 42.353675))
  23. (2 rows)

ST_OffsetCurve

描述

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

函数声明

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

使用示例

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

ST_RemoveRepeatedPoints

描述

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

函数声明

  1. geometry ST_RemoveRepeatedPoints(geometry geom, float8 tolerance);

使用示例

  1. None

ST_SharedPaths

描述

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

函数声明

  1. geometry ST_SharedPaths(geometry lineal1, geometry lineal2);

使用示例

  1.  SELECT ST_AsText(
  2.   ST_SharedPaths(
  3.     ST_GeomFromText('MULTILINESTRING((26 125,26 200,126 200,126 125,26 125),
  4.        (51 150,101 150,76 175,51 150))'),
  5.     ST_GeomFromText('LINESTRING(151 100,126 156.25,126 125,90 161, 76 175)')
  6.     )
  7.   ) As wkt
  9.                                 wkt
  10. -------------------------------------------------------------
  11. GEOMETRYCOLLECTION(MULTILINESTRING((126 156.25,126 125),
  12.  (101 150,90 161),(90 161,76 175)),MULTILINESTRING EMPTY)

ST_ShiftLongitude

描述

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

函数声明

  1. geometry ST_ShiftLongitude(geometry  geomA);

使用示例

  1. --3d points
  2. SELECT ST_AsEWKT(ST_ShiftLongitude(ST_GeomFromEWKT('SRID=4326;POINT(-118.58 38.38 10)'))) As geomA,
  3.     ST_AsEWKT(ST_ShiftLongitude(ST_GeomFromEWKT('SRID=4326;POINT(241.42 38.38 10)'))) As geomb
  4. geomA                              geomB
  5. ----------                          -----------
  6. SRID=4326;POINT(241.42 38.38 10) SRID=4326;POINT(-118.58 38.38 10)
  8. --regular line string
  9. SELECT ST_AsText(ST_ShiftLongitude(ST_GeomFromText('LINESTRING(-118.58 38.38, -118.20 38.45)')))
  11. st_astext
  12. ----------
  13. LINESTRING(241.42 38.38,241.8 38.45)

ST_Simplify

描述

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

函数声明

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

使用示例

  1. 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,
  2. ST_NPoints(ST_Simplify(the_geom,1)) As np1_octagon, ST_NPoints(ST_Simplify(the_geom,10)) As np10_triangle,
  3. (ST_Simplify(the_geom,100) is null) As  np100_geometrygoesaway
  4. FROM (SELECT ST_Buffer('POINT(1 3)', 10,12) As the_geom) As foo;
  5. -result
  6.  np_before | np01_notbadcircle | np05_notquitecircle | np1_octagon | np10_triangle | np100_geometrygoesaway
  7. -----------+-------------------+---------------------+-------------+---------------+------------------------
  8.         49 |                33 |                  17 |           9 |             4 | t

ST_SimplifyPreserveTopology

描述

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

函数声明

  1. geometry ST_SimplifyPreserveTopology(geometry geomA, float tolerance);

使用示例

  1. 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,
  2. ST_NPoints(ST_SimplifyPreserveTopology(the_geom,1)) As np1_octagon, ST_NPoints(ST_SimplifyPreserveTopology(the_geom,10)) As np10_square,
  3. ST_NPoints(ST_SimplifyPreserveTopology(the_geom,100)) As  np100_stillsquare
  4. FROM (SELECT ST_Buffer('POINT(1 3)', 10,12) As the_geom) As foo;
  6. --result--
  7.  np_before | np01_notbadcircle | np05_notquitecircle | np1_octagon | np10_square | np100_stillsquare
  8. -----------+-------------------+---------------------+-------------+---------------+-------------------
  9.         49 |                33 |                  17 |           9 |             5 |                 5

ST_SimplifyVW

描述

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

函数声明

  1. geometry ST_SimplifyVW(geometry geomA, float tolerance);

使用示例

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

ST_ChaikinSmoothing

描述

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

函数声明

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

使用示例

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

ST_FilterByM

描述

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

函数声明

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

使用示例

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

ST_SetEffectiveArea

描述

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

函数声明

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

使用示例

  1. select ST_AsText(ST_SetEffectiveArea(geom)) all_pts, ST_AsText(ST_SetEffectiveArea(geom,30) ) thrshld_30
  2. FROM (SELECT  'LINESTRING(5 2, 3 8, 6 20, 7 25, 10 10)'::geometry geom) As foo;
  3. -result
  4.  all_pts | thrshld_30
  5. -----------+-------------------+
  6. 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表示。

函数声明

  1. geometry ST_Split(geometry input, geometry blade);

使用示例

  1. -- this creates a geometry collection consisting of the 2 halves of the polygon
  2. -- this is similar to the example we demonstrated in ST_BuildArea
  3. SELECT ST_Split(circle, line)
  4. FROM (SELECT
  5.     ST_MakeLine(ST_MakePoint(10, 10),ST_MakePoint(190, 190)) As line,
  6.     ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As circle) As foo;
  8. -- result --
  9.  GEOMETRYCOLLECTION(POLYGON((150 90,149.039264020162 80.2454838991936,146.193976625564 70.8658283817455,..), POLYGON(..)))
  11. -- To convert to individual polygons, you can use ST_Dump or ST_GeometryN
  12. SELECT ST_AsText((ST_Dump(ST_Split(circle, line))).geom) As wkt
  13. FROM (SELECT
  14.     ST_MakeLine(ST_MakePoint(10, 10),ST_MakePoint(190, 190)) As line,
  15.     ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As circle) As foo;
  17. -- result --
  18. wkt
  19. ---------------
  20. POLYGON((150 90,149.039264020162 80.2454838991936,..))
  21. POLYGON((60.1371179574584 60.1371179574584,58.4265193848728 62.2214883490198,53.8060233744357 ..))

ST_SymDifference

描述

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

函数声明

  1. geometry ST_SymDifference(geometry  geomA, geometry  geomB);

使用示例

  1. --Safe for 2d - symmetric difference of 2 linestrings
  2. SELECT ST_AsText(
  3.     ST_SymDifference(
  4.         ST_GeomFromText('LINESTRING(50 100, 50 200)'),
  5.         ST_GeomFromText('LINESTRING(50 50, 50 150)')
  6.     )
  7. );
  9. st_astext
  10. ---------
  11. MULTILINESTRING((50 150,50 200),(50 50,50 100))

ST_Subdivide

描述

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

函数声明

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

使用示例

  1. -- Subdivide complex geometries in table, in place
  2. with complex_areas_to_subdivide as (
  3.     delete from polygons_table
  4.     where ST_NPoints(geom) > 255
  5.     returning id, column1, column2, column3, geom
  6. )
  7. insert into polygons_table (fid, column1, column2, column3, geom)
  8.     select
  9.         fid, column1, column2, column3,
  10.         ST_Subdivide(geom, 255) as geom
  11.     from complex_areas_to_subdivide;

ST_SwapOrdinates

描述

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

函数声明

  1. geometry ST_SwapOrdinates(geometry geom, cstring ords);

使用示例

  1. -- Scale M value by 2
  2. SELECT ST_AsText(
  3.   ST_SwapOrdinates(
  4.     ST_Scale(
  5.       ST_SwapOrdinates(g,'xm'),
  6.       2, 1
  7.     ),
  8.   'xm')
  9. ) FROM ( SELECT 'POINT ZM (0 0 0 2)'::geometry g ) foo;
  10.      st_astext
  11. --------------------
  12.  POINT ZM (0 0 0 4)

ST_Union

描述

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

函数声明

  1. geometry ST_Union(geometry set g1field);
  2. geometry ST_Union(geometry g1, geometry g2);
  3. geometry ST_Union(geometry[] g1_array);

使用示例

  1. -- Aggregate example
  2. SELECT stusps,
  3.        ST_Multi(ST_Union(f.the_geom)) as singlegeom
  4.      FROM sometable As f
  5. GROUP BY stusps
  7. SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(1 2)'),
  8.     ST_GeomFromText('POINT(-2 3)') ) )
  10. st_astext
  11. ----------
  12. MULTIPOINT(-2 3,1 2)
  15. SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(1 2)'),
  16.         ST_GeomFromText('POINT(1 2)') ) );
  17. st_astext
  18. ----------
  19. POINT(1 2)
  21. --3d example - sort of supports 3d (and with mixed dimensions!)
  22. SELECT ST_AsEWKT(st_union(the_geom))
  23. FROM
  24. (SELECT ST_GeomFromEWKT('POLYGON((-7 4.2,-7.1 4.2,-7.1 4.3,
  25. -7 4.2))') as the_geom
  26. UNION ALL
  27. SELECT ST_GeomFromEWKT('POINT(5 5 5)') as the_geom
  28. UNION ALL
  29.     SELECT ST_GeomFromEWKT('POINT(-2 3 1)') as the_geom
  30. UNION ALL
  31. SELECT ST_GeomFromEWKT('LINESTRING(5 5 5, 10 10 10)') as the_geom ) as foo;
  33. st_asewkt
  34. ---------
  35. 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)));
  37. --3d example not mixing dimensions
  38. SELECT ST_AsEWKT(st_union(the_geom))
  39. FROM
  40. (SELECT ST_GeomFromEWKT('POLYGON((-7 4.2 2,-7.1 4.2 3,-7.1 4.3 2,
  41. -7 4.2 2))') as the_geom
  42. UNION ALL
  43. SELECT ST_GeomFromEWKT('POINT(5 5 5)') as the_geom
  44. UNION ALL
  45.     SELECT ST_GeomFromEWKT('POINT(-2 3 1)') as the_geom
  46. UNION ALL
  47. SELECT ST_GeomFromEWKT('LINESTRING(5 5 5, 10 10 10)') as the_geom ) as foo;
  49. st_asewkt
  50. ---------
  51. 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)))
  53. --Examples using new Array construct
  54. SELECT ST_Union(ARRAY(SELECT the_geom FROM sometable));
  56. SELECT ST_AsText(ST_Union(ARRAY[ST_GeomFromText('LINESTRING(1 2, 3 4)'),
  57.             ST_GeomFromText('LINESTRING(3 4, 4 5)')])) As wktunion;
  59. --wktunion---
  60. MULTILINESTRING((3 4,4 5),(1 2,3 4))

ST_VoronoiLines

描述

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

函数声明

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

使用示例

  1. SELECT ST_VoronoiLines(geom, 30) As geom
  2. FROM (SELECT 'MULTIPOINT (50 30, 60 30, 100 100,10 150, 110 120)'::geometry As geom ) As g
  3. -- ST_AsText output
  4. 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类型）。

函数声明

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

使用示例

  1. SELECT
  2.     ST_VoronoiPolygons(geom) As geom
  3. FROM (SELECT 'MULTIPOINT (50 30, 60 30, 100 100,10 150, 110 120)'::geometry As geom ) As g;
  4.  -- ST_AsText output
  5. GEOMETRYCOLLECTION(POLYGON((-110 43.3333333333333,-110 270,100.5 270,59.3478260869565 132.826086956522,36.8181818181818 92.2727272727273,-110 43.3333333333333)),
  6. POLYGON((55 -90,-110 -90,-110 43.3333333333333,36.8181818181818 92.2727272727273,55 79.2857142857143,55 -90)),
  7. 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)),
  8. POLYGON((100.5 270,230 270,230 47.5,59.3478260869565 132.826086956522,100.5 270)))
版权声明

本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。

评论

-----