[第四篇] PostGIS：“我让PG更完美！”
概要
本篇文章主要分为几何图形处理函数、仿生变换函数、聚类函数、边界分析函数、线性参考函数、轨迹函数、SFCGAL 函数、版本函数这八部分。
Geometry Processing
ST_Buffer
(T) Returns a geometry covering all points within a given distance from the input geometry.
//语法
geometry ST_Buffer(geometry g1, float radius_of_buffer, text buffer_style_parameters=”);
geometry ST_Buffer(geometry g1, float radius_of_buffer, integer num_seg_quarter_circle);
geography ST_Buffer(geography g1, float radius_of_buffer, text buffer_style_parameters);
geography ST_Buffer(geography g1, float radius_of_buffer, integer num_seg_quarter_circle);
//示例
SELECT ST_Buffer(
ST_GeomFromText('POINT(100 90)'),
50, 'quad_segs=8');
SELECT ST_Buffer(
ST_GeomFromText('POINT(100 90)'),
50, 'quad_segs=2');
SELECT ST_Buffer(
ST_GeomFromText( 'LINESTRING(50 50,150 150,150 50)'
), 10, 'join=bevel');
SELECT ST_Buffer(
ST_GeomFromText( 'LINESTRING(50 50,150 150,150 50)'
), 10, 'join=mitre mitre_limit=5.0');
SELECT ST_Buffer(
ST_GeomFromText( 'LINESTRING(50 50,150 150,150 50)'
), 10, 'side=left');
SELECT ST_Buffer(
ST_GeomFromText( 'LINESTRING(50 50,150 150,150 50)'
), 10, 'side=right');
SELECT ST_Buffer(
ST_ForceRHR(
ST_Boundary(
ST_GeomFromText( 'POLYGON ((50 50, 50 150, 150 150, 150
50, 50 50))'))),
), 20, 'side=left');
ST_BuildArea
Creates an areal geometry formed by the constituent linework of given geometry.
//语法
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;
SELECT ST_BuildArea(ST_Collect(line,circle))
FROM (SELECT
ST_Buffer(
ST_MakeLine(ST_MakePoint(10, 10),ST_MakePoint(190, 190)),
5) As line,
ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As circle) As foo;
--this creates the same gaping hole
--but using linestrings instead of polygons
SELECT ST_BuildArea(
ST_Collect(ST_ExteriorRing(line),ST_ExteriorRing(circle))
)
FROM (SELECT ST_Buffer(
ST_MakeLine(ST_MakePoint(10, 10),ST_MakePoint(190, 190))
,5) As line,
ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50) As circle) As foo;
ST_Centroid
Returns the geometric center of a geometry.
//语法
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_ClipByBox2D
Returns the portion of a geometry falling within a rectangle.
//语法
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_ConcaveHull
The concave hull of a geometry represents a possibly concave geometry that encloses all geometries within
the set. You can think of it as shrink wrapping.
//语法
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
Computes the convex hull of a geometry.
//语法
geometry ST_ConvexHull(geometry geomA);
//示例
SELECT ST_AsText(ST_ConvexHull(
ST_Collect(
ST_GeomFromText('MULTILINESTRING((100 190,10 8),(150 10, 20 30))'),
ST_GeomFromText('MULTIPOINT(50 5, 150 30, 50 10, 10 10)')
)) );
---st_astext--
POLYGON((50 5,10 8,10 10,100 190,150 30,150 10,50 5))
--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
Converts a CIRCULARSTRING/CURVEPOLYGON/MULTISURFACE to a 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,
PostGIS 3.0.5dev Manual 338 / 841
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
Return a Delaunay triangulation around the given input points.
//语法
geometry ST_DelaunayTriangles(geometry g1, float tolerance, int4 flags);
//示例
-- our original geometry --
ST_Union(ST_GeomFromText('POLYGON((175 150, 20 40,
50 60, 125 100, 175 150))'),
ST_Buffer(ST_GeomFromText('POINT(110 170)'), 20)
)
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
Returns a geometry that represents that part of geometry A that does not intersect with geometry B.
//语法
geometry ST_Difference(geometry geomA, geometry geomB);
//示例
SELECT ST_AsText(
ST_Difference( 'LINESTRING(50 100, 50 200)'::geometry, 'LINESTRING(50 50, 50 150)'::geometry
)
);
st_astext
---------
LINESTRING(50 150,50 200)
ST_FlipCoordinates
Returns a version of the given geometry with X and Y axis flflipped. Useful for people who have built
latitude/longitude features and need to fifix them.
//语法
geometry ST_FlipCoordinates(geometry geom);
//示例
SELECT ST_AsEWKT(ST_FlipCoordinates(GeomFromEWKT('POINT(1 2)')));
st_asewkt
------------
POINT(2 1)
ST_GeneratePoints
Converts a polygon or multi-polygon into a multi-point composed of randomly location points within the
original areas.
//语法
geometry ST_GeneratePoints( g geometry , npoints integer );
geometry ST_GeneratePoints( g geometry , npoints integer , seed integer );
//示例
SELECT ST_GeneratePoints(geom, 12, 1996)
FROM (
SELECT ST_Buffer(
ST_GeomFromText( 'LINESTRING(50 50,150 ←-
150,150 50)'),
10, 'endcap=round join= ←-
round') AS geom
) AS s;
ST_GeometricMedian
Returns the geometric median of a MultiPoint.
//语法
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_Intersection
(T) Returns a geometry that represents the shared portion of geomA and geomB.
//语法
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
SELECT ST_AsText(ST_Intersection('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )':: ←-
geometry));
st_astext
---------------
POINT(0 0)
ST_LineToCurve
Converts a LINESTRING/POLYGON to a 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,
PostGIS 3.0.5dev Manual 352 / 841
| 0.999999999999991 5
3,2.41421356237309 4.4142135623731
3,3 3 3)
(1 row)
ST_MakeValid
Attempts to make an invalid geometry valid without losing vertices.
//语法
geometry ST_MakeValid(geometry input);
ST_MemUnion
Attempts to make an invalid geometry valid without losing vertices.
//语法
geometry ST_MakeValid(geometry input);
ST_MinimumBoundingCircle
Returns the smallest circle polygon that can fully contain a geometry. Default uses 48 segments
per quarter circle.
//语法
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_OrientedEnvelope
Returns a minimum rotated rectangle enclosing a geometry.
//语法
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
Aggregate. Creates a GeometryCollection containing possible polygons formed from the constituent linework
of a set of geometries.
//语法
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_Node
Node a set of linestrings.
//语法
geometry ST_Node(geometry geom);
//示例
SELECT ST_AsText(
ST_Node('LINESTRINGZ(0 0 0, 10 10 10, 0 10 5, 10 0 3)'::geometry) ) As output;
output
-----------
MULTILINESTRING Z ((0 0 0,5 5 4.5),(5 5 4.5,10 10 10,0 10 5,5 5 4.5),(5 5 4.5,10 0 3))
ST_OffsetCurve
Return an offset line at a given distance and side from an input line. Useful for computing parallel lines
about a center line.
//语法
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_PointOnSurface
Returns a POINT guaranteed to lie on the surface.
//语法
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_RemoveRepeatedPoints
Returns a version of the given geometry with duplicated points removed.
//语法
geometry ST_RemoveRepeatedPoints(geometry geom, float8 tolerance);
ST_SharedPaths
Returns a collection containing paths shared by the two input linestrings/multilinestrings.
//语法
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
Toggle geometry coordinates between -180..180 and 0..360 ranges.
//语法
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_WrapX
Wrap a geometry around an X value.
//语法
geometry ST_WrapX(geometry geom, float8 wrap, float8 move);
//示例
-- Move all components of the given geometries whose bounding box
-- falls completely on the left of x=0 to +360
select ST_WrapX(the_geom, 0, 360);
-- Move all components of the given geometries whose bounding box
-- falls completely on the left of x=-30 to +360
select ST_WrapX(the_geom, -30, 360);
ST_Simplify
Returns a "simplifified" version of the given geometry using the Douglas-Peucker algorithm.
//语法
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;
np_before | np01_notbadcircle | np05_notquitecircle | np1_octagon | np10_triangle |
np100_geometrygoesaway
-----------+-------------------+--
49 | 33 | 17 | 9 | 4 | t
ST_SimplifyPreserveTopology
Returns a "simplifified" version of the given geometry using the Douglas-Peucker algorithm.
Will avoid creating derived geometries (polygons in particular) that are invalid.
//语法
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 |
ST_SimplifyVW
Returns a "simplifified" version of the given geometry using the Visvalingam-Whyatt algorithm.
//语法
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
Returns a "smoothed" version of the given geometry using the Chaikin algorithm.
//语法
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
Filters vertex points based on their m-value.
//语法
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
Sets the effective area for each vertex, storing the value in the M ordinate. A simplifified geometry can
then be generated by fifiltering on the M ordinate.
//语法
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
Returns a collection of geometries resulting by splitting a geometry.
//语法
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
Returns a geometry that represents the portions of A and B that do not intersect. It is called a symmetric
difference because ST_SymDifference(A,B) = ST_SymDifference(B,A).
//语法
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
Returns a set of geometry where no geometry in the set has more than the specifified number of vertices.
//语法
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_Union
Returns a geometry that represents the point set union of the Geometries.
//语法
geometry ST_Union(geometry set g1field);
geometry ST_Union(geometry g1, geometry g2);
geometry ST_Union(geometry[] g1_array);
//示例
SELECT stusps,
ST_Union(f.geom) as singlegeom
FROM sometable f
GROUP BY stusps
select ST_AsText(ST_Union('POINT(1 2)' :: geometry, 'POINT(-2 3)' :: geometry))
st_astext
----------
MULTIPOINT(-2 3,1 2)
select ST_AsText(ST_Union('POINT(1 2)' :: geometry, 'POINT(1 2)' :: geometry))
st_astext
----------
POINT(1 2)
ST_UnaryUnion
Like ST_Union, but working at the geometry component level.
//语法
geometry ST_UnaryUnion(geometry geom);
//示例
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_VoronoiLines
Returns the boundaries between the cells of the Voronoi diagram constructed from the vertices of a geome
try.
//语法
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
Returns the cells of the Voronoi diagram constructed from the vertices of a geometry.
//语法
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)))
Affifine Transformations（仿生变换）
ST_Affifine
Apply a 3D affifine transformation to a geometry.
//语法
geometry ST_Affine(geometry geomA, float a, float b, float c, float d, float e, float f, float g, float h, float i, float xoff, float yoff,
float zoff);
geometry ST_Affine(geometry geomA, float a, float b, float d, float e, float xoff, float yoff);
//示例
--Rotate a 3d line 180 degrees about the z axis. Note this is long-hand for doing
ST_Rotate();
SELECT ST_AsEWKT(ST_Affine(the_geom, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), 0,
0, 0, 1, 0, 0, 0)) As using_affine,
ST_AsEWKT(ST_Rotate(the_geom, pi())) As using_rotate
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 1 4 3)') As the_geom) As foo;
using_affine | using_rotate
-----------------------------+-----------------------------
LINESTRING(-1 -2 3,-1 -4 3) | LINESTRING(-1 -2 3,-1 -4 3)
(1 row)
--Rotate a 3d line 180 degrees in both the x and z axis
SELECT ST_AsEWKT(ST_Affine(the_geom, cos(pi()), -sin(pi()), 0, sin(pi()), cos(pi()), -sin(
pi()), 0, sin(pi()), cos(pi()), 0, 0, 0))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(1 2 3, 1 4 3)') As the_geom) As foo;
st_asewkt
-------------------------------
LINESTRING(-1 -2 -3,-1 -4 -3)
(1 row)
ST_Rotate
Rotates a geometry about an origin point.
//语法
geometry ST_Rotate(geometry geomA, float rotRadians);
geometry ST_Rotate(geometry geomA, float rotRadians, float x0, float y0);
geometry ST_Rotate(geometry geomA, float rotRadians, geometry pointOrigin);
//示例
SELECT ST_AsEWKT(ST_Rotate('LINESTRING (50 160, 50 50, 100 50)', pi()));
st_asewkt
---------------------------------------
LINESTRING(-50 -160,-50 -50,-100 -50)
(1 row)
ST_RotateX
Rotates a geometry about the X axis.
//语法
geometry ST_RotateX(geometry geomA, float rotRadians);
//示例
--Rotate a line 90 degrees along x-axis
SELECT ST_AsEWKT(ST_RotateX(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 1 1)'), pi()/2));
st_asewkt
---------------------------
LINESTRING(1 -3 2,1 -1 1)
ST_RotateY
Rotates a geometry about the Y axis.
//语法
geometry ST_RotateY(geometry geomA, float rotRadians);
//示例
--Rotate a line 90 degrees along y-axis
SELECT ST_AsEWKT(ST_RotateY(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 1 1)'), pi()/2));
st_asewkt
---------------------------
LINESTRING(3 2 -1,1 1 -1)
ST_RotateZ
ST_RotateZ — Rotates a geometry about the Z axis.
//语法
geometry ST_RotateZ(geometry geomA, float rotRadians);
//示例
--Rotate a line 90 degrees along z-axis
SELECT ST_AsEWKT(ST_RotateZ(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 1 1)'), pi()/2));
st_asewkt
---------------------------
LINESTRING(-2 1 3,-1 1 1)
--Rotate a curved circle around z-axis
SELECT ST_AsEWKT(ST_RotateZ(the_geom, pi()/2))
FROM (SELECT ST_LineToCurve(ST_Buffer(ST_GeomFromText('POINT(234 567)'), 3)) As the_geom) ←-
As foo;
st_asewkt
---------------------------------------------------------------------------------------------------------------------------- ←-
CURVEPOLYGON(CIRCULARSTRING(-567 237,-564.87867965644 236.12132034356,-564 ←-
234,-569.12132034356 231.87867965644,-567 237))
ST_Scale
Scales a geometry by given factors.
//语法
geometry ST_Scale(geometry geomA, float XFactor, float YFactor, float ZFactor);
geometry ST_Scale(geometry geomA, float XFactor, float YFactor);
geometry ST_Scale(geometry geom, geometry factor);
geometry ST_Scale(geometry geom, geometry factor, geometry origin);
//示例
--Version 1: scale X, Y, Z
SELECT ST_AsEWKT(ST_Scale(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 1 1)'), 0.5, 0.75, 0.8));
st_asewkt
--------------------------------------
LINESTRING(0.5 1.5 2.4,0.5 0.75 0.8)
--Version 2: Scale X Y
SELECT ST_AsEWKT(ST_Scale(ST_GeomFromEWKT('LINESTRING(1 2 3, 1 1 1)'), 0.5, 0.75));
st_asewkt
----------------------------------
LINESTRING(0.5 1.5 3,0.5 0.75 1)
--Version 3: Scale X Y Z M
SELECT ST_AsEWKT(ST_Scale(ST_GeomFromEWKT('LINESTRING(1 2 3 4, 1 1 1 1)'),
ST_MakePoint(0.5, 0.75, 2, -1)));
st_asewkt
----------------------------------------
LINESTRING(0.5 1.5 6 -4,0.5 0.75 2 -1)
--Version 4: Scale X Y using false origin
SELECT ST_AsText(ST_Scale('LINESTRING(1 1, 2 2)', 'POINT(2 2)', 'POINT(1 1)'::geometry));
st_astext
---------------------
LINESTRING(1 1,3 3)
ST_Translate
Translates a geometry by given offsets.
//语法
geometry ST_Translate(geometry g1, float deltax, float deltay);
geometry ST_Translate(geometry g1, float deltax, float deltay, float deltaz);
//示例
SELECT ST_AsText(ST_Translate(ST_GeomFromText('POINT(-71.01 42.37)',4326),1,0)) As ←-
wgs_transgeomtxt;
wgs_transgeomtxt
---------------------
POINT(-70.01 42.37)
Clustering Functions（聚类功能）
ST_ClusterDBSCAN
Window function that returns a cluster id for each input geometry using the DBSCAN algorithm.
//语法
integer ST_ClusterDBSCAN(geometry winset geom, float8 eps, integer minpoints);
//示例
SELECT name, ST_ClusterDBSCAN(geom, eps
:= 50, minpoints := 2) over () AS cid
FROM boston_polys
WHERE name > '' AND building > ''
AND ST_DWithin(geom,
ST_Transform(
ST_GeomFromText('POINT
(-71.04054 42.35141)', 4326), 26986),
500);
SELECT cid, ST_Collect(geom) AS cluster_geom, array_agg(parcel_id) AS ids_in_cluster FROM (
SELECT parcel_id, ST_ClusterDBSCAN(geom, eps := 0.5, minpoints := 5) over () AS cid, ←-
geom
FROM parcels) sq
GROUP BY cid;
ST_ClusterIntersecting
Aggregate function that clusters the input geometries into connected sets.
//语法
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;
--result
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
Window function that returns a cluster id for each input geometry using the K-means algorithm.
//语法
integer ST_ClusterKMeans(geometry winset geom, integer number_of_clusters);
//示例
-- Partitioning parcel clusters by type
SELECT ST_ClusterKMeans(geom,3) over (PARTITION BY type) AS cid, parcel_id, type
FROM parcels;
-- result
cid | parcel_id | type
-----+-----------+-------------
1 | 005 | commercial
1 | 003 | commercial
2 | 007 | commercial
0 | 001 | commercial
1 | 004 | residential
0 | 002 | residential
2 | 006 | residential
(7 rows)
ST_ClusterWithin
Aggregate function that clusters the input geometries by separation distance.
//语法
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;
--result
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))
Bounding Box Functions（边界分析函数）
Box2D
Returns a BOX2D representing the 2D extent of the geometry.
//语法
box2d Box2D(geometry geomA);
//示例
SELECT Box2D(ST_GeomFromText('LINESTRING(1 2, 3 4, 5 6)'));
box2d
---------
BOX(1 2,5 6)
SELECT Box2D(ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)') ←-
);
box2d
--------
BOX(220186.984375 150406,220288.25 150506.140625)
Box3D
Returns a BOX3D representing the 3D extent of the geometry.
//语法
box3d Box3D(geometry geomA);
//示例
SELECT Box3D(ST_GeomFromEWKT('LINESTRING(1 2 3, 3 4 5, 5 6 5)'));
Box3d
---------
BOX3D(1 2 3,5 6 5)
SELECT Box3D(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 1,220227 ←-
150406 1)'));
Box3d
--------
BOX3D(220227 150406 1,220268 150415 1)
ST_EstimatedExtent
Return the ’estimated’ extent of a spatial table.
//语法
box2d ST_EstimatedExtent(text schema_name, text table_name, text geocolumn_name, boolean parent_only);
box2d ST_EstimatedExtent(text schema_name, text table_name, text geocolumn_name);
box2d ST_EstimatedExtent(text table_name, text geocolumn_name);
//示例
SELECT ST_EstimatedExtent('ny', 'edges', 'the_geom');
--result--
BOX(-8877653 4912316,-8010225.5 5589284)
SELECT ST_EstimatedExtent('feature_poly', 'the_geom');
--result--
BOX(-124.659652709961 24.6830825805664,-67.7798080444336 49.0012092590332)
ST_Expand
Returns a bounding box expanded from another bounding box or a geometry.
//语法
geometry ST_Expand(geometry geom, float units_to_expand);
geometry ST_Expand(geometry geom, float dx, float dy, float dz=0, float dm=0);
box2d ST_Expand(box2d box, float units_to_expand);
box2d ST_Expand(box2d box, float dx, float dy);
box3d ST_Expand(box3d box, float units_to_expand);
box3d ST_Expand(box3d box, float dx, float dy, float dz=0);
//示例
--10 meter expanded box around bbox of a linestring
SELECT CAST(ST_Expand(ST_GeomFromText('LINESTRING(2312980 110676,2312923 110701,2312892
110714)', 2163),10) As box2d);
st_expand
------------------------------------
BOX(2312882 110666,2312990 110724)
--10 meter expanded 3D box of a 3D box
SELECT ST_Expand(CAST('BOX3D(778783 2951741 1,794875 2970042.61545891 10)' As box3d),10)
st_expand
------------------------------------------
BOX3D(778773 2951731 -9,794885 2970052.61545891 20)
--10 meter geometry astext rep of a expand box around a point geometry
SELECT ST_AsEWKT(ST_Expand(ST_GeomFromEWKT('SRID=2163;POINT(2312980 110676)'),10));
st_asewkt
------------------------------------
SRID=2163;POLYGON((2312970 110666,2312970 110686,2312990 110686,2312990 110666,2312970
110666))
ST_Extent
an aggregate function that returns the bounding box that bounds rows of geometries.
//语法
box2d ST_Extent(geometry set geomfield);
//示例
SELECT ST_Extent(the_geom) as bextent FROM sometable;
st_bextent
------------------------------------
BOX(739651.875 2908247.25,794875.8125 2970042.75)
--Return extent of each category of geometries
SELECT ST_Extent(the_geom) as bextent
FROM sometable
GROUP BY category ORDER BY category;
bextent | name
----------------------------------------------------+----------------
BOX(778783.5625 2951741.25,794875.8125 2970042.75) | A
BOX(751315.8125 2919164.75,765202.6875 2935417.25) | B
BOX(739651.875 2917394.75,756688.375 2935866) | C
--Force back into a geometry
-- and render the extended text representation of that geometry
SELECT ST_SetSRID(ST_Extent(the_geom),2249) as bextent FROM sometable;
bextent
--------------------------------------------------------------------------------
SRID=2249;POLYGON((739651.875 2908247.25,739651.875 2970042.75,794875.8125 2970042.75,
794875.8125 2908247.25,739651.875 2908247.25))
ST_3DExtent
an aggregate function that returns the 3D bounding box that bounds rows of geometries.
//语法
box3d ST_3DExtent(geometry set geomfield);
//示例
SELECT ST_3DExtent(foo.the_geom) As b3extent
FROM (SELECT ST_MakePoint(x,y,z) As the_geom
FROM generate_series(1,3) As x
CROSS JOIN generate_series(1,2) As y
CROSS JOIN generate_series(0,2) As Z) As foo;
b3extent
--------------------
BOX3D(1 1 0,3 2 2)
--Get the extent of various elevated circular strings
SELECT ST_3DExtent(foo.the_geom) As b3extent
FROM (SELECT ST_Translate(ST_Force_3DZ(ST_LineToCurve(ST_Buffer(ST_MakePoint(x,y),1))),0,0, ←- z) As the_geom
FROM generate_series(1,3) As x
CROSS JOIN generate_series(1,2) As y
CROSS JOIN generate_series(0,2) As Z) As foo;
b3extent
--------------------
BOX3D(1 0 0,4 2 2)
ST_MakeBox2D
Creates a BOX2D defifined by two 2D point geometries.
//语法
box2d ST_MakeBox2D(geometry pointLowLeft, geometry pointUpRight);
//示例
--Return all features that fall reside or partly reside in a US national atlas coordinate ←-
bounding box
--It is assumed here that the geometries are stored with SRID = 2163 (US National atlas
equal area)
SELECT feature_id, feature_name, the_geom
FROM features
WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-989502.1875, 528439.5625),
ST_Point(-987121.375 ,529933.1875)),2163)
ST_3DMakeBox
Creates a BOX3D defifined by two 3D point geometries.
//语法
box3d ST_3DMakeBox(geometry point3DLowLeftBottom, geometry point3DUpRightTop);
//示例
SELECT ST_3DMakeBox(ST_MakePoint(-989502.1875, 528439.5625, 10),
ST_MakePoint(-987121.375 ,529933.1875, 10)) As abb3d
--bb3d--
--------
BOX3D(-989502.1875 528439.5625 10,-987121.375 529933.1875 10)
ST_XMax
Returns the X maxima of a 2D or 3D bounding box or a geometry.
//语法
float ST_XMax(box3d aGeomorBox2DorBox3D);
//示例
SELECT ST_XMax('BOX3D(1 2 3, 4 5 6)');
st_xmax
-------
4
SELECT ST_XMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_xmax
-------
5
SELECT ST_XMax(CAST('BOX(-3 2, 3 4)' As box2d));
st_xmax
-------
3
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a
BOX3D
SELECT ST_XMax('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_XMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227
150406 3)'));
st_xmax
--------
220288.248780547
ST_XMin
Returns the X minima of a 2D or 3D bounding box or a geometry.
//语法
float ST_XMin(box3d aGeomorBox2DorBox3D);
//示例
SELECT ST_XMin('BOX3D(1 2 3, 4 5 6)');
st_xmin
-------
1
SELECT ST_XMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_xmin
-------
1
SELECT ST_XMin(CAST('BOX(-3 2, 3 4)' As box2d));
st_xmin
-------
-3
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a
BOX3D
SELECT ST_XMin('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_XMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227
150406 3)'));
st_xmin
--------
220186.995121892
ST_YMax
Returns the Y maxima of a 2D or 3D bounding box or a geometry.
//语法
float ST_YMax(box3d aGeomorBox2DorBox3D);
//示例
SELECT ST_YMax('BOX3D(1 2 3, 4 5 6)');
st_ymax
-------
5
SELECT ST_YMax(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_ymax
-------
6
SELECT ST_YMax(CAST('BOX(-3 2, 3 4)' As box2d));
st_ymax
-------
4
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a
BOX3D
SELECT ST_YMax('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_YMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227
150406 3)'));
st_ymax
--------
150506.126829327
ST_YMin
Returns the Y minima of a 2D or 3D bounding box or a geometry.
//语法
float ST_YMin(box3d aGeomorBox2DorBox3D);
//示例
SELECT ST_YMin('BOX3D(1 2 3, 4 5 6)');
st_ymin
-------
2
SELECT ST_YMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'));
st_ymin
-------
3
SELECT ST_YMin(CAST('BOX(-3 2, 3 4)' As box2d));
st_ymin
-------
2
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a ←-
BOX3D
SELECT ST_YMin('LINESTRING(1 3, 5 6)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_YMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227
150406 3)'));
st_ymin
--------
150406
ST_ZMax
Returns the Z maxima of a 2D or 3D bounding box or a geometry.
//语法
float ST_ZMax(box3d aGeomorBox2DorBox3D);
//示例
SELECT ST_ZMax('BOX3D(1 2 3, 4 5 6)');
st_zmax
-------
6
SELECT ST_ZMax(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
st_zmax
-------
7
SELECT ST_ZMax('BOX3D(-3 2 1, 3 4 1)' );
st_zmax
-------
1
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a
BOX3D
SELECT ST_ZMax('LINESTRING(1 3 4, 5 6 7)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_ZMax(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227
150406 3)'));
st_zmax
--------
3
ST_ZMin
Returns the Z minima of a 2D or 3D bounding box or a geometry.
//语法
float ST_ZMin(box3d aGeomorBox2DorBox3D);
//示例
SELECT ST_ZMin('BOX3D(1 2 3, 4 5 6)');
st_zmin
-------
3
SELECT ST_ZMin(ST_GeomFromEWKT('LINESTRING(1 3 4, 5 6 7)'));
st_zmin
-------
4
SELECT ST_ZMin('BOX3D(-3 2 1, 3 4 1)' );
st_zmin
-------
1
--Observe THIS DOES NOT WORK because it will try to autocast the string representation to a ←-
BOX3D
SELECT ST_ZMin('LINESTRING(1 3 4, 5 6 7)');
--ERROR: BOX3D parser - doesn't start with BOX3D(
SELECT ST_ZMin(ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 ←-
150406 3)'));
st_zmin
--------
1
Linear Referencing（线性参考）
ST_LineInterpolatePoint
Returns a point interpolated along a line. Second argument is a flfloat8 between 0 and 1 representing
fraction of total length of linestring the point has to be located.
//语法
geometry ST_LineInterpolatePoint(geometry a_linestring, float8 a_fraction);
//示例
--Return point 20% along 2d line
SELECT ST_AsEWKT(ST_LineInterpolatePoint(the_line, 0.20))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(25 50, 100 125, 150 190)') as the_line) As foo;
st_asewkt
----------------
POINT(51.5974135047432 76.5974135047432)
ST_3DLineInterpolatePoint
Returns a point interpolated along a line in 3D. Second argument is a flfloat8 between 0 and 1
representing fraction of total length of linestring the point has to be located.
//语法
geometry ST_LineInterpolatePoint(geometry a_linestring, float8 a_fraction);
//示例
SELECT ST_AsEWKT(ST_3DLineInterpolatePoint(the_line, 0.20))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(25 50 70, 100 125 90, 150 190 200)') as the_line) As foo;
st_asewkt
----------------
POINT(59.0675892910822 84.0675892910822 79.0846904776219)
ST_LineInterpolatePoints
Returns one or more points interpolated along a line.
//语法
geometry ST_LineInterpolatePoints(geometry a_linestring, float8 a_fraction, boolean repeat);
//示例
--Return points each 20% along a 2D line
SELECT ST_AsText(ST_LineInterpolatePoints('LINESTRING(25 50, 100 125, 150 190)', 0.20))
st_astext
----------------
MULTIPOINT(51.5974135047432 76.5974135047432,78.1948270094864
103.194827009486,104.132163186446 130.37181214238,127.066081593223 160.18590607119,150
190)
ST_LineLocatePoint
Returns a flfloat between 0 and 1 representing the location of the closest point on LineString to the given
Point, as a fraction of total 2d line length.
//语法
float8 ST_LineLocatePoint(geometry a_linestring, geometry a_point);
//示例
--Rough approximation of finding the street number of a point along the street
--Note the whole foo thing is just to generate dummy data that looks
--like house centroids and street
--We use ST_DWithin to exclude
--houses too far away from the street to be considered on the street
SELECT ST_AsText(house_loc) As as_text_house_loc,
startstreet_num +
CAST( (endstreet_num - startstreet_num) * ST_LineLocatePoint(street_line, house_loc) As integer) As street_num
FROM
(SELECT ST_GeomFromText('LINESTRING(1 2, 3 4)') As street_line,
ST_MakePoint(x*1.01,y*1.03) As house_loc, 10 As startstreet_num,
20 As endstreet_num
FROM generate_series(1,3) x CROSS JOIN generate_series(2,4) As y)
As foo
WHERE ST_DWithin(street_line, house_loc, 0.2);
as_text_house_loc | street_num
-------------------+------------
POINT(1.01 2.06) | 10
POINT(2.02 3.09) | 15
POINT(3.03 4.12) | 20
--find closest point on a line to a point or other geometry
SELECT ST_AsText(ST_LineInterpolatePoint(foo.the_line, ST_LineLocatePoint(foo.the_line, ←-
ST_GeomFromText('POINT(4 3)'))))
FROM (SELECT ST_GeomFromText('LINESTRING(1 2, 4 5, 6 7)') As the_line) As foo;
st_astext
----------------
POINT(3 4)
ST_LineSubstring
Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d
length. Second and third arguments are flfloat8 values between 0 and 1.
//语法
geometry ST_LineSubstring(geometry a_linestring, float8 startfraction, float8 endfraction);
//示例
--Return the approximate 1/3 mid-range part of a linestring
SELECT ST_AsText(ST_Line_SubString(ST_GeomFromText('LINESTRING(25 50, 100 125, 150 190)'), ←-
0.333, 0.666));
st_astext
------------------------------------------------------------------------------------------------ ←-
LINESTRING(69.2846934853974 94.2846934853974,100 125,111.700356260683 140.210463138888)
--The below example simulates a while loop in
--SQL using PostgreSQL generate_series() to cut all
--linestrings in a table to 100 unit segments
-- of which no segment is longer than 100 units
-- units are measured in the SRID units of measurement
-- It also assumes all geometries are LINESTRING or contiguous MULTILINESTRING
--and no geometry is longer than 100 units*10000
--for better performance you can reduce the 10000
--to match max number of segments you expect
SELECT field1, field2, ST_LineSubstring(the_geom, 100.00*n/length,
CASE
WHEN 100.00*(n+1) < length THEN 100.00*(n+1)/length
ELSE 1
END) As the_geom
FROM
(SELECT sometable.field1, sometable.field2,
ST_LineMerge(sometable.the_geom) AS the_geom,
ST_Length(sometable.the_geom) As length
FROM sometable
) AS t
CROSS JOIN generate_series(0,10000) AS n
WHERE n*100.00/length < 1;
ST_LocateAlong
Return a derived geometry collection value with elements that match the specifified measure. Polygonal
elements are not supported.
//语法
geometry ST_LocateAlong(geometry ageom_with_measure, float8 a_measure, float8 offset);
//示例
SELECT ST_AsText(the_geom)
FROM
(SELECT ST_LocateAlong(
ST_GeomFromText('MULTILINESTRINGM((1 2 3, 3 4 2, 9 4 3),
(1 2 3, 5 4 5))'),3) As the_geom) As foo;
st_asewkt
-----------------------------------------------------------
MULTIPOINT M (1 2 3)
ST_LocateBetween
Return a derived geometry collection value with elements that match the specifified range of measures
inclusively.
//语法
geometry ST_LocateBetween(geometry geom, float8 measure_start, float8 measure_end, float8 offset);
//示例
SELECT ST_AsText(the_geom)
FROM (
SELECT ST_LocateBetween( 'MULTILINESTRING M ((1 2 3, 3 4 2, 9 4 3),(1 2 3, 5 4 5))'),
1.5,
3 ) as the_geom
) As foo;
ST_LocateBetweenElevations
Return a derived geometry (collection) value with elements that intersect the specifified range of
elevations inclusively.
//语法
geometry ST_LocateBetweenElevations(geometry geom, float8 elevation_start, float8 elevation_end);
//示例
SELECT ST_AsEWKT(ST_LocateBetweenElevations(
ST_GeomFromEWKT('LINESTRING(1 2 3, 4 5 6)'), 2, 4)) As ewelev;
ewelev
----------------------------------------------------------------
MULTILINESTRING((1 2 3,2 3 4))
SELECT ST_AsEWKT(ST_LocateBetweenElevations('LINESTRING(1 2 6, 4 5 -1, 7 8 9)', 6, 9)) As ←-
ewelev;
ST_InterpolatePoint
Return the value of the measure dimension of a geometry at the point closed to the provided point.
//语法
float8 ST_InterpolatePoint(geometry line, geometry point);
//示例
SELECT ST_InterpolatePoint('LINESTRING M (0 0 0, 10 0 20)', 'POINT(5 5)');
st_interpolatepoint
---------------------
10
ST_AddMeasure
Return a derived geometry with measure elements linearly interpolated between the start and end points.
//语法
geometry ST_AddMeasure(geometry geom_mline, float8 measure_start, float8 measure_end);
//示例
SELECT ST_AsText(ST_AddMeasure(
ST_GeomFromEWKT('LINESTRING(1 0, 2 0, 4 0)'),1,4)) As ewelev;
ewelev
--------------------------------
LINESTRINGM(1 0 1,2 0 2,4 0 4)
SELECT ST_AsText(ST_AddMeasure(
ST_GeomFromEWKT('LINESTRING(1 0 4, 2 0 4, 4 0 4)'),10,40)) As ewelev;
ewelev
----------------------------------------
LINESTRING(1 0 4 10,2 0 4 20,4 0 4 40)
SELECT ST_AsText(ST_AddMeasure(
ST_GeomFromEWKT('LINESTRINGM(1 0 4, 2 0 4, 4 0 4)'),10,40)) As ewelev;
ewelev
----------------------------------------
LINESTRINGM(1 0 10,2 0 20,4 0 40)
SELECT ST_AsText(ST_AddMeasure(
ST_GeomFromEWKT('MULTILINESTRINGM((1 0 4, 2 0 4, 4 0 4),(1 0 4, 2 0 4, 4 0 4))'),10,70)) As ←-
ewelev;
ewelev
-----------------------------------------------------------------
MULTILINESTRINGM((1 0 10,2 0 20,4 0 40),(1 0 40,2 0 50,4 0 70))
Trajectory Functions（轨迹函数）
ST_IsValidTrajectory
Returns true if the geometry is a valid trajectory.
//语法
boolean ST_IsValidTrajectory(geometry line);
//示例
-- A valid trajectory
SELECT ST_IsValidTrajectory(ST_MakeLine(
ST_MakePointM(0,0,1),
ST_MakePointM(0,1,2))
);
t
-- An invalid trajectory
SELECT ST_IsValidTrajectory(ST_MakeLine(ST_MakePointM(0,0,1), ST_MakePointM(0,1,0)));
NOTICE: Measure of vertex 1 (0) not bigger than measure of vertex 0 (1)
st_isvalidtrajectory
----------------------
f
ST_ClosestPointOfApproach
Returns the measure at which points interpolated along two trajectories are closest.
//语法
float8 ST_ClosestPointOfApproach(geometry track1, geometry track2);
//示例
-- Return the time in which two objects moving between 10:00 and 11:00
-- are closest to each other and their distance at that point
WITH inp AS ( SELECT
ST_AddMeasure('LINESTRING Z (0 0 0, 10 0 5)'::geometry,
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz) ) a,
ST_AddMeasure('LINESTRING Z (0 2 10, 12 1 2)'::geometry,
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz) ) b
), cpa AS (
SELECT ST_ClosestPointOfApproach(a,b) m FROM inp
), points AS (
SELECT ST_Force3DZ(ST_GeometryN(ST_LocateAlong(a,m),1)) pa,
ST_Force3DZ(ST_GeometryN(ST_LocateAlong(b,m),1)) pb
FROM inp, cpa
)
SELECT to_timestamp(m) t,
ST_Distance(pa,pb) distance
FROM points, cpa;t | distance
-------------------------------+------------------
2015-05-26 10:45:31.034483+02 | 1.96036833151395
ST_DistanceCPA
Returns the distance between the closest point of approach of two trajectories.
//语法
float8 ST_DistanceCPA(geometry track1, geometry track2);
//示例
-- Return the minimum distance of two objects moving between 10:00 and 11:00
WITH inp AS ( SELECT
ST_AddMeasure('LINESTRING Z (0 0 0, 10 0 5)'::geometry,
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz) ) a,
ST_AddMeasure('LINESTRING Z (0 2 10, 12 1 2)'::geometry,
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz) ) b )
SELECT ST_DistanceCPA(a,b) distance FROM inp;
distance
------------------
1.96036833151395
ST_CPAWithin
Returns true if the closest point of approach of two trajectories is within the specifified distance.
//语法
float8 ST_CPAWithin(geometry track1, geometry track2, float8 maxdist);
//示例
WITH inp AS ( SELECT
ST_AddMeasure('LINESTRING Z (0 0 0, 10 0 5)'::geometry,
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz) ) a,
ST_AddMeasure('LINESTRING Z (0 2 10, 12 1 2)'::geometry,
extract(epoch from '2015-05-26 10:00'::timestamptz),
extract(epoch from '2015-05-26 11:00'::timestamptz) ) b )
SELECT ST_CPAWithin(a,b,2), ST_DistanceCPA(a,b) distance FROM inp;
st_cpawithin | distance
--------------+------------------
t | 1.96521473776207
SFCGAL Functions
SFCGAL是一个围绕CGAL的C++封装库，目的是支持ISO 191007:2013和OGC简单特征的三维操作。
postgis_sfcgal_version
Returns the version of SFCGAL in use.
//语法
text postgis_sfcgal_version(void);
//示例
text postgis_sfcgal_version(void);
ST_Extrude
Extrude a surface to a related volume.
//语法
geometry ST_Extrude(geometry geom, float x, float y, float z);
//示例
SELECT ST_Buffer(ST_GeomFromText('POINT ←-
(100 90)'),
50, 'quad_segs=2'),0,0,30);
ST_StraightSkeleton
Compute a straight skeleton from a geometry.
//语法
geometry ST_StraightSkeleton(geometry geom);
//示例
SELECT ST_StraightSkeleton(ST_GeomFromText('POLYGON (( 190 190, 10 190, 10 10, 190 10, 190 ←-
20, 160 30, 60 30, 60 130, 190 140, 190 190 ))'));
ST_ApproximateMedialAxis
Compute the approximate medial axis of an areal geometry.
//语法
geometry ST_ApproximateMedialAxis(geometry geom);
//示例
SELECT ST_ApproximateMedialAxis(ST_GeomFromText('POLYGON (( 190 190, 10 190, 10 10, 190 10,
190 20, 160 30, 60 30, 60 130, 190 140, 190 190 ))'));
ST_IsPlanar
Check if a surface is or not planar.
//语法
boolean ST_IsPlanar(geometry geom);
ST_Orientation
Determine surface orientation.
//语法
integer ST_Orientation(geometry geom);
ST_ForceLHR
Force LHR orientation.
//语法
geometry ST_ForceLHR(geometry geom);
ST_MinkowskiSum
Performs Minkowski sum.
//语法
geometry ST_MinkowskiSum(geometry geom1, geometry geom2);
//示例
SELECT ST_MinkowskiSum(line, circle))
FROM (SELECT
ST_MakeLine(ST_MakePoint(10, 10),ST_MakePoint(100, 100)) As line,
ST_Buffer(ST_GeomFromText('POINT(50 50)'), 30) As circle) As foo;
-- wkt --
MULTIPOLYGON(((30 59.9999999999999,30.5764415879031 54.1472903395161,32.2836140246614 ←-
PostGIS 3.0.5dev Manual 432 / 841
48.5194970290472,35.0559116309237 43.3328930094119,38.7867965644036
38.7867965644035,43.332893009412 35.0559116309236,48.5194970290474
32.2836140246614,54.1472903395162 30.5764415879031,60.0000000000001 30,65.8527096604839
30.5764415879031,71.4805029709527 32.2836140246614,76.6671069905881
35.0559116309237,81.2132034355964 38.7867965644036,171.213203435596
128.786796564404,174.944088369076 133.332893009412,177.716385975339
138.519497029047,179.423558412097 144.147290339516,180 150,179.423558412097
155.852709660484,177.716385975339 161.480502970953,174.944088369076
166.667106990588,171.213203435596 171.213203435596,166.667106990588 174.944088369076,
161.480502970953 177.716385975339,155.852709660484 179.423558412097,150
180,144.147290339516 179.423558412097,138.519497029047 177.716385975339,133.332893009412
174.944088369076,128.786796564403 171.213203435596,38.7867965644035
81.2132034355963,35.0559116309236 76.667106990588,32.2836140246614
71.4805029709526,30.5764415879031 65.8527096604838,30 59.9999999999999)))
ST_ConstrainedDelaunayTriangles
Return a constrained Delaunay triangulation around the given input geometry.
//语法
geometry ST_ConstrainedDelaunayTriangles(geometry g1);
//示例
select ST_ConstrainedDelaunayTriangles(
ST_Union('POLYGON((175 150, ←-
20 40, 50 60, 125 100, 175 150))'::geometry,
ST_Buffer('POINT ←-
(110 170)'::geometry, 20)
)
);
ST_3DIntersection
Perform 3D intersection.
//语法
geometry ST_3DIntersection(geometry geom1, geometry geom2);
//示例
SELECT ST_Extrude(ST_Buffer( ←-
ST_GeomFromText('POINT(100 90)'),
50, 'quad_segs=2'),0,0,30) AS geom1,
ST_Extrude(ST_Buffer( ←-
ST_GeomFromText('POINT(80 80)'),
50, 'quad_segs=1'),0,0,30) AS geom2;
ST_3DDifference
— Perform 3D difference.
//语法
geometry ST_3DDifference(geometry geom1, geometry geom2);
//示例
SELECT ST_Extrude(ST_Buffer( ←-
ST_GeomFromText('POINT(100 90)'),
50, 'quad_segs=2'),0,0,30) AS geom1,
ST_Extrude(ST_Buffer( ←-
ST_GeomFromText('POINT(80 80)'),
50, 'quad_segs=1'),0,0,30) AS geom2;
ST_3DUnion
Perform 3D union.
//语法
geometry ST_3DUnion(geometry geom1, geometry geom2);
//示例
SELECT ST_Extrude(ST_Buffer( ←-
ST_GeomFromText('POINT(100 90)'),
50, 'quad_segs=2'),0,0,30) AS geom1,
ST_Extrude(ST_Buffer( ←-
ST_GeomFromText('POINT(80 80)'),
50, 'quad_segs=1'),0,0,30) AS geom2;
ST_3DArea
Computes area of 3D surface geometries. Will return 0 for solids.
//语法
floatST_3DArea(geometry geom1);
//示例
SELECT ST_3DArea(geom) As cube_surface_area,
ST_3DArea(ST_MakeSolid(geom)) As solid_surface_area
FROM (SELECT 'POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),
((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )'::geometry) As f(geom);
cube_surface_area | solid_surface_area
-------------------+--------------------
6 | 0
ST_Tesselate
Perform surface Tesselation of a polygon or polyhedralsurface and returns as a TIN or collection of TINS.
//语法
geometry ST_Tesselate(geometry geom);
//示例
SELECT
ST_Tesselate('POLYGON (( 10 190, ←-
10 70, 80 70, 80 130, 50 160, 120 160, 120 190, 10 190 ))'::geometry);
ST_AsText output
TIN(((80 130,50 160,80 70,80 130)),((50
160,10 190,10 70,50 160)),
((80 70,50 160,10 70,80 70))
,((120 160,120 190,50 160,120 160)),
((120 190,10 190,50 160,120 190)))
ST_Volume
Computes the volume of a 3D solid. If applied to surface (even closed) geometries will return 0.
//语法
float ST_Volume(geometry geom1);
//示例
SELECT ST_Volume(geom) As cube_surface_vol,
ST_Volume(ST_MakeSolid(geom)) As solid_surface_vol
FROM (SELECT 'POLYHEDRALSURFACE( ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),
((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)) )'::geometry) As f(geom);
cube_surface_vol | solid_surface_vol
------------------+-------------------
0 | 1
ST_MakeSolid
Cast the geometry into a solid. No check is performed. To obtain a valid solid, the input geometry must be a
closed Polyhedral Surface or a closed TIN.
//语法
geometryST_MakeSolid(geometry geom1);
ST_IsSolid
Test if the geometry is a solid. No validity check is performed.
//语法
booleanST_IsSolid(geometry geom1);
Version Functions
PostGIS_Extensions_Upgrade
Packages and upgrades postgis extensions (e.g. postgis_raster, postgis_topology, postgis_sfcgal)
to latest available version.
//语法
text PostGIS_Extensions_Upgrade();
//示例
SELECT PostGIS_Extensions_Upgrade();
NOTICE: Packaging extension postgis
NOTICE: Packaging extension postgis_raster
NOTICE: Packaging extension postgis_sfcgal
NOTICE: Extension postgis_topology is not available or not packagable for some reason
NOTICE: Extension postgis_tiger_geocoder is not available or not packagable for some ←-
reason
postgis_extensions_upgrade
-------------------------------------------------------------------
Upgrade completed, run SELECT postgis_full_version(); for details
(1 row)
PostGIS_Full_Version
Reports full postgis version and build confifiguration infos.
//语法
text PostGIS_Full_Version();
//示例
SELECT PostGIS_Full_Version();
postgis_full_version
----------------------------------------------------------------------------------
POSTGIS="3.0.0dev r17211" [EXTENSION] PGSQL="110" GEOS="3.8.0dev-CAPI-1.11.0 df24b6bb"
SFCGAL="1.3.6" PROJ="Rel. 5.2.0, September 15th, 2018"
GDAL="GDAL 2.3.2, released 2018/09/21" LIBXML="2.9.9" LIBJSON="0.13.1" LIBPROTOBUF="1.3.1"
WAGYU="0.4.3 (Internal)" TOPOLOGY RASTER
(1 row)
PostGIS_GEOS_Version
Returns the version number of the GEOS library.
//语法
text PostGIS_GEOS_Version();
//示例
SELECT PostGIS_GEOS_Version();
postgis_geos_version
----------------------
3.1.0-CAPI-1.5.0
(1 row)
PostGIS_Liblwgeom_Version
Returns the version number of the liblwgeom library. This should match the version of PostGIS.
//语法
text PostGIS_Liblwgeom_Version();
//示例
SELECT PostGIS_Liblwgeom_Version();
postgis_liblwgeom_version
--------------------------
2.3.3 r15473
(1 row)
PostGIS_LibXML_Version
Returns the version number of the libxml2 library.
//语法
text PostGIS_LibXML_Version();
//示例
SELECT PostGIS_LibXML_Version();
postgis_libxml_version
----------------------
2.7.6
(1 row)
PostGIS_Lib_Build_Date
Returns build date of the PostGIS library.
//语法
text PostGIS_Lib_Build_Date();
//示例
SELECT PostGIS_Lib_Build_Date();
postgis_lib_build_date
------------------------
2008-06-21 17:53:21
(1 row)
PostGIS_Lib_Version
Returns the version number of the PostGIS library.
//语法
text PostGIS_Lib_Version();
//示例
SELECT PostGIS_Lib_Version();
postgis_lib_version
---------------------
1.3.3
(1 row)
PostGIS_PROJ_Version
Returns the version number of the PROJ4 library.
//语法
text PostGIS_PROJ_Version();
//示例
SELECT PostGIS_PROJ_Version();
postgis_proj_version
-------------------------
Rel. 4.4.9, 29 Oct 2004
(1 row)
PostGIS_Wagyu_Version
Returns the version number of the internal Wagyu library.
//语法
text PostGIS_Wagyu_Version();
//示例
SELECT PostGIS_Wagyu_Version();
postgis_wagyu_version
-----------------------
0.4.3 (Internal)
(1 row)
PostGIS_Scripts_Build_Date
Returns build date of the PostGIS scripts.
//语法
text PostGIS_Scripts_Build_Date();
//示例
SELECT PostGIS_Scripts_Build_Date();
postgis_scripts_build_date
-------------------------
2007-08-18 09:09:26
(1 row)
PostGIS_Scripts_Installed
Returns version of the postgis scripts installed in this database.
//语法
SELECT PostGIS_Scripts_Installed();
postgis_scripts_installed
-------------------------
1.5.0SVN
(1 row)
//示例
SELECT PostGIS_Scripts_Installed();
postgis_scripts_installed
-------------------------
1.5.0SVN
(1 row)
PostGIS_Scripts_Released
Returns the version number of the postgis.sql script released with the installed postgis lib.
//语法
text PostGIS_Scripts_Released();
//示例
SELECT PostGIS_Scripts_Released();
postgis_scripts_released
-------------------------
1.3.4SVN
(1 row)
PostGIS_Version
Returns PostGIS version number and compile-time options.
//语法
text PostGIS_Version();
//示例
SELECT PostGIS_Version();
postgis_version
---------------------------------------
1.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
