[第二篇] 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