[第三篇] PostGIS：“我让PG更完美”

概要

本篇文章主要分为几何图形输入输出其它输入格式、几何图形输出（WKT/WKB）、边界操作符、距离操作符、拓扑空间关系函数、距离空间关系函数、测量函数这九部分。

Geometry Input Other Formats

ST_Box2dFromGeoHash

Return a BOX2D from a GeoHash string.

//语法
box2d ST_Box2dFromGeoHash(text geohash, integer precision=full_precision_of_geohash);
//示例
SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0');
st_geomfromgeohash
--------------------------------------------------
BOX(-115.172816 36.114646,-115.172816 36.114646)
SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 0);
st_box2dfromgeohash
----------------------
BOX(-180 -90,180 90)
SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10);
st_box2dfromgeohash
---------------------------------------------------------------------------
BOX(-115.17282128334 36.1146408319473,-115.172810554504 36.1146461963654)

ST_GeomFromGeoHash

Return a geometry from a GeoHash string.

//语法
geometry ST_GeomFromGeoHash(text geohash, integer precision=full_precision_of_geohash);
//示例
SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0'));
st_astext
-------------------------------------------------------------------------------------------------------------------------- 
POLYGON((-115.172816 36.114646,-115.172816 36.114646,-115.172816 36.114646,-115.172816 
36.114646,-115.172816 36.114646))
SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 4));
st_astext
------------------------------------------------------------------------------------------------------------------------------ 
POLYGON((-115.3125 36.03515625,-115.3125 36.2109375,-114.9609375 36.2109375,-114.9609375 ←-
36.03515625,-115.3125 36.03515625))
SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10));
st_astext 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
POLYGON((-115.17282128334 36.1146408319473,-115.17282128334 ←-
36.1146461963654,-115.172810554504 36.1146461963654,-115.172810554504 ←-
36.1146408319473,-115.17282128334 36.1146408319473))

ST_GeomFromGML

Takes as input GML representation of geometry and outputs a PostGIS geometry object.

//语法
geometry ST_GeomFromGML(text geomgml);
geometry ST_GeomFromGML(text geomgml, integer srid);
//示例
SELECT ST_GeomFromGML('
<gml:LineString srsName="EPSG:4269">
<gml:coordinates>
-71.16028,42.258729 -71.160837,42.259112 -71.161143,42.25932
</gml:coordinates>
</gml:LineString>');

ST_GeomFromGeoJSON

Takes as input a geojson representation of a geometry and outputs a PostGIS geometry object.

//语法
geometry ST_GeomFromGeoJSON(text geomjson);
geometry ST_GeomFromGeoJSON(json geomjson);
geometry ST_GeomFromGeoJSON(jsonb geomjson);
//示例
SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}'))
As wkt;
wkt
------
POINT(-48.23456 20.12345)

-- a 3D linestring
SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"LineString","coordinates
":[[1,2,3],[4,5,6],[7,8,9]]}')) As wkt;
wkt
-------------------
LINESTRING(1 2,4 5,7 8)

ST_GeomFromKML

Takes as input KML representation of geometry and outputs a PostGIS geometry object.

//语法
geometry ST_GeomFromKML(text geomkml)
//示例
SELECT ST_GeomFromKML('
<LineString> <coordinates>-71.1663,42.2614
-71.1667,42.2616</coordinates>
</LineString>');

ST_GeomFromTWKB

Creates a geometry instance from a TWKB ("Tiny Well-Known Binary") geometry representation.

//语法
geometry ST_GeomFromTWKB(bytea twkb);
//示例
SELECT ST_AsText(ST_GeomFromTWKB(ST_AsTWKB('LINESTRING(126 34, 127 35)'::geometry)));
st_astext
-----------------------------
LINESTRING(126 34, 127 35)
(1 row)

SELECT ST_AsEWKT(
ST_GeomFromTWKB(E'\\x620002f7f40dbce4040105')
);
st_asewkt
------------------------------------------------------
LINESTRING(-113.98 39.198,-113.981 39.195)
(1 row)

ST_GMLToSQL

Return a specifified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML.

//语法
geometry ST_GMLToSQL(text geomgml);
geometry ST_GMLToSQL(text geomgml, integer srid);
//示例
-- Create a line string from a polyline
SELECT ST_AsEWKT(ST_LineFromEncodedPolyline('_p~iF~ps|U_ulLnnqC_mqNvxq❵@'));
-- result --
SRID=4326;LINESTRING(-120.2 38.5,-120.95 40.7,-126.453 43.252)
-- Select different precision that was used for polyline encoding
SELECT ST_AsEWKT(ST_LineFromEncodedPolyline('_p~iF~ps|U_ulLnnqC_mqNvxq❵@',6));
-- result --
SRID=4326;LINESTRING(-12.02 3.85,-12.095 4.07,-12.6453 4.3252)

ST_PointFromGeoHash

Return a point from a GeoHash string.

//语法
point ST_PointFromGeoHash(text geohash, integer precision=full_precision_of_geohash);
//示例
SELECT ST_AsText(ST_PointFromGeoHash('9qqj7nmxncgyy4d0dbxqz0'));
st_astext
------------------------------
POINT(-115.172816 36.114646)
SELECT ST_AsText(ST_PointFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 4));
st_astext
-----------------------------------
POINT(-115.13671875 36.123046875)
SELECT ST_AsText(ST_PointFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10));
st_astext
-------------------------------------------
POINT(-115.172815918922 36.1146435141563)

