[第二篇] PostGIS：“我让PG更完美！”
概要
本篇文章主要介绍了PostGIS的基础函数、几何图形访问函数、几何图形编辑函数、几何图形验证函数、空间参考系统函数、几何图形输入输出（WKT/WKB）这七部分。
PostGIS Reference
ST_Collect
Creates a GeometryCollection or Multi* geometry from a set of geometries.
//语法
geometry ST_Collect(geometry g1, geometry g2);
geometry ST_Collect(geometry[] g1_array);
geometry ST_Collect(geometry set g1field);
ST_LineFromMultiPoint
Creates a LineString from a MultiPoint geometry.
//语法
geometry ST_LineFromMultiPoint(geometry aMultiPoint);
ST_MakeEnvelope
ST_MakeEnvelope — Creates a rectangular Polygon from minimum and maximum coordinates.
//语法
geometry ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer srid=unknown);
//示例
SELECT ST_AsText( ST_MakeEnvelope(10, 10, 11, 11, 4326) );
st_asewkt
-----------
POLYGON((10 10, 10 11, 11 11, 11 10, 10 10))
ST_MakeLine
Creates a Linestring from Point, MultiPoint, or LineString geometries.
//语法
geometry ST_MakeLine(geometry geom1, geometry geom2);
geometry ST_MakeLine(geometry[] geoms_array);
geometry ST_MakeLine(geometry set geoms);
ST_MakePoint
//语法
geometry ST_MakePoint(float x, float y);
geometry ST_MakePoint(float x, float y, float z);
geometry ST_MakePoint(float x, float y, float z, float m);
ST_MakePointM
Creates a Point from X, Y and M values.
//语法
geometry ST_MakePointM(float x, float y, float m);
//示例
SELECT ST_AsEWKT( ST_MakePointM(-71.1043443253471, 42.3150676015829, 10) );
st_asewkt
-----------------------------------------------
POINTM(-71.1043443253471 42.3150676015829 10)
ST_MakePolygon
Creates a Polygon from a shell and optional list of holes.
//语法
geometry ST_MakePolygon(geometry linestring);
geometry ST_MakePolygon(geometry outerlinestring, geometry[] interiorlinestrings);
//示例
SELECT ST_MakePolygon( ST_GeomFromText('LINESTRING(75 29,77 29,77 29, 75 29)'));
//Create a Polygon from an open LineString, using ST_StartPoint and ST_AddPoint to close it.
SELECT ST_MakePolygon( ST_AddPoint(foo.open_line, ST_StartPoint(foo.open_line)) )
FROM (
SELECT ST_GeomFromText('LINESTRING(75 29,77 29,77 29, 75 29)') As open_line) As foo;
//Create a Polygon from a 3D LineString
SELECT ST_AsEWKT( ST_MakePolygon( 'LINESTRING(75.15 29.53 1,77 29 1,77.6 29.5 1, 75.15
29.53 1)'));
st_asewkt
-----------
POLYGON((75.15 29.53 1,77 29 1,77.6 29.5 1,75.15 29.53 1))
//Create a Polygon from a LineString with measures
SELECT ST_AsEWKT( ST_MakePolygon( 'LINESTRINGM(75.15 29.53 1,77 29 1,77.6 29.5 2, 75.15
29.53 2)' ));
st_asewkt
----------
POLYGONM((75.15 29.53 1,77 29 1,77.6 29.5 2,75.15 29.53 2))
ST_Point
Creates a Point with the given coordinate values. Alias for ST_MakePoint.
//语法
geometry ST_Point(float x, float y);
//示例 Geometry
SELECT ST_SetSRID( ST_Point( -71.104, 42.315), 4326)
//Geography
SELECT CAST(ST_SetSRID( ST_Point( -71.104, 42.315), 4326) AS geography);
//方法引用示例
SELECT ST_SetSRID( ST_Point( -71.104, 42.315), 4326)::geography;
//
//点坐标的重投影
SELECT ST_Transform(ST_SetSRID( ST_Point( 3637510, 3014852 ), 2273), 4326)::geography;
ST_Polygon
Creates a Polygon from a LineString with a specifified SRID.
//语法
geometry ST_Polygon(geometry lineString, integer srid);
//Create a 2D polygon.
SELECT ST_AsText( ST_Polygon('LINESTRING(75 29, 77 29, 77 29, 75 29)'::geometry, 4326) );
-- result --
POLYGON((75 29, 77 29, 77 29, 75 29))
//Create a 3D polygon.
SELECT ST_AsEWKT( ST_Polygon( ST_GeomFromEWKT('LINESTRING(75 29 1, 77 29 2, 77 29 3, 75 29
1)'), 4326) );
-- result --
SRID=4326;POLYGON((75 29 1, 77 29 2, 77 29 3, 75 29 1))
ST_TileEnvelope
Creates a rectangular Polygon in Web Mercator (SRID:3857) using the XYZ tile system.
//语法
geometry ST_TileEnvelope(integer tileZoom, integer tileX, integer tileY, geometry bounds=SRID=3857;LINESTRING(-20037508.342789
-20037508.342789,20037508.342789 20037508.342789));
// Creates a rectangular Polygon in Web Mercator (SRID:3857) using the XYZ tile system.
SELECT ST_AsText( ST_TileEnvelope(2, 1, 1) );
st_astext
------------------------------
POLYGON((-10018754.1713945 0,-10018754.1713945 10018754.1713945,0 10018754.1713945,0
0,-10018754.1713945 0))
SELECT ST_AsText( ST_TileEnvelope(3, 1, 1, ST_MakeEnvelope(-180, -90, 180, 90, 4326) ) );
st_astext
------------------------------------------------------
POLYGON((-135 45,-135 67.5,-90 67.5,-90 45,-135 45))
Geometry Accessors
GeometryType
Returns the type of a geometry as text.
//语法
text GeometryType(geometry geomA);
//示例
SELECT GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29
29.07)'));
geometrytype
--------------
LINESTRING
//示例
SELECT ST_GeometryType(ST_GeomFromEWKT('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)) )'));
--result
POLYHEDRALSURFACE
//示例
SELECT GeometryType(geom) as result
FROM
(SELECT
ST_GeomFromEWKT('TIN (((
0 0 0,
0 0 1,
0 1 0,
0 0 0
)), ((
0 0 0,
0 1 0,
1 1 0,
0 0 0
))
)') AS geom
) AS g;
result
--------
TIN
ST_Boundary
Returns the boundary of a geometry.
//语法
geometry ST_Boundary(geometry geomA);
//示例
SELECT ST_Boundary(geom)
FROM (SELECT 'LINESTRING(100 150,50 60, ←-
70 80, 160 170)'::geometry As geom) As f;
-- ST_AsText output
MULTIPOINT(100 150,160 170)
//示例
SELECT ST_Boundary(geom)
FROM (SELECT
'POLYGON (( 10 130, 50 190, 110 190, 140 ←-
150, 150 80, 100 10, 20 40, 10 130 ),
( 70 40, 100 50, 120 80, 80 110, ←-
50 90, 70 40 ))'::geometry As geom) As f;
-- ST_AsText output
MULTILINESTRING((10 130,50 190,110 ←-
190,140 150,150 80,100 10,20 40,10 130),
(70 40,100 50,120 80,80 110,50 ←-
90,70 40))
ST_CoordDim
Return the coordinate dimension of a geometry.
//语法
integer **ST_CoordDim**(geometry geomA);
//示例
SELECT ST_CoordDim('CIRCULARSTRING(1 2 3, 1 3 4, 5 6 7, 8 9 10, 11 12 13)');
---result--
3
//示例
SELECT ST_CoordDim(ST_Point(1,2));
--result--
2
ST_Dimension
Returns the topological dimension of a geometry.
//语法
integer ST_Dimension(geometry g);
//示例
SELECT ST_Dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0))');
ST_Dimension
-----------
1
ST_Dump
Returns a set of geometry_dump rows for the components of a geometry.
//语法
geometry_dump[] ST_Dump(geometry g1);
//示例
SELECT sometable.field1, sometable.field1, (ST_Dump(sometable.the_geom)).geom AS the_geom
FROM sometable;
-- Break a compound curve into its constituent linestrings and circularstrings
SELECT ST_AsEWKT(a.geom), ST_HasArc(a.geom)
FROM ( SELECT (ST_Dump(p_geom)).geom AS geom
FROM (SELECT ST_GeomFromEWKT('COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 ←-
1))') AS p_geom) AS b ) AS a;
st_asewkt | st_hasarc
-----------------------------+----------
CIRCULARSTRING(0 0,1 1,1 0) | t
LINESTRING(1 0,0 1) | f
(2 rows)
ST_DumpPoints
Returns a set of geometry_dump rows for the points in a geometry.
//语法
geometry_dump[]ST_DumpPoints(geometry geom);
//示例
SELECT edge_id, (dp).path[1] As index, ST_AsText((dp).geom) As wktnode
FROM (SELECT 1 As edge_id
, ST_DumpPoints(ST_GeomFromText('LINESTRING(1 2, 3 4, 10 10)')) AS dp
UNION ALL
SELECT 2 As edge_id
, ST_DumpPoints(ST_GeomFromText('LINESTRING(3 5, 5 6, 9 10)')) AS dp
) As foo;
edge_id | index | wktnode
---------+-------+--------------
1 | 1 | POINT(1 2)
1 | 2 | POINT(3 4)
1 | 3 | POINT(10 10)
2 | 1 | POINT(3 5)
2 | 2 | POINT(5 6)
2 | 3 | POINT(9 10)
ST_DumpRings
Returns a set of geometry_dump rows for the exterior and interior rings of a Polygon.
//语法
geometry_dump[] ST_DumpRings(geometry a_polygon);
//示例
SELECT sometable.field1, sometable.field1, (ST_DumpRings(sometable.the_geom)).geom As the_geom
FROM sometableOfpolys;
SELECT ST_AsEWKT(geom) As the_geom, path
FROM ST_DumpRings(
ST_GeomFromEWKT('POLYGON((-8149064 5133092 1,-8149064 5132986 1,-8148996 5132839 ←-
1,-8148972 5132767 1,-8148958 5132508 1,-8148941 5132466 1,-8148924 5132394 1,
-8148903 5132210 1,-8148930 5131967 1,-8148992 5131978 1,-8149237 5132093 1,-8149404 ←-
5132211 1,-8149647 5132310 1,-8149757 5132394 1,
-8150305 5132788 1,-8149064 5133092 1),
(-8149362 5132394 1,-8149446 5132501 1,-8149548 5132597 1,-8149695 5132675 1,-8149362 ←-
5132394 1))')) as foo;
path | the_geom
----------------------------------------------------------------------------------------------------------------
{0} | POLYGON((-8149064 5133092 1,-8149064 5132986 1,-8148996 5132839 1,-8148972 5132767
1,-8148958 5132508 1,
| -8148941 5132466 1,-8148924 5132394 1,
| -8148903 5132210 1,-8148930 5131967 1,
| -8148992 5131978 1,-8149237 5132093 1,
| -8149404 5132211 1,-8149647 5132310 1,-8149757 5132394 1,-8150305 5132788 ←-
1,-8149064 5133092 1))
{1} | POLYGON((-8149362 5132394 1,-8149446 5132501 1,
| -8149548 5132597 1,-8149695 5132675 1,-8149362 5132394 1))
ST_EndPoint
Returns the last point of a LineString or CircularLineString.
//语法
geometry ST_EndPoint(geometry g);
//示例
postgis=# SELECT ST_AsText(ST_EndPoint('LINESTRING(1 1, 2 2, 3 3)'::geometry));
st_astext
------------
POINT(3 3)
(1 row)
postgis=# SELECT ST_EndPoint('POINT(1 1)'::geometry) IS NULL AS is_null;
is_null
----------
t
(1 row)
ST_Envelope
Returns a geometry representing the bounding box of a geometry.
//语法
geometry ST_Envelope(geometry g1);
//实例
SELECT ST_AsText(ST_Envelope('POINT(1 3)'::geometry));
st_astext
------------
POINT(1 3)
(1 row)
ST_BoundingDiagonal
Returns the diagonal of a geometry’s bounding box.
//语法
geometry ST_BoundingDiagonal(geometry geom, boolean fits=false);
//示例
SELECT ST_X(ST_StartPoint(ST_BoundingDiagonal(
ST_Buffer(ST_MakePoint(0,0),10)
)));
st_x
------
-10
ST_ExteriorRing
Returns a LineString representing the exterior ring of a Polygon.
//语法
geometry ST_ExteriorRing(geometry a_polygon);
//示例
SELECT ST_AsEWKT(
ST_ExteriorRing(
ST_GeomFromEWKT('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))')
)
);
st_asewkt
---------
LINESTRING(0 0 1,1 1 1,1 2 1,1 1 1,0 0 1)
ST_GeometryN
Return the Nth geometry element of a geometry collection.
//语法
geometry ST_GeometryN(geometry geomA, integer n);
//示例
SELECT n, ST_AsEWKT(ST_GeometryN(the_geom, n)) As geomewkt
FROM (
VALUES (ST_GeomFromEWKT('MULTIPOINT(1 2 7, 3 4 7, 5 6 7, 8 9 10)') ),
( ST_GeomFromEWKT('MULTICURVE(CIRCULARSTRING(2.5 2.5,4.5 2.5, 3.5 3.5), (10 11, 12 11))') )
)As foo(the_geom)
CROSS JOIN generate_series(1,100) n
WHERE n <= ST_NumGeometries(the_geom);
n | geomewkt
---+-----------------------------------------
1 | POINT(1 2 7)
2 | POINT(3 4 7)
3 | POINT(5 6 7)
4 | POINT(8 9 10)
1 | CIRCULARSTRING(2.5 2.5,4.5 2.5,3.5 3.5)
2 | LINESTRING(10 11,12 11)
ST_GeometryType
Returns the SQL-MM type of a geometry as text.
//语法
text ST_GeometryType(geometry g1);
//示例
SELECT ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 ←-
29.31,77.29 29.07)'));
--result
ST_LineString
ST_HasArc
Tests if a geometry contains a circular arc.
//语法
boolean ST_HasArc(geometry geomA);
//示例
SELECT ST_HasArc(ST_Collect('LINESTRING(1 2, 3 4, 5 6)', 'CIRCULARSTRING(1 1, 2 3, 4 5, 6 ←-
7, 5 6)'));
st_hasarc
--------
t
ST_InteriorRingN
Returns the Nth interior ring (hole) of a Polygon.
//语法
geometry ST_InteriorRingN(geometry a_polygon, integer n);
//示例
SELECT ST_AsText(ST_InteriorRingN(the_geom, 1)) As the_geom
FROM (SELECT ST_BuildArea(
ST_Collect(ST_Buffer(ST_Point(1,2), 20,3),
ST_Buffer(ST_Point(1, 2), 10,3))) As the_geom
) as foo
ST_IsPolygonCCW
Tests if Polygons have exterior rings oriented counter-clockwise and interior rings oriented clockwise.
//语法
boolean ST_IsPolygonCCW ( geometry geom );
ST_IsPolygonCW
Tests if Polygons have exterior rings oriented clockwise and interior rings oriented counter-clockwise.
//语法
boolean ST_IsPolygonCW ( geometry geom );
ST_IsClosed
Tests if a LineStrings’s start and end points are coincident. For a PolyhedralSurface tests if it is closed (volu
metric).
//语法
boolean ST_IsClosed(geometry g);
//示例
SELECT ST_IsClosed('LINESTRING(0 0, 1 1)'::geometry);
st_isclosed
-------------
f
(1 row)
ST_IsCollection
Tests if a geometry is a geometry collection type.
//语法
boolean ST_IsCollection(geometry g);
//示例
SELECT ST_IsCollection('LINESTRING(0 0, 1 1)'::geometry);
st_iscollection
-------------
f
(1 row)
ST_IsEmpty
Tests if a geometry is empty.
//语法
boolean ST_IsEmpty(geometry geomA);
//示例
SELECT ST_IsEmpty(ST_GeomFromText('GEOMETRYCOLLECTION EMPTY'));
st_isempty
------------
t
(1 row)
ST_IsRing
Tests if a LineString is closed and simple.
//语法
boolean ST_IsRing(geometry g);
//示例
SELECT ST_IsRing(the_geom), ST_IsClosed(the_geom), ST_IsSimple(the_geom)
FROM (SELECT 'LINESTRING(0 0, 0 1, 1 1, 1 0, 0 0)'::geometry AS the_geom) AS foo;
st_isring | st_isclosed | st_issimple
-----------+-------------+-------------
t | t | t
(1 row)
ST_IsSimple
Tests if a geometry has no points of self-intersection or self-tangency.
//语法
boolean ST_IsSimple(geometry geomA);
//示例
SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));
st_issimple
-------------
t
(1 row)
ST_M
Returns the M coordinate of a Point.
//语法
float ST_M(geometry a_point)
//示例
SELECT ST_M(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_m
------
4
(1 row)
ST_MemSize
Returns the amount of memory space a geometry takes.
//语法
integer ST_MemSize(geometry geomA);
//示例
SELECT pg_size_pretty(SUM(ST_MemSize(the_geom))) as totgeomsum,
pg_size_pretty(SUM(CASE WHEN town = 'BOSTON' THEN ST_MemSize(the_geom) ELSE 0 END)) As ←-
bossum,
CAST(SUM(CASE WHEN town = 'BOSTON' THEN ST_MemSize(the_geom) ELSE 0 END)*1.00 /
SUM(ST_MemSize(the_geom))*100 As numeric(10,2)) As perbos
FROM towns;
totgeomsum bossum perbos
---------- ------ ------
1522 kB 30 kB 1.99
ST_NDims
Returns the coordinate dimension of a geometry.
//语法
integer ST_NDims(geometry g1);
//示例
SELECT ST_NDims(ST_GeomFromText('POINT(1 1)')) As d2point,
ST_NDims(ST_GeomFromEWKT('POINT(1 1 2)')) As d3point,
ST_NDims(ST_GeomFromEWKT('POINTM(1 1 0.5)')) As d2pointm;
d2point | d3point | d2pointm
---------+---------+----------
2 | 3 | 3
ST_NPoints
Returns the coordinate dimension of a geometry.
//语法
integer ST_NDims(geometry g1);
//示例
SELECT ST_NDims(ST_GeomFromText('POINT(1 1)')) As d2point,
ST_NDims(ST_GeomFromEWKT('POINT(1 1 2)')) As d3point,
ST_NDims(ST_GeomFromEWKT('POINTM(1 1 0.5)')) As d2pointm;
d2point | d3point | d2pointm
---------+---------+----------
2 | 3 | 3
ST_NPoints
Returns the number of points (vertices) in a geometry.
//语法
integer ST_NPoints(geometry g1);
//示例
SELECT ST_NPoints(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 ←-
29.07)'));
--result
4
ST_NRings
Returns the number of rings in a polygonal geometry.
//语法
integer ST_NRings(geometry geomA);
//示例
SELECT ST_NRings(the_geom) As Nrings, ST_NumInteriorRings(the_geom) As ninterrings
FROM (SELECT ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))') As the_geom) As foo ←- ;
nrings | ninterrings
--------+-------------
1 | 0
(1 row)
ST_NumGeometries
Returns the number of elements in a geometry collection.
//语法
integer ST_NumGeometries(geometry geom);
//示例
SELECT ST_NumGeometries(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 ←-
29.31,77.29 29.07)'));
--result
1
ST_NumInteriorRings
Returns the number of interior rings (holes) of a Polygon.
//语法
integer ST_NumInteriorRings(geometry a_polygon)
//示例
SELECT gid, field1, field2, ST_NumInteriorRings(the_geom) AS numholes
FROM sometable;
ST_NumInteriorRings
Returns the number of interior rings (holes) of a Polygon.
//语法
integer ST_NumInteriorRings(geometry a_polygon);
//示例
SELECT gid, field1, field2, ST_NumInteriorRings(the_geom) AS numholes
FROM sometable;
ST_NumInteriorRing
Returns the number of interior rings (holes) of a Polygon. Aias for ST_NumInteriorRings.
//语法
integer ST_NumInteriorRing(geometry a_polygon);
//示例
SELECT ST_NumPatches(ST_GeomFromEWKT('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)) )'));
--result
6
ST_NumPoints
Returns the number of points in a LineString or CircularString.
//语法
integer ST_NumPoints(geometry g1);
//示例
SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 ←-
29.07)'));
--result
4
ST_PatchN
Returns the Nth geometry (face) of a PolyhedralSurface.
//语法
geometry ST_PatchN(geometry geomA, integer n);
//示例
SELECT ST_AsEWKT(ST_PatchN(geom, 2)) As geomewkt
FROM (
VALUES (ST_GeomFromEWKT('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)) )')) ) As ←-
foo(geom);
geomewkt
---+-----------------------------------------
POLYGON((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0))
ST_PointN
Returns the Nth point in the fifirst LineString or circular LineString in a geometry.
//语法
geometry ST_PointN(geometry a_linestring, integer n);
//示例
SELECT ST_AsText(
ST_PointN(
column1,
generate_series(1, ST_NPoints(column1))
))
FROM ( VALUES ('LINESTRING(0 0, 1 1, 2 2)'::geometry) ) AS foo;
st_astext
------------
POINT(0 0)
POINT(1 1)
POINT(2 2)
(3 rows)
ST_Points
Returns a MultiPoint containing all the coordinates of a geometry.
//语法
geometry ST_Points( geometry geom );
//示例
SELECT ST_AsText(ST_Points('POLYGON Z ((30 10 4,10 30 5,40 40 6, 30 10))'));
--result
MULTIPOINT Z (30 10 4,10 30 5,40 40 6, 30 10 4)
ST_StartPoint
Returns the fifirst point of a LineString.
//语法
geometry ST_StartPoint(geometry geomA);
//示例
SELECT ST_AsText(ST_StartPoint('LINESTRING(0 1, 0 2)'::geometry));
st_astext
------------
POINT(0 1)
(1 row)
ST_Summary
Returns a text summary of the contents of a geometry.
//语法
text ST_Summary(geometry g);
text ST_Summary(geography g);
//示例
SELECT ST_Summary(ST_GeomFromText('LINESTRING(0 0, 1 1)')) as geom,
ST_Summary(ST_GeogFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) geog;
geom | geog
-----------------------------+--------------------------
LineString[B] with 2 points | Polygon[BGS] with 1 rings
| ring 0 has 5 points
:
(1 row)
ST_X
Returns the X coordinate of a Point.
//语法
float ST_X(geometry a_point);
//示例
SELECT ST_X(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_x
------
1
(1 row)
ST_Y
Returns the Y coordinate of a Point.
//语法
float ST_Y(geometry a_point);
//示例
SELECT ST_Y(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_y
------
2
(1 row)
ST_Z
Returns the Z coordinate of a Point.
//语法
float ST_Z(geometry a_point);
//示例
SELECT ST_Z(ST_GeomFromEWKT('POINT(1 2 3 4)'));
st_z
------
3
(1 row)
ST_Zmflflag
Returns a code indicating the ZM coordinate dimension of a geometry.
//语法
smallint ST_Zmflag(geometry geomA);
//示例
SELECT ST_Zmflag(ST_GeomFromEWKT('LINESTRING(1 2, 3 4)'));
st_zmflag
-----------
0
Geometry Editors
ST_AddPoint
Add a point to a LineString.
//语法
geometry ST_AddPoint(geometry linestring, geometry point);
geometry ST_AddPoint(geometry linestring, geometry point, integer position);
//示例
UPDATE sometable
SET the_geom = ST_AddPoint(the_geom, ST_StartPoint(the_geom))
FROM sometable
WHERE ST_IsClosed(the_geom) = false;
--Adding point to a 3-d line
SELECT ST_AsEWKT(ST_AddPoint(ST_GeomFromEWKT('LINESTRING(0 0 1, 1 1 1)'), ST_MakePoint
(1, 2, 3)));
--result
st_asewkt
----------
LINESTRING(0 0 1,1 1 1,1 2 3)
ST_CollectionExtract
Given a (multi)geometry, return a (multi)geometry consisting only of elements of the specifified type.
//语法
geometry ST_CollectionExtract(geometry collection, integer type);
//示例
-- Constants: 1 == POINT, 2 == LINESTRING, 3 == POLYGON
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('GEOMETRYCOLLECTION(
GEOMETRYCOLLECTION(POINT(0 0)))'),1));
st_astext
---------------
MULTIPOINT(0 0)
(1 row)
SELECT ST_AsText(ST_CollectionExtract(ST_GeomFromText('GEOMETRYCOLLECTION(
GEOMETRYCOLLECTION(LINESTRING(0 0, 1 1)),LINESTRING(2 2, 3 3))'),2));
st_astext
---------------
MULTILINESTRING((0 0, 1 1), (2 2, 3 3))
(1 row)
ST_CollectionHomogenize
Given a geometry collection, return the "simplest" representation of the contents.
//语法
geometry ST_CollectionHomogenize(geometry collection);
//示例
SELECT ST_AsText(ST_CollectionHomogenize('GEOMETRYCOLLECTION(POINT(0 0))'));
st_astext
------------
POINT(0 0)
(1 row)
SELECT ST_AsText(ST_CollectionHomogenize('GEOMETRYCOLLECTION(POINT(0 0),POINT(1 1))'));
st_astext
---------------------
MULTIPOINT(0 0,1 1)
(1 row)
ST_Force2D
Force the geometries into a "2-dimensional mode".
//语法
geometry ST_Force2D(geometry geomA);
//示例
SELECT ST_AsEWKT(ST_Force2D(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 ←-
2)')));
st_asewkt
-------------------------------------
CIRCULARSTRING(1 1,2 3,4 5,6 7,5 6)
SELECT ST_AsEWKT(ST_Force2D('POLYGON((0 0 2,0 5 2,5 0 2,0 0 2),(1 1 2,3 1 2,1 3 2,1 1 2)) ←-
'));
st_asewkt
----------------------------------------------
POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))
ST_Force3D
Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.
//语法
geometry ST_Force3D(geometry geomA);
//示例
--Nothing happens to an already 3D geometry
SELECT ST_AsEWKT(ST_Force3D(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2,
5 6 2)')));
st_asewkt
-----------------------------------------------
CIRCULARSTRING(1 1 2,2 3 2,4 5 2,6 7 2,5 6 2)
SELECT ST_AsEWKT(ST_Force3D('POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))'));
st_asewkt
--------------------------------------------------------------
POLYGON((0 0 0,0 5 0,5 0 0,0 0 0),(1 1 0,3 1 0,1 3 0,1 1 0))
ST_Force3DZ
Force the geometries into XYZ mode.
//语法
geometry ST_Force3DZ(geometry geomA);
//示例
--Nothing happens to an already 3D geometry
SELECT ST_AsEWKT(ST_Force3DZ(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5
6 2)')));
st_asewkt
-----------------------------------------------
CIRCULARSTRING(1 1 2,2 3 2,4 5 2,6 7 2,5 6 2)
SELECT ST_AsEWKT(ST_Force3DZ('POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))'));
st_asewkt
--------------------------------------------------------------
POLYGON((0 0 0,0 5 0,5 0 0,0 0 0),(1 1 0,3 1 0,1 3 0,1 1 0))
ST_Force3DM
ST_Force3DM — Force the geometries into XYM mode.
//语法
geometry ST_Force3DM(geometry geomA);
//示例
--Nothing happens to an already 3D geometry
SELECT ST_AsEWKT(ST_Force3DM(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5
6 2)')));
st_asewkt
------------------------------------------------
CIRCULARSTRINGM(1 1 0,2 3 0,4 5 0,6 7 0,5 6 0)
SELECT ST_AsEWKT(ST_Force3DM('POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 1,1 1 1))
'));
st_asewkt
---------------------------------------------------------------
POLYGONM((0 0 0,0 5 0,5 0 0,0 0 0),(1 1 0,3 1 0,1 3 0,1 1 0))
ST_Force4D
Force the geometries into XYZM mode.
//语法
geometry ST_Force4D(geometry geomA);
//示例
--Nothing happens to an already 3D geometry
SELECT ST_AsEWKT(ST_Force4D(ST_GeomFromEWKT('CIRCULARSTRING(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6
2)')));
st_asewkt
---------------------------------------------------------
CIRCULARSTRING(1 1 2 0,2 3 2 0,4 5 2 0,6 7 2 0,5 6 2 0)
SELECT ST_AsEWKT(ST_Force4D('MULTILINESTRINGM((0 0 1,0 5 2,5 0 3,0 0 4),(1 1 1,3 1 1,1 3
1,1 1 1))'));
st_asewkt
--------------------------------------------------------------------------------------
MULTILINESTRING((0 0 0 1,0 5 0 2,5 0 0 3,0 0 0 4),(1 1 0 1,3 1 0 1,1 3 0 1,1 1 0 1))
ST_ForcePolygonCCW
Orients all exterior rings counter-clockwise and all interior rings clockwise.
//语法
geometry **ST_ForcePolygonCCW** ( geometry geom );
ST_ForceCollection
Convert the geometry into a GEOMETRYCOLLECTION.
//语法
geometry ST_ForceCollection(geometry geomA);
//示例
SELECT ST_AsEWKT(ST_ForceCollection('POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 ←-
1,1 1 1))'));
st_asewkt
----------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POLYGON((0 0 1,0 5 1,5 0 1,0 0 1),(1 1 1,3 1 1,1 3 1,1 1 1)))
SELECT ST_AsText(ST_ForceCollection('CIRCULARSTRING(220227 150406,2220227 150407,220227 ←-
150406)'));
st_astext
--------------------------------------------------------------------------------
GEOMETRYCOLLECTION(CIRCULARSTRING(220227 150406,2220227 150407,220227 150406))
(1 row)
-- POLYHEDRAL example --
SELECT ST_AsEWKT(ST_ForceCollection('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)))'))
st_asewkt
----------------------------------------------------------------------------------
GEOMETRYCOLLECTION(
POLYGON((0 0 0,0 0 1,0 1 1,0 1 0,0 0 0)),
POLYGON((0 0 0,0 1 0,1 1 0,1 0 0,0 0 0)),
POLYGON((0 0 0,1 0 0,1 0 1,0 0 1,0 0 0)),
POLYGON((1 1 0,1 1 1,1 0 1,1 0 0,1 1 0)),
POLYGON((0 1 0,0 1 1,1 1 1,1 1 0,0 1 0)),
POLYGON((0 0 1,1 0 1,1 1 1,0 1 1,0 0 1))
)
ST_ForcePolygonCW
Orients all exterior rings clockwise and all interior rings counter-clockwise.
//语法
geometry ST_ForcePolygonCW ( geometry geom );
ST_ForceSFS
//语法
geometry ST_ForceSFS(geometry geomA);
geometry ST_ForceSFS(geometry geomA, text version);
ST_ForceRHR
Force the orientation of the vertices in a polygon to follow the Right-Hand-Rule.
//语法
geometry ST_ForceRHR(geometry g);
//示例
SELECT ST_AsEWKT(
ST_ForceRHR( 'POLYGON((0 0 2, 5 0 2, 0 5 2, 0 0 2),(1 1 2, 1 3 2, 3 1 2, 1 1 2))'
)
);
st_asewkt
--------------------------------------------------------------
POLYGON((0 0 2,0 5 2,5 0 2,0 0 2),(1 1 2,3 1 2,1 3 2,1 1 2))
(1 row)
ST_ForceCurve
Upcast a geometry into its curved type, if applicable.
//语法
geometry ST_ForceCurve(geometry g);
//示例
SELECT ST_AsText(
ST_ForceCurve( 'POLYGON((0 0 2, 5 0 2, 0 5 2, 0 0 2),(1 1 2, 1 3 2, 3 1 2, 1 1 2))'::geometry
)
);
st_astext
----------------------------------------------------------------------
CURVEPOLYGON Z ((0 0 2,5 0 2,0 5 2,0 0 2),(1 1 2,1 3 2,3 1 2,1 1 2))
(1 row)
ST_LineMerge
Return a (set of) LineString(s) formed by sewing together a MULTILINESTRING.
//语法
geometry ST_LineMerge(geometry amultilinestring);
//示例
SELECT ST_AsText(ST_LineMerge(
ST_GeomFromText('MULTILINESTRING((-29 -27,-30 -29.7,-36 -31,-45 -33),(-45 -33,-46 -32))')
)
);
st_astext
-------------------------------------------------------------------------------------------------- ←-
LINESTRING(-29 -27,-30 -29.7,-36 -31,-45 -33,-46 -32)
(1 row)
--If can't be merged - original MULTILINESTRING is returned
SELECT ST_AsText(ST_LineMerge(
ST_GeomFromText('MULTILINESTRING((-29 -27,-30 -29.7,-36 -31,-45 -33),(-45.2 -33.2,-46 -32)) ←-
')
)
);
st_astext
----------------
MULTILINESTRING((-45.2 -33.2,-46 -32),(-29 -27,-30 -29.7,-36 -31,-45 -33))
-- example with Z dimension
SELECT ST_AsText(ST_LineMerge(
ST_GeomFromText('MULTILINESTRING((-29 -27 11,-30 -29.7 10,-36 -31 5,-45 -33 6), (-29 -27 ←-
12,-30 -29.7 5), (-45 -33 1,-46 -32 11))')
)
);
st_astext
-------------------------------------------------------------------------------------------------- ←-
LINESTRING Z (-30 -29.7 5,-29 -27 11,-30 -29.7 10,-36 -31 5,-45 -33 1,-46 -32 11)
(1 row)
ST_Multi
Return the geometry as a MULTI* geometry.
//语法
geometry ST_Multi(geometry g1);
//示例
SELECT ST_AsText(ST_Multi(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))')));
st_astext
PostGIS 3.0.5dev Manual 170 / 841
--------------------------------------------------------------------------------------------------
MULTIPOLYGON(((743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,
743238 2967416)))
(1 row)
ST_Normalize
Return the geometry in its canonical form.
//语法
geometry ST_Normalize(geometry geom);
//示例
SELECT ST_AsText(ST_Normalize(ST_GeomFromText( 'GEOMETRYCOLLECTION(
POINT(2 3),
MULTILINESTRING((0 0, 1 1),(2 2, 3 3)),
POLYGON(
(0 10,0 0,10 0,10 10,0 10),
(4 2,2 2,2 4,4 4,4 2),
(6 8,8 8,8 6,6 6,6 8)
)
)'
)));
st_astext
---------------------------------------------------------------------------------------------------------------------------------------------------- ←-
GEOMETRYCOLLECTION(POLYGON((0 0,0 10,10 10,10 0,0 0),(6 6,8 6,8 8,6 8,6 6),(2 2,4 2,4 4,2 ←-
4,2 2)),MULTILINESTRING((2 2,3 3),(0 0,1 1)),POINT(2 3))
(1 row)
ST_QuantizeCoordinates
Sets least signifificant bits of coordinates to zero.
//语法
geometry ST_QuantizeCoordinates ( geometry g , int prec_x , int prec_y , int prec_z , int prec_m );
//示例
SELECT ST_AsText(ST_QuantizeCoordinates('POINT (100.123456 0)'::geometry, 4));
st_astext
-------------------------
POINT(100.123455047607 0)
WITH test AS (SELECT 'POINT (123.456789123456 123.456789123456)'::geometry AS geom)
SELECT
digits,
encode(ST_QuantizeCoordinates(geom, digits), 'hex'),
ST_AsText(ST_QuantizeCoordinates(geom, digits))
FROM test, generate_series(15, -15, -1) AS digits;
digits | encode | st_astext
--------+--------------------------------------------+------------------------------------------
15 | 01010000005f9a72083cdd5e405f9a72083cdd5e40 | POINT(123.456789123456 ←-
123.456789123456)
14 | 01010000005f9a72083cdd5e405f9a72083cdd5e40 | POINT(123.456789123456 ←-
123.456789123456)
13 | 01010000005f9a72083cdd5e405f9a72083cdd5e40 | POINT(123.456789123456 ←-
123.456789123456)
12 | 01010000005c9a72083cdd5e405c9a72083cdd5e40 | POINT(123.456789123456 ←-
123.456789123456)
11 | 0101000000409a72083cdd5e40409a72083cdd5e40 | POINT(123.456789123456 ←-
123.456789123456)
10 | 0101000000009a72083cdd5e40009a72083cdd5e40 | POINT(123.456789123455 ←-
123.456789123455)
9 | 0101000000009072083cdd5e40009072083cdd5e40 | POINT(123.456789123418 ←-
123.456789123418)
8 | 0101000000008072083cdd5e40008072083cdd5e40 | POINT(123.45678912336 ←-
123.45678912336)
7 | 0101000000000070083cdd5e40000070083cdd5e40 | POINT(123.456789121032 ←-
123.456789121032)
6 | 0101000000000040083cdd5e40000040083cdd5e40 | POINT(123.456789076328 ←-
123.456789076328)
5 | 0101000000000000083cdd5e40000000083cdd5e40 | POINT(123.456789016724 ←-
123.456789016724)
4 | 0101000000000000003cdd5e40000000003cdd5e40 | POINT(123.456787109375 ←-
123.456787109375)
3 | 0101000000000000003cdd5e40000000003cdd5e40 | POINT(123.456787109375 ←-
123.456787109375)
2 | 01010000000000000038dd5e400000000038dd5e40 | POINT(123.45654296875 ←-
123.45654296875)
1 | 01010000000000000000dd5e400000000000dd5e40 | POINT(123.453125 123.453125)
0 | 01010000000000000000dc5e400000000000dc5e40 | POINT(123.4375 123.4375)
-1 | 01010000000000000000c05e400000000000c05e40 | POINT(123 123)
-2 | 01010000000000000000005e400000000000005e40 | POINT(120 120)
-3 | 010100000000000000000058400000000000005840 | POINT(96 96)
-4 | 010100000000000000000058400000000000005840 | POINT(96 96)
-5 | 010100000000000000000058400000000000005840 | POINT(96 96)
-6 | 010100000000000000000058400000000000005840 | POINT(96 96)
-7 | 010100000000000000000058400000000000005840 | POINT(96 96)
-8 | 010100000000000000000058400000000000005840 | POINT(96 96)
-9 | 010100000000000000000058400000000000005840 | POINT(96 96)
-10 | 010100000000000000000058400000000000005840 | POINT(96 96)
-11 | 010100000000000000000058400000000000005840 | POINT(96 96)
-12 | 010100000000000000000058400000000000005840 | POINT(96 96)
-13 | 010100000000000000000058400000000000005840 | POINT(96 96)
-14 | 010100000000000000000058400000000000005840 | POINT(96 96)
-15 | 010100000000000000000058400000000000005840 | POINT(96 96)
ST_RemovePoint
Remove point from a linestring.
//语法
geometry ST_RemovePoint(geometry linestring, integer offset);
//示例
--guarantee no LINESTRINGS are closed
--by removing the end point. The below assumes the_geom is of type LINESTRING
UPDATE sometable
SET the_geom = ST_RemovePoint(the_geom, ST_NPoints(the_geom) - 1)
FROM sometable
WHERE ST_IsClosed(the_geom) = true;
ST_Reverse
Return the geometry with vertex order reversed.
//语法
geometry ST_Reverse(geometry g1);
//示例
SELECT ST_AsText(the_geom) as line, ST_AsText(ST_Reverse(the_geom)) As reverseline
FROM
(SELECT ST_MakeLine(ST_MakePoint(1,2),
ST_MakePoint(1,10)) As the_geom) as foo;
--result
line | reverseline
---------------------+----------------------
LINESTRING(1 2,1 10) | LINESTRING(1 10,1 2)
ST_Segmentize
Return a modifified geometry/geography having no segment longer than the given distance.
//语法
geometry ST_Segmentize(geometry geom, float max_segment_length);
geography ST_Segmentize(geography geog, float max_segment_length);
//示例
SELECT ST_AsText(ST_Segmentize(
ST_GeomFromText('MULTILINESTRING((-29 -27,-30 -29.7,-36 -31,-45 -33),(-45 -33,-46 -32))')
,5)
);
st_astext
-------------------------------------------------------------------------------------------------- ←-
MULTILINESTRING((-29 -27,-30 -29.7,-34.886615700134 -30.758766735029,-36 -31,
-40.8809353009198 -32.0846522890933,-45 -33),
(-45 -33,-46 -32))
(1 row)
SELECT ST_AsText(ST_Segmentize(ST_GeomFromText('POLYGON((-29 28, -30 40, -29 28))'),10));
st_astext
-----------------------
POLYGON((-29 28,-29.8304547985374 37.9654575824488,-30 40,-29.1695452014626 ←-
30.0345424175512,-29 28))
(1 row)
ST_SetPoint
Replace point of a linestring with a given point.
//语法
geometry ST_SetPoint(geometry linestring, integer zerobasedposition, geometry point);
//示例
--Change first point in line string from -1 3 to -1 1
SELECT ST_AsText(ST_SetPoint('LINESTRING(-1 2,-1 3)', 0, 'POINT(-1 1)'));
st_astext
-----------------------
LINESTRING(-1 1,-1 3)
---Change last point in a line string (lets play with 3d linestring this time)
SELECT ST_AsEWKT(ST_SetPoint(foo.the_geom, ST_NumPoints(foo.the_geom) - 1, ST_GeomFromEWKT ←-
('POINT(-1 1 3)')))
FROM (SELECT ST_GeomFromEWKT('LINESTRING(-1 2 3,-1 3 4, 5 6 7)') As the_geom) As foo;
st_asewkt
-----------------------
LINESTRING(-1 2 3,-1 3 4,-1 1 3)
SELECT ST_AsText(ST_SetPoint(g, -3, p))
FROM ST_GEomFromText('LINESTRING(0 0, 1 1, 2 2, 3 3, 4 4)') AS g , ST_PointN(g,1) as p;
st_astext
-----------------------
LINESTRING(0 0,1 1,0 0,3 3,4 4)
ST_SnapToGrid
Snap all points of the input geometry to a regular grid.
//语法
geometry ST_SnapToGrid(geometry geomA, float originX, float originY, float sizeX, float sizeY);
geometry ST_SnapToGrid(geometry geomA, float sizeX, float sizeY);
geometry ST_SnapToGrid(geometry geomA, float size);
geometry ST_SnapToGrid(geometry geomA, geometry pointOrigin, float sizeX, float sizeY, float sizeZ, float sizeM);
//示例
--Snap your geometries to a precision grid of 10^-3
UPDATE mytable
SET the_geom = ST_SnapToGrid(the_geom, 0.001);
SELECT ST_AsText(ST_SnapToGrid(
ST_GeomFromText('LINESTRING(1.1115678 2.123, 4.111111 3.2374897, 4.11112 3.23748667)
'),
0.001)
);
st_astext
-------------------------------------
LINESTRING(1.112 2.123,4.111 3.237)
--Snap a 4d geometry
SELECT ST_AsEWKT(ST_SnapToGrid(
ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 2.3456 1.11111,
4.111111 3.2374897 3.1234 1.1111, -1.11111112 2.123 2.3456 1.1111112)'),
ST_GeomFromEWKT('POINT(1.12 2.22 3.2 4.4444)'),
0.1, 0.1, 0.1, 0.01) );
st_asewkt
------------------------------------------------------------------------------
LINESTRING(-1.08 2.12 2.3 1.1144,4.12 3.22 3.1 1.1144,-1.08 2.12 2.3 1.1144)
--With a 4d geometry - the ST_SnapToGrid(geom,size) only touches x and y coords but keeps m
and z the same
SELECT ST_AsEWKT(ST_SnapToGrid(ST_GeomFromEWKT('LINESTRING(-1.1115678 2.123 3 2.3456,
4.111111 3.2374897 3.1234 1.1111)'),
0.01) );
st_asewkt
---------------------------------------------------------
LINESTRING(-1.11 2.12 3 2.3456,4.11 3.24 3.1234 1.1111)
ST_Snap
Snap segments and vertices of input geometry to vertices of a reference geometry.
//语法
geometry ST_Snap(geometry input, geometry reference, float tolerance);
//示例
SELECT ST_AsText(
ST_Snap(line, poly, ST_Distance(poly,
line)*1.01)
) AS linesnapped
FROM (SELECT
ST_GeomFromText('MULTIPOLYGON(
((26 125, 26 200, 126 200, 126 125,
26 125),
(51 150, 101 150, 76 175, 51 150 )) ,
((151 100, 151 200, 176 175, 151
100)))') As poly,
ST_GeomFromText('LINESTRING (5
107, 54 84, 101 100)') As line
) As foo;
linesnapped
----------------------------------------
LINESTRING(5 107,26 125,54 84,101 100)
SELECT ST_AsText(
ST_Snap(line, poly, ST_Distance(poly,
line)*1.25)
) AS linesnapped
FROM (SELECT
ST_GeomFromText('MULTIPOLYGON(
(( 26 125, 26 200, 126 200, 126 125,
26 125 ),
(51 150, 101 150, 76 175, 51 150 )) ,
((151 100, 151 200, 176 175, 151
100 )))') As poly,
ST_GeomFromText('LINESTRING (5
107, 54 84, 101 100)') As line
) As foo;
linesnapped
---------------------------------------
LINESTRING(26 125,54 84,101 100)
ST_SwapOrdinates
Returns a version of the given geometry with given ordinate values swapped.
//语法
geometry ST_SwapOrdinates(geometry geom, cstring ords);
//示例
-- Scale M value by 2
SELECT ST_AsText(
ST_SwapOrdinates(
ST_Scale(
ST_SwapOrdinates(g,'xm'),
2, 1
),
'xm')
) FROM ( SELECT 'POINT ZM (0 0 0 2)'::geometry g ) foo;
st_astext
--------------------
POINT ZM (0 0 0 4)
Geometry Validation(几何图形验证)
ST_IsValid
Tests if a geometry is well-formed in 2D.
//语法
boolean ST_IsValid(geometry g);
boolean ST_IsValid(geometry g, integer flags);
//示例
SELECT ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) As good_line,
ST_IsValid(ST_GeomFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) As bad_poly
--results
NOTICE: Self-intersection at or near point 0 0
good_line | bad_poly
-----------+----------
t | f
ST_IsValidDetail
Returns a valid_detail row stating if a geometry is valid, and if not a reason why and a location.
//语法
valid_detail ST_IsValidDetail(geometry geom, integer flags);
//示例
--First 3 Rejects from a successful quintuplet experiment
SELECT gid, reason(ST_IsValidDetail(the_geom)), ST_AsText(location(ST_IsValidDetail( ←-
the_geom))) as location
FROM
(SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), array_agg(f.line)) As the_geom, gid
FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
FROM generate_series(-4,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,8) z1
WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
INNER JOIN (SELECT ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1 ←- *1, z1*2) As line
FROM generate_series(-3,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,10) z1
WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
GROUP BY gid, e.buff) As quintuplet_experiment
WHERE ST_IsValid(the_geom) = false
ORDER BY gid
LIMIT 3;
gid | reason | location
------+-------------------+-------------
5330 | Self-intersection | POINT(32 5)
5340 | Self-intersection | POINT(42 5)
5350 | Self-intersection | POINT(52 5)
--simple example
SELECT * FROM ST_IsValidDetail('LINESTRING(220227 150406,2220227 150407,222020 150410)');
valid | reason | location
-------+--------+----------
t | |
ST_IsValidReason
Returns text stating if a geometry is valid, or a reason for invalidity.
//语法
text ST_IsValidReason(geometry geomA, integer flags);
text ST_IsValidReason(geometry geomA);
//示例
--First 3 Rejects from a successful quintuplet experiment
SELECT gid, ST_IsValidReason(the_geom) as validity_info
FROM
(SELECT ST_MakePolygon(ST_ExteriorRing(e.buff), array_agg(f.line)) As the_geom, gid
FROM (SELECT ST_Buffer(ST_MakePoint(x1*10,y1), z1) As buff, x1*10 + y1*100 + z1*1000 As gid
FROM generate_series(-4,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,8) z1
WHERE x1 > y1*0.5 AND z1 < x1*y1) As e
INNER JOIN (SELECT ST_Translate(ST_ExteriorRing(ST_Buffer(ST_MakePoint(x1*10,y1), z1)),y1 ←- *1, z1*2) As line
FROM generate_series(-3,6) x1
CROSS JOIN generate_series(2,5) y1
CROSS JOIN generate_series(1,10) z1
WHERE x1 > y1*0.75 AND z1 < x1*y1) As f
ON (ST_Area(e.buff) > 78 AND ST_Contains(e.buff, f.line))
GROUP BY gid, e.buff) As quintuplet_experiment
WHERE ST_IsValid(the_geom) = false
ORDER BY gid
LIMIT 3;
gid | validity_info
------+--------------------------
5330 | Self-intersection [32 5]
5340 | Self-intersection [42 5]
5350 | Self-intersection [52 5]
--simple example
SELECT ST_IsValidReason('LINESTRING(220227 150406,2220227 150407,222020 150410)');
st_isvalidreason
------------------
Valid Geometry
Spatial Reference System Functions
ST_SetSRID
Set the SRID on a geometry to a particular integer value.
//语法
geometry ST_SetSRID(geometry geom, integer srid);
//示例
SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611),4326) As wgs84long_lat;
-- the ewkt representation (wrap with ST_AsEWKT) -
SRID=4326;POINT(-123.365556 48.428611)
ST_SRID
Returns the spatial reference identififier for the ST_Geometry as defifined in spatial_ref_sys table.
//语法
integer ST_SRID(geometry g1);
//示例
SELECT ST_SRID(ST_GeomFromText('POINT(-71.1043 42.315)',4326));
--result
4326
ST_Transform
Return a new geometry with its coordinates transformed to a different spatial reference system.
//语法
geometry ST_Transform(geometry g1, integer srid);
geometry ST_Transform(geometry geom, text to_proj);
geometry ST_Transform(geometry geom, text from_proj, text to_proj);
geometry ST_Transform(geometry geom, text from_proj, integer to_srid);
//示例
SELECT ST_AsText(ST_Transform(ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))',2249),4326)) As wgs_geom;
wgs_geom
---------------------------
POLYGON((-71.1776848522251 42.3902896512902,-71.1776843766326 42.3903829478009,
-71.1775844305465 42.3903826677917,-71.1775825927231 42.3902893647987,-71.177684
8522251 42.3902896512902));
(1 row)
--3D Circular String example
SELECT ST_AsEWKT(ST_Transform(ST_GeomFromEWKT('SRID=2249;CIRCULARSTRING(743238 2967416 ←-
1,743238 2967450 2,743265 2967450 3,743265.625 2967416 3,743238 2967416 4)'),4326));
st_asewkt
--------------------------------------------------------------------------------------
SRID=4326;CIRCULARSTRING(-71.1776848522251 42.3902896512902 1,-71.1776843766326 ←-
42.3903829478009 2,
-71.1775844305465 42.3903826677917 3,
-71.1775825927231 42.3902893647987 3,-71.1776848522251 42.3902896512902 4)
CREATE INDEX idx_the_geom_26986_parcels
ON parcels
USING gist
(ST_Transform(the_geom, 26986))
WHERE the_geom IS NOT NULL;
Geometry Input-WKT
ST_BdPolyFromText
Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString Well-Known
text representation.
//语法
geometry ST_BdPolyFromText(text WKT, integer srid);
ST_BdMPolyFromText
Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text
representation Well-Known text representation.、
//语法
geometry ST_BdMPolyFromText(text WKT, integer srid);
ST_GeogFromText
Return a specifified geography value from Well-Known Text representation or extended (WKT).
//语法
geography ST_GeogFromText(text EWKT);
//示例
--- converting lon lat coords to geography
ALTER TABLE sometable ADD COLUMN geog geography(POINT,4326);
UPDATE sometable SET geog = ST_GeogFromText('SRID=4326;POINT(' || lon || ' ' || lat || ')');
--- specify a geography point using EPSG:4267, NAD27
SELECT ST_AsEWKT(ST_GeogFromText('SRID=4267;POINT(-77.0092 38.889588)'));
ST_GeographyFromText
Return a specifified geography value from Well-Known Text representation or extended (WKT).
//语法
geography ST_GeographyFromText(text EWKT);
ST_GeomCollFromText
Makes a collection Geometry from collection WKT with the given SRID. If SRID is not given, it
defaults to 0.
//语法
geometry ST_GeomCollFromText(text WKT, integer srid);
geometry ST_GeomCollFromText(text WKT);
//示例
SELECT ST_GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 2),LINESTRING(1 2, 3 4))');
ST_GeomFromEWKT
Return a specifified ST_Geometry value from Extended Well-Known Text representation (EWKT).
//语法
geometry ST_GeomFromEWKT(text EWKT);
//示例
SELECT ST_GeomFromEWKT('SRID=4269;LINESTRING(-71.160281 42.258729,-71.160837 ←-
42.259113,-71.161144 42.25932)');
SELECT ST_GeomFromEWKT('SRID=4269;MULTILINESTRING((-71.160281 42.258729,-71.160837 ←-
42.259113,-71.161144 42.25932))');
SELECT ST_GeomFromEWKT('SRID=4269;POINT(-71.064544 42.28787)');
SELECT ST_GeomFromEWKT('SRID=4269;POLYGON((-71.1776585052917 ←-
42.3902909739571,-71.1776820268866 42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917
42.3902909739571))');
SELECT ST_GeomFromEWKT('SRID=4269;MULTIPOLYGON(((-71.1031880899493 42.3152774590236,
-71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,
-71.1023097974109 42.3151969047397,-71.1019285062273 42.3147384934248,
-71.102505233663 42.3144722937587,-71.10277487471 42.3141658254797,
-71.103113945163 42.3142739188902,-71.10324876416 42.31402489987,
-71.1033002961013 42.3140393340215,-71.1033488797549 42.3139495090772,
-71.103396240451 42.3138632439557,-71.1041521907712 42.3141153348029,
-71.1041411411543 42.3141545014533,-71.1041287795912 42.3142114839058,
-71.1041188134329 42.3142693656241,-71.1041112482575 42.3143272556118,
-71.1041072845732 42.3143851580048,-71.1041057218871 42.3144430686681,
-71.1041065602059 42.3145009876017,-71.1041097995362 42.3145589148055,
-71.1041166403905 42.3146168544148,-71.1041258822717 42.3146748022936,
-71.1041375307579 42.3147318674446,-71.1041492906949 42.3147711126569,
-71.1041598612795 42.314808571739,-71.1042515013869 42.3151287620809,
-71.1041173835118 42.3150739481917,-71.1040809891419 42.3151344119048,
-71.1040438678912 42.3151191367447,-71.1040194562988 42.3151832057859,
-71.1038734225584 42.3151140942995,-71.1038446938243 42.3151006300338,
-71.1038315271889 42.315094347535,-71.1037393329282 42.315054824985,
-71.1035447555574 42.3152608696313,-71.1033436658644 42.3151648370544,
-71.1032580383161 42.3152269126061,-71.103223066939 42.3152517403219,
-71.1031880899493 42.3152774590236)),
((-71.1043632495873 42.315113108546,-71.1043583974082 42.3151211109857,
-71.1043443253471 42.3150676015829,-71.1043850704575 42.3150793250568,-71.1043632495873 ←-
42.315113108546)))');
--3d circular string
SELECT ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)');
--Polyhedral Surface example
SELECT ST_GeomFromEWKT('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))
)');
ST_GeometryFromText
Return a specifified ST_Geometry value from Well-Known Text representation (WKT). This is an alias
name for ST_GeomFromText.
//语法
geometry ST_GeometryFromText(text WKT);
geometry ST_GeometryFromText(text WKT, integer srid);
ST_GeomFromText
Return a specifified ST_Geometry value from Well-Known Text representation (WKT).
//语法
geometry ST_GeomFromText(text WKT);
geometry ST_GeomFromText(text WKT, integer srid);
//示例
SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144
42.25932)');
SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144
42.25932)',4269);
SELECT ST_GeomFromText('MULTILINESTRING((-71.160281 42.258729,-71.160837 ←-
42.259113,-71.161144 42.25932))');
SELECT ST_GeomFromText('POINT(-71.064544 42.28787)');
SELECT ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866 ←-
42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917
42.3902909739571))');
SELECT ST_GeomFromText('MULTIPOLYGON(((-71.1031880899493 42.3152774590236,
-71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,
-71.1023097974109 42.3151969047397,-71.1019285062273 42.3147384934248,
-71.102505233663 42.3144722937587,-71.10277487471 42.3141658254797,
-71.103113945163 42.3142739188902,-71.10324876416 42.31402489987,
-71.1033002961013 42.3140393340215,-71.1033488797549 42.3139495090772,
-71.103396240451 42.3138632439557,-71.1041521907712 42.3141153348029,
-71.1041411411543 42.3141545014533,-71.1041287795912 42.3142114839058,
-71.1041188134329 42.3142693656241,-71.1041112482575 42.3143272556118,
-71.1041072845732 42.3143851580048,-71.1041057218871 42.3144430686681,
-71.1041065602059 42.3145009876017,-71.1041097995362 42.3145589148055,
-71.1041166403905 42.3146168544148,-71.1041258822717 42.3146748022936,
-71.1041375307579 42.3147318674446,-71.1041492906949 42.3147711126569,
-71.1041598612795 42.314808571739,-71.1042515013869 42.3151287620809,
-71.1041173835118 42.3150739481917,-71.1040809891419 42.3151344119048,
-71.1040438678912 42.3151191367447,-71.1040194562988 42.3151832057859,
-71.1038734225584 42.3151140942995,-71.1038446938243 42.3151006300338,
-71.1038315271889 42.315094347535,-71.1037393329282 42.315054824985,
-71.1035447555574 42.3152608696313,-71.1033436658644 42.3151648370544,
-71.1032580383161 42.3152269126061,-71.103223066939 42.3152517403219,
-71.1031880899493 42.3152774590236)),
((-71.1043632495873 42.315113108546,-71.1043583974082 42.3151211109857,
-71.1043443253471 42.3150676015829,-71.1043850704575 42.3150793250568,-71.1043632495873
42.315113108546)))',4326);
SELECT ST_GeomFromText('CIRCULARSTRING(220268 150415,220227 150505,220227 150406)');
ST_LineFromText
Makes a Geometry from WKT representation with the given SRID. If SRID is not given, it defaults to 0.
//语法
geometry ST_LineFromText(text WKT);
geometry ST_LineFromText(text WKT, integer srid);
//示例
SELECT ST_LineFromText('LINESTRING(1 2, 3 4)') AS aline, ST_LineFromText('POINT(1 2)') AS
null_return;
aline | null_return
------------------------------------------------
010200000002000000000000000000F ... | t
ST_MLineFromText
Return a specifified ST_MultiLineString value from WKT representation.
//语法
geometry ST_MLineFromText(text WKT, integer srid);
geometry ST_MLineFromText(text WKT);
//示例
SELECT ST_MLineFromText('MULTILINESTRING((1 2, 3 4), (4 5, 6 7))');
ST_MPointFromText
Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0.
//语法
geometry ST_MPointFromText(text WKT, integer srid);
geometry ST_MPointFromText(text WKT);
//示例
SELECT ST_MPointFromText('MULTIPOINT(1 2, 3 4)');
SELECT ST_MPointFromText('MULTIPOINT(-70.9590 42.1180, -70.9611 42.1223)', 4326);
ST_MPolyFromText
Makes a MultiPolygon Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0.
//语法
geometry ST_MPolyFromText(text WKT, integer srid);
geometry ST_MPolyFromText(text WKT);
//示例
SELECT ST_MPolyFromText('MULTIPOLYGON(((0 0 1,20 0 1,20 20 1,0 20 1,0 0 1),(5 5 3,5 7 3,7 7
3,7 5 3,5 5 3)))');
SELECt ST_MPolyFromText('MULTIPOLYGON(((-70.916 42.1002,-70.9468 42.0946,-70.9765
42.0872,-70.9754 42.0875,-70.9749 42.0879,-70.9752 42.0881,-70.9754 42.0891,-70.9758
42.0894,-70.9759 42.0897,-70.9759 42.0899,-70.9754 42.0902,-70.9756 42.0906,-70.9753
42.0907,-70.9753 42.0917,-70.9757 42.0924,-70.9755 42.0928,-70.9755 42.0942,-70.9751
42.0948,-70.9755 42.0953,-70.9751 42.0958,-70.9751 42.0962,-70.9759 42.0983,-70.9767
42.0987,-70.9768 42.0991,-70.9771 42.0997,-70.9771 42.1003,-70.9768 42.1005,-70.977
42.1011,-70.9766 42.1019,-70.9768 42.1026,-70.9769 42.1033,-70.9775 42.1042,-70.9773
42.1043,-70.9776 42.1043,-70.9778 42.1048,-70.9773 42.1058,-70.9774 42.1061,-70.9779
42.1065,-70.9782 42.1078,-70.9788 42.1085,-70.9798 42.1087,-70.9806 42.109,-70.9807
42.1093,-70.9806 42.1099,-70.9809 42.1109,-70.9808 42.1112,-70.9798 42.1116,-70.9792
42.1127,-70.979 42.1129,-70.9787 42.1134,-70.979 42.1139,-70.9791 42.1141,-70.9987
42.1116,-71.0022 42.1273,
-70.9408 42.1513,-70.9315 42.1165,-70.916 42.1002)))',4326);
ST_PointFromText
Makes a point Geometry from WKT with the given SRID. If SRID is not given, it defaults to unknown.
//语法
geometry ST_PointFromText(text WKT);
geometry ST_PointFromText(text WKT, integer srid);
//示例
SELECT ST_PointFromText('POINT(-71.064544 42.28787)');
SELECT ST_PointFromText('POINT(-71.064544 42.28787)', 4326);
ST_PolygonFromText
Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0.
//语法
geometry ST_PolygonFromText(text WKT);
geometry ST_PolygonFromText(text WKT, integer srid);
//示例
SELECT ST_PolygonFromText('POLYGON((-71.1776585052917 42.3902909739571,-71.1776820268866
42.3903701743239,
-71.1776063012595 42.3903825660754,-71.1775826583081 42.3903033653531,-71.1776585052917
42.3902909739571))');
st_polygonfromtext
------------------
010300000001000000050000006...
SELECT ST_PolygonFromText('POINT(1 2)') IS NULL as point_is_notpoly;
point_is_not_poly
----------
t
ST_WKTToSQL
Return a specifified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name
for ST_GeomFromText.
//语法
geometry ST_WKTToSQL(text WKT);
Geometry Input-WKB
ST_GeogFromWKB
Creates a geography instance from a Well-Known Binary geometry representation (WKB) or extended
Well Known Binary (EWKB).
//语法
geography ST_GeogFromWKB(bytea wkb);
//示例
--Although bytea rep contains single \, these need to be escaped when inserting into a
table
SELECT ST_AsText(
ST_GeogFromWKB(E'\\001\\002\\000\\000\\000\\002\\000\\000\\000\\037\\205\\353Q ←-
\\270~\\\\\\300\\323Mb\\020X\\231C@\\020X9\\264\\310~\\\\\\300)\\\\\\217\\302\\365\\230
C@')
);
st_astext
------------------------------------------------------
LINESTRING(-113.98 39.198,-113.981 39.195)
(1 row)
ST_GeomFromEWKB
Return a specifified ST_Geometry value from Extended Well-Known Binary representation (EWKB).
//语法
geometry ST_GeomFromEWKB(bytea EWKB);
//示例
SELECT ST_GeomFromEWKB(E'\\001\\002\\000\\000 \\255\\020\\000\\000\\003\\000\\000\\000\\344 J=
\\013B\\312Q\\300n\\303(\\010\\036!E@''\\277E''K
\\312Q\\300\\366{b\\235*!E@\\225|\\354.P\\312Q
\\300p\\231\\323e1!E@');
set standard_conforming_strings = on;
SELECT ST_GeomFromEWKB('\001\002\000\000 \255\020\000\000\003\000\000\000\344J=\012\013B
\312Q\300n\303(\010\036!E@''\277E''K\012\312Q\300\366{b\235*!E@\225|\354.P\312Q\012\300 p\231\323e1')
ST_GeomFromWKB
Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional
SRID.
//语法
geometry ST_GeomFromWKB(bytea geom);
geometry ST_GeomFromWKB(bytea geom, integer srid);
//示例
--Although bytea rep contains single \, these need to be escaped when inserting into a ←-
table
-- unless standard_conforming_strings is set to on.
SELECT ST_AsEWKT(
ST_GeomFromWKB(E'\\001\\002\\000\\000\\000\\002\\000\\000\\000\\037\\205\\353Q ←-
\\270~\\\\\\300\\323Mb\\020X\\231C@\\020X9\\264\\310~\\\\\\300)\\\\\\217\\302\\365\\230 ←-
C@',4326)
);
st_asewkt
------------------------------------------------------
SRID=4326;LINESTRING(-113.98 39.198,-113.981 39.195)
(1 row)
SELECT
ST_AsText(
ST_GeomFromWKB(
ST_AsEWKB('POINT(2 5)'::geometry) )
);
st_astext
------------
POINT(2 5)
(1 row)
ST_LineFromWKB
Makes a LINESTRING from WKB with the given SRID.
//语法
geometry ST_LineFromWKB(bytea WKB);
geometry ST_LineFromWKB(bytea WKB, integer srid);
//示例
SELECT ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))) AS aline,
ST_LineFromWKB(ST_AsBinary(ST_GeomFromText('POINT(1 2)'))) IS NULL AS null_return;
aline | null_return
------------------------------------------------
010200000002000000000000000000F ... | t
ST_LinestringFromWKB
Makes a geometry from WKB with the given SRID.
//语法
geometry ST_LinestringFromWKB(bytea WKB);
geometry ST_LinestringFromWKB(bytea WKB, integer srid);
//示例
SELECT
ST_LineStringFromWKB(
ST_AsBinary(ST_GeomFromText('LINESTRING(1 2, 3 4)'))
) AS aline,
ST_LinestringFromWKB(
ST_AsBinary(ST_GeomFromText('POINT(1 2)'))
) IS NULL AS null_return;
aline | null_return
------------------------------------------------
010200000002000000000000000000F ... | t
ST_PointFromWKB
Makes a geometry from WKB with the given SRID.
//语法
geometry ST_GeomFromWKB(bytea geom);
geometry ST_GeomFromWKB(bytea geom, integer srid);
//示例
SELECT
ST_AsText(
ST_PointFromWKB(
ST_AsEWKB('POINT(2 5)'::geometry) )
);
st_astext
------------
POINT(2 5)
(1 row)
SELECT
ST_AsText(
ST_PointFromWKB(
ST_AsEWKB('LINESTRING(2 5, 2 6)'::geometry) )
);
st_astext
-----------
(1 row)
ST_WKBToSQL
Return a specifified ST_Geometry value from Well-Known Binary representation (WKB). This is an alias
name for ST_GeomFromWKB that takes no srid.
//语法
geometry ST_WKBToSQL(bytea WKB);
//语法
geometry ST_WKBToSQL(bytea WKB);