Geometry Output-WKT

ST_AsEWKT

Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.

//语法
text ST_AsEWKT(geometry g1);
text ST_AsEWKT(geography g1);
//示例
SELECT ST_AsEWKT('0103000020E61000000100000005000000000000
000000000000000000000000000000000000000000000000000000
F03F000000000000F03F000000000000F03F000000000000F03
F000000000000000000000000000000000000000000000000'::geometry);
st_asewkt
--------------------------------
PostGIS 3.0.5dev Manual 215 / 841
SRID=4326;POLYGON((0 0,0 1,1 1,1 0,0 0))
(1 row)
SELECT ST_AsEWKT('0108000080030000000000000060
E30A4100000000785C0241000000000000F03F0000000018
E20A4100000000485F024100000000000000400000000018
E20A4100000000305C02410000000000000840')
--st_asewkt---
CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)

ST_AsText

Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.

//语法
text ST_AsText(geometry g1);
text ST_AsText(geometry g1, integer maxdecimaldigits=15);
text ST_AsText(geography g1);
text ST_AsText(geography g1, integer maxdecimaldigits=15);
//示例
SELECT ST_AsText('01030000000100000005000000000000000000
000000000000000000000000000000000000000000000000
F03F000000000000F03F000000000000F03F000000000000F03
F000000000000000000000000000000000000000000000000');
st_astext
--------------------------------
POLYGON((0 0,0 1,1 1,1 0,0 0))
(1 row)

Geometry Output-WKB

ST_AsBinary

Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.

//语法
bytea ST_AsBinary(geometry g1);
bytea ST_AsBinary(geometry g1, text NDR_or_XDR);
bytea ST_AsBinary(geography g1);
bytea ST_AsBinary(geography g1, text NDR_or_XDR);
//示例
SELECT ST_AsBinary(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
st_asbinary
--------------------------------
\001\003\000\000\000\001\000\000\000\005
\000\000\000\000\000\000\000\000\000\000
\000\000\000\000\000\000\000\000\000\000
\000\000\000\000\000\000\000\000\000\000
\000\000\000\360?\000\000\000\000\000\000
\360?\000\000\000\000\000\000\360?\000\000
\000\000\000\000\360?\000\000\000\000\000
\000\000\000\000\000\000\000\000\000\000\000
\000\000\000\000\000\000\000\000
(1 row)

ST_AsEWKB

Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.

//语法
bytea ST_AsEWKB(geometry g1);
bytea ST_AsEWKB(geometry g1, text NDR_or_XDR);
//示例
SELECT ST_AsEWKB(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
st_asewkb
--------------------------------
\001\003\000\000 \346\020\000\000\001\000
\000\000\005\000\000\000\000
\000\000\000\000\000\000\000\000
\000\000\000\000\000\000\000\000\000
\000\000\000\000\000\000\000\000\000\000
\000\000\360?\000\000\000\000\000\000\360?
\000\000\000\000\000\000\360?\000\000\000\000\000
\000\360?\000\000\000\000\000\000\000\000\000\000\000
\000\000\000\000\000\000\000\000\000\000\000\000\000
(1 row)

ST_AsHEXEWKB

Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR)

encoding.

//语法
text ST_AsHEXEWKB(geometry g1, text NDRorXDR);
text ST_AsHEXEWKB(geometry g1);
//示例
SELECT ST_AsHEXEWKB(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
which gives same answer as
SELECT ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326)::text;

Geometry Output Other Formats

ST_AsEncodedPolyline

Returns an Encoded Polyline from a LineString geometry.

//语法
text ST_AsEncodedPolyline(geometry geom, integer precision=5);
//示例
SELECT ST_AsEncodedPolyline(GeomFromEWKT('SRID=4326;LINESTRING(-120.2 38.5,-120.95 ←-
40.7,-126.453 43.252)'));
--result--
|_p~iF~ps|U_ulLnnqC_mqNvxq❵@

ST_AsGeobuf

Return a Geobuf representation of a set of rows.

//语法
bytea ST_AsGeobuf(anyelement set row);
bytea ST_AsGeobuf(anyelement row, text geom_name);
//示例
SELECT encode(ST_AsGeobuf(q, 'geom'), 'base64')
FROM (SELECT ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))') AS geom) AS q;
st_asgeobuf
----------------------------------
GAAiEAoOCgwIBBoIAAAAAgIAAAE=

ST_AsGeoJSON

Return the geometry as a GeoJSON element.

//语法
text ST_AsGeoJSON(record feature, text geomcolumnname, integer maxdecimaldigits=9, boolean pretty_bool=false);
text ST_AsGeoJSON(geometry geom, integer maxdecimaldigits=9, integer options=8);
text ST_AsGeoJSON(geography geog, integer maxdecimaldigits=9, integer options=0);
//示例
select json_build_object( 'type', 'FeatureCollection',
'features', json_agg(ST_AsGeoJSON(t.*)::json) )
from ( values (1, 'one', 'POINT(1 1)'::geometry),
(2, 'two', 'POINT(2 2)'),
(3, 'three', 'POINT(3 3)')
) as t(id, name, geom);
-----------------------------
{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point 
","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}, {"type": "Feature", " 
geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two
"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": 
{"id": 3, "name": "three"}}]}

ST_AsGML

Return the geometry as a GML version 2 or 3 element.

//语法
text ST_AsGML(geometry geom, integer maxdecimaldigits=15, integer options=0);
text ST_AsGML(geography geog, integer maxdecimaldigits=15, integer options=0, text nprefix=null, text id=null);
text ST_AsGML(integer version, geometry geom, integer maxdecimaldigits=15, integer options=0, text nprefix=null, text id=null);
text ST_AsGML(integer version, geography geog, integer maxdecimaldigits=15, integer options=0, text nprefix=null, text
id=null);
//示例
SELECT ST_AsGML(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
st_asgml
--------
<gml:Polygon srsName="EPSG:4326"><gml:outerBoundaryIs><gml:LinearRing><gml:coordinates 
>0,0 0,1 1,1 1,0 0,0</gml:coordinates></gml:LinearRing></gml:outerBoundaryIs></gml:
Polygon>

ST_AsKML

Return the geometry as a KML element. Several variants. Default version=2, default maxdecimaldigits=15.

//语法
text ST_AsKML(geometry geom, integer maxdecimaldigits=15, text nprefix=NULL);
text ST_AsKML(geography geog, integer maxdecimaldigits=15, text nprefix=NULL);
//示例
SELECT ST_AsKML(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326));
st_askml
--------
<Polygon><outerBoundaryIs><LinearRing><coordinates>0,0 0,1 1,1 1,0 0,0</coordinates></
LinearRing></outerBoundaryIs></Polygon>
--3d linestring
SELECT ST_AsKML('SRID=4326;LINESTRING(1 2 3, 4 5 6)');
<LineString><coordinates>1,2,3 4,5,6</coordinates></LineString>

ST_AsLatLonText

Return the Degrees, Minutes, Seconds representation of the given point.

//语法
text ST_AsLatLonText(geometry pt, text format=”)
//示例
SELECT (ST_AsLatLonText('POINT (-3.2342342 -2.32498)'));
st_aslatlontext
----------------------------
2\textdegree{}19'29.928"S 3\textdegree{}14'3.243"W

ST_AsMVTGeom

Transform a geometry into the coordinate space of a Mapbox Vector Tile.

//语法
geometry ST_AsMVTGeom(geometry geom, box2d bounds, integer extent=4096, integer buffer=256, boolean clip_geom=true);
//示例
SELECT ST_AsText(ST_AsMVTGeom(
ST_GeomFromText('POLYGON ((0 0, 10 0, 10 5, 0 -5, 0 0))'),
ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)),
4096, 0, false));
st_astext
--------------------------------------------------------------------
MULTIPOLYGON(((5 4096,10 4091,10 4096,5 4096)),((5 4096,0 4101,0 4096,5 4096)))

ST_AsMVT

Aggregate function returning a Mapbox Vector Tile representation of a set of rows.

//语法
bytea ST_AsMVT(anyelement set row);
bytea ST_AsMVT(anyelement row, text name);
bytea ST_AsMVT(anyelement row, text name, integer extent);
bytea ST_AsMVT(anyelement row, text name, integer extent, text geom_name);
bytea ST_AsMVT(anyelement r