GeoSparkSQL函数汇总

yangcun_88

已于 2023-05-08 21:17:27 修改

阅读量344

点赞数

分类专栏：随手记文章标签：数据库 postgresql

于 2023-04-27 22:53:45 首次发布

本文链接：https://blog.csdn.net/weixin_43846235/article/details/130416873

版权

随手记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

GeoSparkSQL函数汇总

Geometry 创建函数

ST_Box2dFromGeoHash

-- http://www.postgis.net/docs/ST_Box2dFromGeoHash.html
-- 根据Geohash字符串(base-32)返回对应的BOX2D。
-- 如果未指定，precision则ST_Box2dFromGeoHash根据输入的GeoHash字符串的全精度返回BOX2D。
-- 如果precision指定了ST_Box2dFromGeoHash，它将使用GeoHash中的那么多字符来创建BOX2D。较低的精度值会导致BOX2D较大，而较大的值会提高精度。
box2d ST_Box2dFromGeoHash(text geohash, integer precision=full_precision_of_geohash);

-- 实例
SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0');
--------------------------------------------------
BOX(-115.172816 36.114646,-115.172816 36.114646)

SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 0);
----------------------
BOX(-180 -90,180 90)

SELECT ST_Box2dFromGeoHash('9qqj7nmxncgyy4d0dbxqz0', 10);
---------------------------------------------------------------------------
BOX(-115.17282128334 36.1146408319473,-115.172810554504 36.1146461963654)

ST_GeomFromGeoHash

-- http://www.postgis.net/docs/ST_GeomFromGeoHash.html
-- 根据Geohash字符串(base-32)返回对应的边界框的几何形状。几何将是代表GeoHash边界的多边形。
-- 如果未precision指定，则ST_GeomFromGeoHash根据输入的GeoHash字符串的全精度返回多边形。
-- 如果precision指定了ST_GeomFromGeoHash，它将使用GeoHash中的那么多字符来创建多边形。
geometry ST_GeomFromGeoHash(text geohash, integer precision=full_precision_of_geohash);

-- 实例
SELECT ST_AsText(ST_GeomFromGeoHash('9qqj7nmxncgyy4d0dbxqz0'));
------------------------------------------------------------------------------------------------------------
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));
------------------------------------------------------------------------------------------------------------
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));
------------------------------------------------------------------------------------------------------------
POLYGON((-115.17282128334 36.1146408319473,-115.17282128334 36.1146461963654,-115.172810554504 36.1146461963654,-115.172810554504 36.1146408319473,-115.17282128334 36.1146408319473))

ST_GeomFromText

-- http://www.postgis.net/docs/ST_GeomFromText.html
-- 根据给定的WKT构造PostGIS ST_Geometry对象。
-- 支持圆弧线和曲线
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)');
010200000003000000E44A3D0B42CA51C06EC328081E21454027BF45274BCA51C0F67B629D2A214540957CEC2E50CA51C07099D36531214540

SELECT ST_GeomFromText('LINESTRING(-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932)',4269);
0102000020AD10000003000000E44A3D0B42CA51C06EC328081E21454027BF45274BCA51C0F67B629D2A214540957CEC2E50CA51C07099D36531214540

SELECT ST_GeomFromText('MULTILINESTRING((-71.160281 42.258729,-71.160837 42.259113,-71.161144 42.25932))');
010500000001000000010200000003000000E44A3D0B42CA51C06EC328081E21454027BF45274BCA51C0F67B629D2A214540957CEC2E50CA51C07099D36531214540

SELECT ST_GeomFromText('POINT(-71.064544 42.28787)');
0101000000CB49287D21C451C0F0BF95ECD8244540

SELECT st_astext(ST_GeomFromText('POINT(-71.064544 42.28787)'));
POINT(-71.064544 42.28787)

ST_GeomFromWKB

-- http://www.postgis.net/docs/ST_GeomFromWKB.html
-- 根据给定的二进制表示形式和空间参考系统ID（SRID），并创建适当的几何类型的实例。
-- 方法支持圆弧线和曲线
geometry ST_GeomFromWKB(bytea geom);
geometry ST_GeomFromWKB(bytea geom, integer srid);

-- 实例
-- "\"在插入时需要转译，除非standard_conforming_strings设置为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\\230C@',4326)
);
------------------------------------------------------
SRID=4326;LINESTRING(-113.98 39.198,-113.981 39.195)

SELECT ST_AsText ( ST_GeomFromWKB ( ST_AsEWKB ( 'POINT(2 5)' :: geometry ) ) );
------------
POINT(2 5)

ST_AsEWKB

-- http://www.postgis.net/docs/ST_AsEWKB.html
-- 将geometry转换为二进制形式。
-- 该功能有2个变体。第一个变体不带字节序编码参数，默认为小字节序。第二个变体采用第二个参数表示编码-使用小尾数（'NDR'）或大尾数（'XDR'）编码。
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));
--------------------------------
\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

SELECT ST_AsEWKB(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326), 'XDR');
--------------------------------
\000 \000\000\003\000\000\020\346\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?
\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\000\000\000\000\000\000

ST_LineFromText

-- http://www.postgis.net/docs/ST_LineFromText.html
-- 使用给定的SRID从WKT创建几何。如果未提供SRID，则默认为0。如果传入的WKT不是LINESTRING，则返回null。
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
------------------------------------------------
01020000000200000000000000000... | t

ST_MLineFromText

-- http://www.postgis.net/docs/ST_MLineFromText.html
-- 使用给定的SRID，根据WKT创建几何。如果未提供SRID，则默认为0。
-- 如果WKT不是MULTILINESTRING，则返回null
geometry ST_MLineFromText(text WKT, integer srid);
geometry ST_MLineFromText(text WKT);

-- 实例
SELECT ST_MLineFromText('MULTILINESTRING((1 2, 3 4), (4 5, 6 7))');
--------------------------------------------------------------------
010500000002000000010200000002000000000000000000F03F0000000000000040000000000000084000000000000010400102000000020000000000000000001040000000000000144000000000000018400000000000001C40

ST_MPointFromText

-- http://www.postgis.net/docs/ST_MPointFromText.html
-- 使用给定的SRID从WKT创建几何。如果未提供SRID，则默认为0。
-- 如果WKT不是MULTIPOINT，则返回null
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

http://www.postgis.net/docs/ST_MPolyFromText.html
-- 使用给定的SRID从WKT创建几何。如果未提供SRID，则默认为0。
-- 如果WKT不是MULTIPOLYGON，则会引发错误
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_MakeBox2D

-- http://www.postgis.net/docs/ST_MakeBox2D.html
-- 根据边界框创建几何BOX2D 
box2d ST_MakeBox2D(geometry pointLowLeft, geometry pointUpRight);

-- 实例
无

ST_MakeLine

-- http://www.postgis.net/docs/ST_MakeLine.html
-- 创建一条LineString，可以接受Point，MultiPoint或LineString，其他几何类型会导致错误。
-- 形式1：接受两个输入几何
-- 变体2：接受一系列几何
-- 变体3：集合函数接受几何的行集。为了确保输入几何的顺序，请ORDER BY在函数调用或带ORDER BY子句的子查询中使用。
geometry ST_MakeLine(geometry geom1, geometry geom2);
geometry ST_MakeLine(geometry[] geoms_array);
geometry ST_MakeLine(geometry set geoms);

-- 实例
-- 两个点
SELECT ST_AsText( ST_MakeLine(ST_MakePoint(1,2), ST_MakePoint(3,4)) );
---------------------
LINESTRING(1 2,3 4)

SELECT ST_AsEWKT( ST_MakeLine(ST_MakePoint(1,2,3), ST_MakePoint(3,4,5) ));
-------------------------
LINESTRING(1 2 3,3 4 5)

select ST_AsText( ST_MakeLine( 'LINESTRING(0 0, 1 1)', 'LINESTRING(2 2, 3 3)' ) );
-----------------------------
LINESTRING(0 0,1 1,2 2,3 3)

-- 数组
SELECT ST_AsEWKT( ST_MakeLine(
        ARRAY[ ST_MakePoint(1,2,3), ST_MakePoint(3,4,5), ST_MakePoint(6,6,6) ]  
));
---------------------------------------------------------------------------------
LINESTRING(1 2 3,3 4 5,6 6 6)

-- 汇总
SELECT gps.track_id,ST_MakeLine ( gps.geom ORDER BY gps_time ) AS geom FROM gps_points AS gps GROUP BY track_id;

SELECT gps.track_id,ST_MakeLine ( gps.geom ) AS geom FROM
    ( SELECT track_id, gps_time, geom FROM gps_points ORDER BY track_id, gps_time ) AS gps 
GROUP BY track_id;

ST_MakePoint

-- http://www.postgis.net/docs/ST_MakePoint.html
-- 创建2D，3D Z或4D ZM点几何
-- 虽然不符合OGC，但ST_MakePoint比ST_GeomFromText 和ST_PointFromText更快，更精确。数字坐标值也更容易使用。
-- 使用ST_MakePointM来制作具有XYM坐标的点。
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);

-- 示例
-- Return point with unknown SRID
SELECT ST_MakePoint(-71.1043443253471, 42.3150676015829);

-- Return point marked as WGS 84 long lat
SELECT ST_SetSRID(ST_MakePoint(-71.1043443253471, 42.3150676015829),4326);

-- Return a 3D point (e.g. has altitude)
SELECT ST_MakePoint(1, 2,1.5);

-- Get z of point
SELECT ST_Z(ST_MakePoint(1, 2,1.5));
result
-------
1.5

ST_MakePointM

-- http://www.postgis.net/docs/ST_MakePointM.html
-- 根据三个double值创建一个point
-- 使用ST_MakePoint制作具有XY，XYZ或XYZM坐标的点。
geometry ST_MakePointM(float x, float y, float m);

-- 示例
-- 未知srid
SELECT ST_AsEWKT(  ST_MakePointM(-71.1043443253471, 42.3150676015829, 10)  );
-----------------------------------------------
 POINTM(-71.1043443253471 42.3150676015829 10)

-- WGS84
SELECT ST_AsEWKT( ST_SetSRID(  ST_MakePointM(-71.104, 42.315, 10),  4326));
---------------------------------------------------------
SRID=4326;POINTM(-71.104 42.315 10)

-- 创建点的度量
SELECT ST_M(  ST_MakePointM(-71.104, 42.315, 10)  );
-------
10

16.st_makePolygon

/ 根据一些LineString创建一个Polygon ，这些LineString必须是闭合的。
Polygon st_makePolygon(LineString shell)

17.st_point

// 根据两个double值创建一个point。是st_makePoint的别名。
Point st_point(Double x, Double y)

18.st_pointFromGeoHash

// 返回由Geohash字符串 (base-32)定义的边界框几何中心的点。
Point st_pointFromGeoHash(String geohash, Int prec)

19.st_pointFromText

// 根据WKT创建point
Point st_pointFromText(String wkt)

ST_PointFromWKB

-- http://www.postgis.net/docs/ST_PointFromWKB.html
-- 根据二进制表示形式WKB和空间参考系统ID（SRID），创建适当的几何类型的实例。
-- 此函数在SQL中扮演“几何工厂”的角色。这是ST_WKBToSQL的备用名称。
geometry ST_PointFromWKB(bytea geom);
geometry ST_PointFromWKB(bytea geom, integer srid);

-- 实例
SELECT ST_AsText ( ST_PointFromWKB ( ST_AsEWKB ( 'POINT(2 5)' :: geometry ) ) );
------------
POINT(2 5)

21.st_polygon

// 根据一些LineString创建一个Polygon ，这些LineString必须是闭合的。
Polygon st_polygon(LineString shell)

22.st_polygonFromText

// 根据WKT创建Polygon 。
Polygon st_polygonFromText(String wkt)

ST_GeomFromEWKT

-- http://www.postgis.net/docs/ST_GeomFromEWKT.html
-- 根据OGC扩展的知名文本（EWKT）表示构造一个PostGIS ST_Geometry对象。
-- EWKT格式不是OGC标准，而是PostGIS特定的格式，其中包括空间参考系统（SRID）标识符
-- 此功能支持3d，并且不会删除z-index。
-- 此方法支持圆弧线和曲线
-- 此功能支持多面曲面。
-- 此功能支持“三角形”和“不规则三角网”（TIN）。
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))');

--3d圆形字符串
SELECT ST_GeomFromEWKT('CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3)');

--多面曲面示例
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_SetSRID

-- http://www.postgis.net/docs/ST_SetSRID.html
-- 将几何体上的SRID设置为特定的整数值。在构造查询的边界框时很有用。
-- 此函数不会以任何方式转换几何坐标-只是设置定义定义该几何所在的空间参考系统的元数据。如果要将几何转换为新的投影，请使用ST_Transform。
-- 支持圆弧线和曲线
geometry ST_SetSRID(geometry geom, integer srid);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_SetSRID(ST_Point(-123.365556, 48.428611),3857) As wgs84long_lat;
---------------------------------------------------------------------------------
0101000020E6100000D236FE4465D75EC059DAA9B9DC364840

SELECT ST_AsEWKT(ST_SetSRID(ST_Point(-123.365556, 48.428611),4326)) As wgs84long_lat;
-------------------------------------------------------------------------------------
SRID=4326;POINT(-123.365556 48.428611)

SELECT ST_Transform(ST_SetSRID(ST_Point(-123.365556, 48.428611),4326),3785) As spere_merc;
-------------------------------------------------------------------------------------
0101000020C90E000010DC02DC8F316AC16457B6BDA6915741

SELECT ST_AsEWKT(ST_Transform(ST_SetSRID(ST_Point(-123.365556, 48.428611),4326),3785)) As spere_merc;
-------------------------------------------------------------------------------------
SRID=3785;POINT(-13732990.8753491 6178458.96425423)

Geometry 访问器

1.st_boundary

// 返回边界，如果geom为空，则返回适当维度的空geometry 。
Geometry st_boundary(Geometry geom)

2.st_coordDim

// 返回几何坐标geom的维数。
Int st_coordDim(Geometry geom)

3.st_dimension

// 返回此几何对象的固有维数，它必须小于或等于坐标维数。
Int st_dimension(Geometry geom)

4.st_envelope

// 返回表示geom边界框的Geometry。
Geometry st_envelope(Geometry geom)

5.st_exteriorRing

// 返回表示geometry外部环的行LineString ;如果Geometry不是Polygon，则返回null。
LineString st_exteriorRing(Geometry geom)

6.st_geometryN

// 如果该Geometry 是GeometryCollection，返回geom的第n个Geometry (从1开始);如果不是，则返回geom。
Int st_geometryN(Geometry geom, Int n)

7.st_interiorRingN

// 返回geom（Polygon 类型）的第n个内部LineString 环。如果geometry 不是Polygon 或给定的n超出范围，则返回null。
Int st_interiorRingN(Geometry geom, Int n)

8.st_isClosed

// 如果geom是一个LineString或MultiLineString，并且它的起点和终点是重合的，则返回true。对所有其他Geometry返回true。
Boolean st_isClosed(Geometry geom)

9.st_isCollection

// 如果geom是一个GeometryCollection，则返回true。
Boolean st_isCollection(Geometry geom)

10.st_isEmpty

// 如果geom为空，则返回true。
Boolean st_isEmpty(Geometry geom)

11.st_isRing

// 如果geom是一个LineString或一个MultiLineString，并且是封闭的，则返回true。
Boolean st_isRing(Geometry geom)

12.st_isSimple

// 如果geom没有异常的几何点，如存在相交点或者相切，则返回true。
Boolean st_isSimple(Geometry geom)

13.st_isValid

// 如果Geometry 根据OGC SFS规范在拓扑上有效，则返回true。
Boolean st_isValid(Geometry geom)

14.st_numGeometries

// 如果geom是GeometryCollection，则返回geometries的数量。对于单个几何图形，返回1。
Int st_numGeometries(Geometry geom)

15.st_numPoints

// 返回几何geom中的顶点数。
Int st_numPoints(Geometry geom)

16.st_pointN

// 如果geom是一个LineString，则返回geom的第n个顶点作为一个点。负值从LineString的末尾向后计数。如果geom不是一个LineString，则返回null。
Point st_pointN(Geometry geom, Int n)

17.st_x

// 如果geom是一个点，返回该点的X坐标。
Float st_X(Geometry geom)

18.st_y

// 如果geom是一个点，返回该点的Y坐标。
Float st_y(Geometry geom)

Geometry投影

1.st_castToLineString

// 将Geometry 类型的g投影成LineString。
LineString st_castToLineString(Geometry g)

2.st_castToPoint

// 将Geometry 类型的g投影成point。
Point st_castToPoint(Geometry g)

3.st_castToPolygon

// 将Geometry 类型的g投影成Polygon。
Polygon st_castToPolygon(Geometry g)

4.st_castToGeometry

// 将几何子类g强制转换为Geometry。在case class中，使用st_makePoint输出一个Geometry 时，这个函数是必要的。
Geometry st_castToGeometry(Geometry g)

5.st_byteArray

// 将一个字符串使用UTF8转为Byte数组。
Array[Byte] st_byteArray(String s)

Geometry编辑器

ST_Translate

-- http://www.postgis.net/docs/ST_Translate.html
-- 对坐标进行偏移，deltax:经度偏移量 deltay:纬度偏移量 deltaz:高度偏移量。
-- 返回一个新几何，其坐标转换为delta x，delta y，delta z单位。单位基于此几何的空间参考（SRID）中定义的单位。
-- 此方法支持圆弧线和曲线
-- 此功能支持3d，并且不会删除z-index。
geometry ST_Translate(geometry g1, float deltax, float deltay);
geometry ST_Translate(geometry g1, float deltax, float deltay, float deltaz);

-- 示例
-- 将点移动1度经度
SELECT ST_AsText(ST_Translate(ST_GeomFromText('POINT(-71.01 42.37)',4326),1,0)) As wgs_transgeomtxt;
---------------------
POINT(-70.01 42.37)

-- 移动经度1度和移动纬度0.5度
SELECT ST_AsText(ST_Translate(ST_GeomFromText('LINESTRING(-71.01 42.37,-71.11 42.38)',4326),1,0.5)) As wgs_transgeomtxt;
---------------------------------------
LINESTRING(-70.01 42.87,-70.11 42.88)

-- 移动3d
SELECT ST_AsEWKT(ST_Translate(CAST('POINT(1 1 1)' As geometry), 5, 12,3));
-------------
POINT(6 13 4)

-- 移动曲线和点
SELECT ST_AsText(ST_Translate(ST_Collect('CURVEPOLYGON(CIRCULARSTRING(4 3,3.12 0.878,1 0,-1.121 5.1213,6 7, 8 9,4 3))','POINT(1 3)'),1,2));
------------------------------------------------------------------------------------------------------------
GEOMETRYCOLLECTION(CURVEPOLYGON(CIRCULARSTRING(5 5,4.12 2.878,2 2,-0.121 7.1213,7 9,9 11,5 5)),POINT(2 5))

Geometry 输出

ST_AsBinary

-- http://www.postgis.net/docs/ST_AsBinary.html
-- 返回几何的二进制表示形式。
-- 该功能有2个变体。第一个变体不带字节序编码参数，默认为服务器计算机字节序。第二个变体采用第二个参数表示编码-使用小尾数（'NDR'）或大尾数（'XDR'）编码。
-- 此方法支持圆弧线和曲线
-- 此功能支持多面曲面。
-- 此功能支持“三角形”和“不规则三角网”（TIN）。
-- 此功能支持3d，并且不会删除z-index。
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));
------------------------------------------------------------------------------------------------
\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

SELECT ST_AsBinary(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',4326), 'XDR');
------------------------------------------------------------------------------------------------
\000\000\000\000\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?\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\000\000\000\000\000\000

ST_AsGeoJSON

-- http://www.postgis.net/docs/ST_AsGeoJSON.html
-- 返回几何的json形式。
-- maxdecimaldigits参数可用于减少输出中使用的最大小数位数（默认为9）。如果您正在使用EPSG：4326并且仅输出几何图形以供显示，则maxdecimaldigits= 6是许多地图的不错选择。
-- options参数可用于在GeoJSON输出中添加BBOX或CRS：【0：表示无选择 1：GeoJSON BBOX 2：GeoJSON短CRS（例如EPSG：4326）4：GeoJSON Long CRS（例如urn：ogc：def：crs：EPSG :: 4326）8：如果不是EPSG：4326，则为GeoJSON短CRS（默认）】
-- 此功能支持3d，并且不会删除z-index。
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 ST_AsGeoJSON(t.*)
FROM (VALUES
  (1, 'one', 'POINT(1 1)'::geometry),
  (2, 'two', 'POINT(2 2)'),
  (3, 'three', 'POINT(3 3)'))
AS t(id, name, geom);
-----------------------------------------------------------------------------------------------------------
 {"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_AsLatLonText

-- http://www.postgis.net/docs/ST_AsLatLonText.html
-- 返回一个字符串，该字符串以度、分和秒（degrees, minutes, and seconds）为单位描述点p的经纬度。(假设p的坐标单位是经纬度)
text ST_AsLatLonText(geometry pt, text format='');

-- 示例
-- 默认
SELECT (ST_AsLatLonText('POINT (-3.2342342 -2.32498)'));
----------------------------
 2°19'29.928"S 3°14'3.243"W
-- 自定义格式
SELECT (ST_AsLatLonText('POINT (-3.2342342 -2.32498)', 'D°M''S.SSS"C'));
----------------------------
 2°19'29.928"S 3°14'3.243"W

SELECT (ST_AsLatLonText('POINT (-3.2342342 -2.32498)', 'D degrees, M minutes, S seconds to the C'));
--------------------------------------------------------------------------------------
 2 degrees, 19 minutes, 30 seconds to the S 3 degrees, 14 minutes, 3 seconds to the W
-- 其他
-- Signed degrees instead of cardinal directions.
SELECT (ST_AsLatLonText('POINT (-3.2342342 -2.32498)', 'D°M''S.SSS"'));
----------------------------
 -2°19'29.928" -3°14'3.243"

-- Decimal degrees.
SELECT (ST_AsLatLonText('POINT (-3.2342342 -2.32498)', 'D.DDDD degrees C'));
-----------------------------------
 2.3250 degrees S 3.2342 degrees W

-- Excessively large values are normalized.
SELECT (ST_AsLatLonText('POINT (-302.2342342 -792.32498)'));
-------------------------------
 72°19'29.928"S 57°45'56.757"E

ST_AsText

-- http://www.postgis.net/docs/ST_AsText.html
-- 返回几何/地理的文本表示，可选参数可用于减少在输出中使用浮点数后的最大十进制数（默认为15）。
-- 此方法支持圆弧线和曲线。
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('01030000000100000005000000000000000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F000000000000F03F000000000000000000000000000000000000000000000000000000000000'); 
----------------------------------------------------------------------------------------------------------
POLYGON((0 0,0 0,0 0,0 0,0 0))

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_AsText(GeomFromEWKT('SRID=4326;POINT(111.1111111 1.1111111)'))
-----------------------------------------------------------------------------------------------------------
 POINT(111.1111111 1.1111111)

ST_GeoHash

-- http://www.postgis.net/docs/ST_GeoHash.html
-- 返回几何geom内部点的Geohash(以base-32表示)。
-- 如果未maxchars指定，则ST_GeoHash基于输入几何类型的全精度返回GeoHash。
text ST_GeoHash(geometry geom, integer maxchars=full_precision_of_point);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326));
----------------------
c0w3hf1s70w3hf1s70w3

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_GeoHash(ST_SetSRID(ST_MakePoint(-126,48),4326),5);
------------
c0w3h

Spatial 关系

ST_Area

-- http://www.postgis.net/docs/ST_Area.html
-- 返回多边形几何的面积。对于几何类型，将使用SRID指定的单位来计算2D笛卡尔（平面）区域。对于地理类型，默认情况下，面积是在椭球体上确定的，单位为平方米。要使用更快但不太准确的球形模型来计算面积ST_Area(geog,false)。
-- 对于非面的geometries （Points，非闭合的LineStrings），返回0.0。
-- 此功能支持多面曲面。
float ST_Area(geometry g1);
float ST_Area(geography geog, boolean use_spheroid=true);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT
    ST_Area ( geom ) sqft,
    ST_Area ( geom ) * 0.3048 ^ 2 sqm 
FROM
    ( SELECT 'SRID=2249;POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))' :: geometry geom ) subquery;
----------------------------------------------------------------------------------------------------------
┌─────────┬─────────────┐
│  sqft   │     sqm     │
├─────────┼─────────────┤
│ 928.625 │ 86.27208552 │
└─────────┴─────────────┘

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT
    ST_Area ( geom ) sqft,
    ST_Area (
    ST_Transform ( geom, 26986 )) AS sqm 
FROM
    ( SELECT 'SRID=2249;POLYGON((743238 2967416,743238 2967450,
743265 2967450,743265.625 2967416,743238 2967416))' :: geometry geom ) subquery;
----------------------------------------------------------------------------------------------------------
┌─────────┬─────────────────┐
│  sqft   │       sqm       │
├─────────┼─────────────────┤
│ 928.625 │ 86.272430607008 │
└─────────┴─────────────────┘

ST_Centroid

-- http://www.postgis.net/docs/ST_Centroid.html
-- 返回geometry的几何中心，或等效地将几何的质心计算为POINT。
-- 对于[ MULTI] POINT，将其计算为输入坐标的算术平均值。对于[ MULTI] LINESTRING，将其计算为每个线段的加权长度。对于[ MULTI] POLYGON，“重量”是根据面积来考虑的。如果提供了空的几何体，GEOMETRYCOLLECTION则返回一个空 的。如果 NULL提供，NULL则返回。如果 提供CIRCULARSTRING或COMPOUNDCURVE，则首先将它们转换为CurveToLine的线串，然后与 LINESTRING‘
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 )'));
------------------------------------------
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_ClosestPoint

-- http://www.postgis.net/docs/ST_ClosestPoint.html
-- 返回g1到g2最近的Point ，也就是最短线上的第一个点。
geometry ST_ClosestPoint(geometry g1, geometry g2);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT
    ST_AsText (
    ST_ClosestPoint ( pt, line )) AS cp_pt_line,
    ST_AsText (
    ST_ClosestPoint ( line, pt )) AS cp_line_pt 
FROM
    ( SELECT 'POINT(100 100)' :: geometry AS pt, 'LINESTRING (20 80, 98 190, 110 180, 50 75 )' :: geometry AS line ) AS foo;
------------------------------------------------------------------------------------
   cp_pt_line   |                cp_line_pt
----------------+------------------------------------------
 POINT(100 100) | POINT(73.0769230769231 115.384615384615)

 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 SELECT
    ST_AsText ( ST_ClosestPoint ( ST_GeomFromText ( 'POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))' ), ST_Buffer ( ST_GeomFromText ( 'POINT(110 170)' ), 20 ) ) ) AS ptwkt;
------------------------------------------------------------------------------------
                  ptwkt
------------------------------------------
 POINT(140.752120669087 125.695053378061)

ST_Contains

-- http://www.postgis.net/docs/ST_Contains.html
-- 查询一个点是否在多边形内,返回t表示在范围内,返回f表示不在范围内
-- 当且仅当B的点不位于A的外部且B的内部的至少一个点位于A的内部时，几何A才包含几何B。此定义的一个重要微妙之处在于A不包含其B边界，但A确实包含自身。将其与ST_ContainsProperly进行对比，其中几何A不包含其本身。
-- 如果几何B完全在几何A内，则返回TRUE。为使此函数有意义，源几何必须都具有相同的坐标投影，并且具有相同的SRID。ST_Contains是ST_Within的逆。因此，ST_Contains（A，B）隐含ST_Within（B，A），除非几何无效，无论结果是否定义，结果始终为假。
-- ST_Contains和ST_Within的某些微妙之处在直观上并不明显。
boolean ST_Contains(geometry geomA, geometry geomB);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_Contains(smallc, bigc) As smallcontainsbig,
       ST_Contains(bigc,smallc) As bigcontainssmall,
       ST_Contains(bigc, ST_Union(smallc, bigc)) as bigcontainsunion,
       ST_Equals(bigc, ST_Union(smallc, bigc)) as bigisunion,
       ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
       ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
-- Result
-------------------------------------------------------------------------------------------------------
smallcontainsbig | bigcontainssmall | bigcontainsunion | bigisunion | bigcoversexterior | bigcontainsexterior
------------------+------------------+------------------+------------+-------------------+---------------------
 f                | t                | t                | t          | t          | f

 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 SELECT
    ST_GeometryType ( geomA ) AS geomtype,
    ST_Contains ( geomA, geomA ) AS acontainsa,
    ST_ContainsProperly ( geomA, geomA ) AS acontainspropa,
    ST_Contains (geomA,ST_Boundary ( geomA )) AS acontainsba,
    ST_ContainsProperly (geomA,ST_Boundary ( geomA )) AS acontainspropba 
FROM
    (VALUES
        ( ST_Buffer ( ST_Point ( 1, 1 ), 5, 1 ) ),
        ( ST_MakeLine ( ST_Point ( 1, 1 ), ST_Point ( - 1,- 1 ) ) ),
    ( ST_Point ( 1, 1 ) ) 
    ) AS foo ( geomA );
-- Result    
--------------+------------+----------------+-------------+-----------------
  geomtype    | acontainsa | acontainspropa | acontainsba | acontainspropba
--------------+------------+----------------+-------------+-----------------
ST_Polygon    | t          | f              | f           | f
ST_LineString | t          | f              | f           | f
ST_Point      | t          | t              | f           | f

ST_Covers

-- http://www.postgis.net/docs/ST_Covers.html
-- 查询一个几何是否在另一个几何里面
-- 如果几何/地理B中的任何点都不在几何/地理A之外，则返回1（TRUE）【A包含B】
-- 此函数调用将自动包括边界框比较，该比较将利用几何上可用的任何索引。
boolean ST_Covers(geometry geomA, geometry geomB);
boolean ST_Covers(geography geogpolyA, geography geogpointB);

-- 几何示例【一个圆覆盖了一个圆】
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_Covers(smallc,smallc) As smallinsmall,
    ST_Covers(smallc, bigc) As smallcoversbig,
    ST_Covers(bigc, ST_ExteriorRing(bigc)) As bigcoversexterior,
    ST_Contains(bigc, ST_ExteriorRing(bigc)) As bigcontainsexterior
FROM (SELECT ST_Buffer(ST_GeomFromText('POINT(1 2)'), 10) As smallc,
    ST_Buffer(ST_GeomFromText('POINT(1 2)'), 20) As bigc) As foo;
--Result
--------------+----------------+-------------------+---------------------
 smallinsmall | smallcoversbig | bigcoversexterior | bigcontainsexterior
--------------+----------------+-------------------+---------------------
 t            | f              | t                 | f

 -- 地理实例【一个有300米缓冲的点与一个点，一个点和它的10米缓冲】
 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
 SELECT
    ST_Covers ( geog_poly, geog_pt ) AS poly_covers_pt,
    ST_Covers ( ST_Buffer ( geog_pt, 10 ), geog_pt ) AS buff_10m_covers_cent 
FROM
    ( SELECT ST_Buffer ( ST_GeogFromText ( 'SRID=4326;POINT(-99.327 31.4821)' ), 300 ) AS geog_poly, ST_GeogFromText ( 'SRID=4326;POINT(-99.33 31.483)' ) AS geog_pt ) AS foo;
--Result
----------------+------------------
 poly_covers_pt | buff_10m_covers_cent
----------------+------------------
 f              | t

ST_Crosses

-- http://www.postgis.net/docs/ST_Crosses.html
-- 如果提供的Geometry存在相同的内部点，则返回true（几何具有一些内部点，但并非所有内部点都相同）。
boolean ST_Crosses(geometry g1, geometry g2);
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE roads (
  id serial NOT NULL,
  the_geom geometry,
  CONSTRAINT roads_pkey PRIMARY KEY (road_id)
);
--------------------------------------------------------------------------------------
CREATE TABLE highways (
  id serial NOT NULL,
  the_gem geometry,
  CONSTRAINT roads_pkey PRIMARY KEY (road_id)
);
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT roads.id FROM roads, highways WHERE ST_Crosses(roads.the_geom, highways.the_geom);

ST_Disjoint

-- http://www.postgis.net/docs/ST_Disjoint.html
-- geometries 不相交，则返回true
boolean ST_Disjoint( geometry A , geometry B );

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
--Result
---------------
 st_disjoint
---------------
 t

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_Disjoint('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
--Result
---------------
 st_disjoint
---------------
 f

ST_Distance

-- http://www.postgis.net/docs/ST_Distance.html
-- 方法支持圆弧线和曲线
-- 为几何类型返回两个几何图形之间的最小二维笛卡尔(平面)距离，在投影单位(空间参考单位)。
float ST_Distance(geometry g1, geometry g2);
-- 对于地理类型默认返回两个地理位置之间的最小测地线距离(以米为单位)，在SRID确定的椭球上计算。如果use_spheroid为false，则使用更快的球形计算。
float ST_Distance(geography geog1, geography geog2, >boolean use_spheroid=true);

-- 几何示例-平面度为4326的单位为WGS 84长纬度，单位为度。
SELECT ST_Distance(
    'SRID=4326;POINT(-72.1235 42.3521)'::geometry,
    'SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry
);
st_distance
-----------------
0.00150567726382282

-- 几何示例-单位为米（SRID：3857，与流行的网络地图上的像素成比例）。尽管该值不可用，但是可以正确比较附近的值，这使其成为KNN或KMeans等算法的不错选择。
SELECT ST_Distance(
    ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
    ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)
);
st_distance
-----------------
167.441410065196

-- 几何示例-以米为单位（SRID：如上所述为3857，但已通过cos（lat）进行了校正以解决变形）
SELECT ST_Distance(
    ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 3857),
    ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 3857)
) * cosd(42.3521);
st_distance
-----------------
123.742351254151

-- 几何示例-以米为单位的单位（SRID：26986马萨诸塞州平面飞机米）（对于马萨诸塞州而言最准确）
SELECT ST_Distance(
    ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 26986),
    ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 26986)
);
st_distance
-----------------
123.797937878454

-- 几何示例-单位为米（SRID：2163美国国家地图集相等区域）（最不准确）
SELECT ST_Distance(
    ST_Transform('SRID=4326;POINT(-72.1235 42.3521)'::geometry, 2163),
    ST_Transform('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)'::geometry, 2163)
);
st_distance
------------------
126.664256056812

ST_DistanceSphere

-- http://www.postgis.net/docs/ST_DistanceSphere.html
-- 假设地球是球型，返回两个geometries 之间的最小距离。
-- 比ST_DistanceSpheroid快，但准确性较低。
float ST_DistanceSphere(geometry geomlonlatA, geometry geomlonlatB);

-- 示例
SELECT
    round( CAST ( ST_DistanceSphere ( ST_Centroid ( the_geom ), ST_GeomFromText ( 'POINT(-118 38)', 4326 )) AS NUMERIC ), 2 ) AS dist_meters,
    round( CAST ( ST_Distance ( ST_Transform ( ST_Centroid ( the_geom ), 32611 ), ST_Transform ( ST_GeomFromText ( 'POINT(-118 38)', 4326 ), 32611 )) AS NUMERIC ), 2 ) AS dist_utm11_meters,
    round( CAST ( ST_Distance ( ST_Centroid ( the_geom ), ST_GeomFromText ( 'POINT(-118 38)', 4326 )) AS NUMERIC ), 5 ) AS dist_degrees,
    round( CAST ( ST_Distance ( ST_Transform ( the_geom, 32611 ), ST_Transform ( ST_GeomFromText ( 'POINT(-118 38)', 4326 ), 32611 )) AS NUMERIC ), 2 ) AS min_dist_line_point_meters 
FROM ( SELECT ST_GeomFromText ( 'LINESTRING(-118.584 38.374,-118.583 38.5)', 4326 ) AS the_geom ) AS foo;
    ------------- + ------------------- + -------------- + ---------------------------- 
        70424.47 | 70438.00 | 0.72900 | 65871.18

ST_DistanceSpheroid

-- http://www.postgis.net/docs/ST_Distance_Spheroid.html
-- 假设地球为WGS84球型，返回两个geometries之间的最小距离。
float ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT
    round( CAST ( ST_DistanceSpheroid ( ST_Centroid ( the_geom ), ST_GeomFromText ( 'POINT(-118 38)', 4326 ), 'SPHEROID["WGS 84",6378137,298.257223563]' ) AS NUMERIC ), 2 ) AS dist_meters_spheroid,
    round( CAST ( ST_DistanceSphere ( ST_Centroid ( the_geom ), ST_GeomFromText ( 'POINT(-118 38)', 4326 )) AS NUMERIC ), 2 ) AS dist_meters_sphere,
    round(
        CAST ( ST_Distance ( ST_Transform ( ST_Centroid ( the_geom ), 32611 ), ST_Transform ( ST_GeomFromText ( 'POINT(-118 38)', 4326 ), 32611 )) AS NUMERIC ),
        2 
    ) AS dist_utm11_meters 
FROM
    ( SELECT ST_GeomFromText ( 'LINESTRING(-118.584 38.374,-118.583 38.5)', 4326 ) AS the_geom ) AS foo;
----------------------+--------------------+-------------------
dist_meters_spheroid | dist_meters_sphere | dist_utm11_meters
----------------------+--------------------+-------------------
         70454.92 |           70424.47 |          70438.00

ST_Equals

-- http://www.postgis.net/docs/ST_Equals.html
-- 判断两个Geometries 是否相同，不考虑方向。
-- 要验证点的顺序是否一致，请使用ST_OrderingEquals
-- 如果任一几何均无效，则该函数将返回false，除非它们是二进制相等的。
boolean ST_Equals(geometry A, geometry B);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
        ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
 st_equals
-----------
 t

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT ST_Equals(ST_Reverse(ST_GeomFromText('LINESTRING(0 0, 10 10)')),
        ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
 st_equals
-----------
 t

ST_Intersects

http://www.postgis.net/docs/ST_Intersects.html
-- 如果两个geometries 在二维空间相交，则返回true。相当于NOT st_disjoint(a, b)。
-- 对于地理区域-公差为0.00001米（因此，任何靠近的点都视为相交）
boolean ST_Intersects( geometry geomA , geometry geomB );
boolean ST_Intersects( geography geogA , geography geogB );

-- 几何范例
SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 2 0, 0 2 )'::geometry);
 st_intersects
---------------
 f

SELECT ST_Intersects('POINT(0 0)'::geometry, 'LINESTRING ( 0 0, 0 2 )'::geometry);
 st_intersects
---------------
 t

-- 在表中查找。确保表在几何列上具有GiST索引，以加快查找速度。
SELECT id, name FROM cities WHERE ST_Intersects(geom, 'SRID=4326;POLYGON((28 53,27.707 52.293,27 52,26.293 52.293,26 53,26.293 53.707,27 54,27.707 53.707,28 53))');
 id | name
----+-------
  2 | Minsk

-- 地理范例
SELECT ST_Intersects(
    'SRID=4326;LINESTRING(-43.23456 72.4567,-43.23456 72.4568)'::geography,
    'SRID=4326;POINT(-43.23456 72.4567772)'::geography
);
st_intersects
---------------
t

ST_Length

-- http://www.postgis.net/docs/ST_Length.html
-- 返回线性geometries的二维路径长度，或面积geometries的周长，单位为坐标参考系统。如果为其他几何类型(例如Point)，返回0.0。
-- 对于几何类型：如果几何是LineString，MultiLineString，ST_Curve，ST_MultiCurve，则返回几何的2D笛卡尔长度。对于面几何，返回0；否则，返回0。请改用ST_Perimeter。长度单位由几何的空间参考系统确定。
-- 对于地理类型：使用反测地线计算执行计算。长度单位为米。如果PostGIS使用PROJ版本4.8.0或更高版本编译，则该椭球体由SRID指定，否则它是WGS84独有的。如果为use_spheroid=false，则计算基于球体而不是球体。
float ST_Length(geometry a_2dlinestring);
float ST_Length(geography geog, boolean use_spheroid=true);

-- 以英尺为单位
SELECT ST_Length(ST_GeomFromText('LINESTRING(743238 2967416,743238 2967450,743265 2967450,743265.625 2967416,743238 2967416)',2249));
---------
st_length
---------
 122.630744000095

--Transforming WGS 84 LineString to Massachusetts state plane meters
SELECT ST_Length(
    ST_Transform(
        ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)'),
        26986
    )
);
---------
st_length
---------
34309.4563576191

-- the default calculation uses a spheroid
SELECT
    ST_Length ( the_geog ) AS length_spheroid,
    ST_Length ( the_geog, FALSE ) AS length_sphere 
FROM
    ( SELECT ST_GeographyFromText ( 'SRID=4326;LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)' ) AS the_geog ) AS foo;

 length_spheroid  |  length_sphere
------------------+------------------
 34310.5703627288 | 34346.2060960742

ST_LengthSpheroid

-- http://postgis.net/docs/manual-3.0/ST_Length_Spheroid.html
-- 假设地球为WGS84球型，返回LineString路径长度。返回的长度以米为单位。
float ST_LengthSpheroid(geometry a_geometry, spheroid a_spheroid);

-- 参数描述
SPHEROID[<NAME>,<SEMI-MAJOR AXIS>,<INVERSE FLATTENING>]
SPHEROID["GRS_1980",6378137,298.257222101]

-- 实例
SELECT
    ST_LengthSpheroid ( the_geom, sph_m ) AS tot_len,
    ST_LengthSpheroid ( ST_GeometryN ( the_geom, 1 ), sph_m ) AS len_line1,
    ST_LengthSpheroid ( ST_GeometryN ( the_geom, 2 ), sph_m ) AS len_line2 
FROM
    ( SELECT ST_GeomFromText ( 'MULTILINESTRING((-118.584 38.374,-118.583 38.5),
    (-71.05957 42.3589 , -71.061 43))' ) AS the_geom, CAST ( 'SPHEROID["GRS_1980",6378137,298.257222101]' AS spheroid ) AS sph_m ) AS foo;
------------------+------------------+------------------
    tot_len      |    len_line1     |    len_line2
------------------+------------------+------------------
 85204.5207562955 | 13986.8725229309 | 71217.6482333646

-- 3D
SELECT
    ST_LengthSpheroid ( the_geom, sph_m ) AS tot_len,
    ST_LengthSpheroid ( ST_GeometryN ( the_geom, 1 ), sph_m ) AS len_line1,
    ST_LengthSpheroid ( ST_GeometryN ( the_geom, 2 ), sph_m ) AS len_line2 
FROM
    ( SELECT ST_GeomFromEWKT ( 'MULTILINESTRING((-118.584 38.374 20,-118.583 38.5 30),
    (-71.05957 42.3589 75, -71.061 43 90))' ) AS the_geom, CAST ( 'SPHEROID["GRS_1980",6378137,298.257222101]' AS spheroid ) AS sph_m ) AS foo;
------------------+-----------------+------------------
     tot_len      |    len_line1    |    len_line2
------------------+-----------------+------------------
 85204.5259107402 | 13986.876097711 | 71217.6498130292

st_overlaps

-- http://www.postgis.net/docs/ST_Overlaps.html
-- 如果两个geometries存在共同点，并且具有相同的维数，并且两个geometries的交集和geometries本身的维数相同，则返回true。
-- 它们相交，但是一个并不完全包含另一个。
boolean ST_Overlaps(geometry A, geometry B);

-- 实例
-- 直线上的点由直线所包含，且维数较低，因此不与直线重叠也没有穿过
SELECT
    ST_Overlaps ( A, b ) AS a_overlap_b,
    ST_Crosses ( A, b ) AS a_crosses_b,
    ST_Intersects ( A, b ) AS a_intersects_b,
    ST_Contains ( b, A ) AS b_contains_a 
FROM
    ( SELECT ST_GeomFromText ( 'POINT(1 0.5)' ) AS A, ST_GeomFromText ( 'LINESTRING(1 0, 1 1, 3 5)' ) AS b ) AS foo
------------+-------------+----------------+--------------
a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a
------------+-------------+----------------+--------------
f           | f           | t              | t

-- 一条部分被圆所包含，但未全部被定义为相交和交叉的线，但由于维数不同，所以不重叠
SELECT
    ST_Overlaps ( A, b ) AS a_overlap_b,
    ST_Crosses ( A, b ) AS a_crosses_b,
    ST_Intersects ( A, b ) AS a_intersects_b,
    ST_Contains ( A, b ) AS a_contains_b 
FROM
    ( SELECT ST_Buffer ( ST_GeomFromText ( 'POINT(1 0.5)' ), 3 ) AS A, ST_GeomFromText ( 'LINESTRING(1 0, 1 1, 3 5)' ) AS b ) AS foo;
-------------+-------------+----------------+--------------
 a_overlap_b | a_crosses_b | a_intersects_b | a_contains_b
-------------+-------------+----------------+--------------
 f           | t           | t              | f

-- 一个与圆相交的二维弯曲热狗(又名缓冲线串)，
-- 但不完全包含在圆中，因为它们的维数相同，所以定义为重叠，
-- 但它不相交，因为这两个向量的交点维数相同
-- 为2的最大维数
SELECT
    ST_Overlaps ( A, b ) AS a_overlap_b,
    ST_Crosses ( A, b ) AS a_crosses_b,
    ST_Intersects ( A, b ) AS a_intersects_b,
    ST_Contains ( b, A ) AS b_contains_a,
    ST_Dimension ( A ) AS dim_a,
    ST_Dimension ( b ) AS dim_b,
    ST_Dimension (
    ST_Intersection ( A, b )) AS dima_intersection_b 
FROM
    ( SELECT ST_Buffer ( ST_GeomFromText ( 'POINT(1 0.5)' ), 3 ) AS A, ST_Buffer ( ST_GeomFromText ( 'LINESTRING(1 0, 1 1, 3 5)' ), 0.5 ) AS b ) AS foo;
-------------+-------------+----------------+--------------+-------+-------+---------------------
 a_overlap_b | a_crosses_b | a_intersects_b | b_contains_a | dim_a | dim_b | dima_intersection_b
-------------+-------------+----------------+--------------+-------+-------+---------------------
 t           | f           | t              | f            |     2 |     2 |              2

ST_Relate

-- http://www.postgis.net/docs/ST_Relate.html
-- ST_Relate 比较两个几何，如果几何满足 DE-9IM 模式矩阵字符串指定的条件，则返回 1（Oracle 和 SQLite）或 t (PostgreSQL)；否则，返回 0（Oracle 和 SQLite）或 f (PostgreSQL)。
-- DE-9IM 模式矩阵：https://desktop.arcgis.com/zh-cn/arcmap/latest/manage-data/using-sql-with-gdbs/relational-functions-for-st-geometry.htm#GUID-49A0D330-D62D-429F-A5A8-497D627E2D20
boolean ST_Relate(geometry geomA, geometry geomB, text intersectionMatrixPattern);

-- 返回 3x3矩阵，描述两个geometries的内部、边界和外部之间的交集的维数。
text ST_Relate(geometry geomA, geometry geomB);
text ST_Relate(geometry geomA, geometry geomB, integer BoundaryNodeRule);

-- 实例
SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2));
st_relate
-----------
0FFFFF212

SELECT ST_Relate(ST_GeometryFromText('LINESTRING(1 2, 3 4)'), ST_GeometryFromText('LINESTRING(5 6, 7 8)'));
st_relate
-----------
FF1FF0102


SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '0FFFFF212');
st_relate
-----------
t

SELECT ST_Relate(ST_GeometryFromText('POINT(1 2)'), ST_Buffer(ST_GeometryFromText('POINT(1 2)'),2), '*FF*FF212');
st_relate
-----------
t

ST_Touches

-- http://www.postgis.net/docs/ST_Touches.html
-- 如果geometries 至少有一个共同点，但内部不相交，则返回true。
-- ST_Touches关系适用于所有面积/面积，线/线，线/面积，点/面积和点/线对关系，但不适用于点/点。
boolean ST_Touches(geometry g1, geometry g2);

-- 实例
SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(1 1)'::geometry);
 st_touches
------------
 f
(1 row)

SELECT ST_Touches('LINESTRING(0 0, 1 1, 0 2)'::geometry, 'POINT(0 2)'::geometry);
 st_touches
------------
 t
(1 row)

st_within

-- http://www.postgis.net/docs/ST_Within.html
-- 如果几何体A完全在几何体B内，则返回TRUE。
-- 为了使此函数有意义，源几何体必须都具有相同的坐标投影，并且具有相同的SRID。假设如果ST_Within（A，B）为true并且ST_Within（B，A）为true，则认为这两个几何在空间上相等。
boolean ST_Within(geometry A, geometry B);

-- 实例
-- 一个圆中的一个圆
SELECT
    ST_Within ( smallc, smallc ) AS smallinsmall,
    ST_Within ( smallc, bigc ) AS smallinbig,
    ST_Within ( bigc, smallc ) AS biginsmall,
    ST_Within ( ST_Union ( smallc, bigc ), bigc ) AS unioninbig,
    ST_Within (bigc, ST_Union ( smallc, bigc )) AS biginunion,
    ST_Equals (bigc, ST_Union ( smallc, bigc )) AS bigisunion 
FROM
    ( SELECT ST_Buffer ( ST_GeomFromText ( 'POINT(50 50)' ), 20 ) AS smallc, ST_Buffer ( ST_GeomFromText ( 'POINT(50 50)' ), 40 ) AS bigc ) AS foo;
--------------+------------+------------+------------+------------+------------
 smallinsmall | smallinbig | biginsmall | unioninbig | biginunion | bigisunion
--------------+------------+------------+------------+------------+------------
 t            | t          | f          | t          | t          | t
(1 row)

ST_antimeridianSafeGeom

-- https://help.aliyun.com/document_detail/129915.html?spm=5176.13910061.sslink.31.5cd7762at5J6mW
-- 如果geom跨越antimeridian，则尝试将几何转换为“antimeridian-safe”的等效形式（即输出几何由BOX（-180 -90,180,90）覆盖）。在某些情况下，此方法可能会失败，在这种情况下将返回输入几何，并将记录错误。
Geometry st_antimeridianSafeGeom(Geometry geom)

-- 实例
暂无

ST_SimplifyPreserveTopology

-- http://www.postgis.net/docs/ST_SimplifyPreserveTopology.html
-- 对数据进行抽稀，使用Douglas-Peucker算法返回给定几何的“简化”版本。
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;
-----------+-------------------+---------------------+-------------+---------------+-------------------
 np_before | np01_notbadcircle | np05_notquitecircle | np1_octagon | np10_square | np100_stillsquare
-----------+-------------------+---------------------+-------------+---------------+-------------------
        49 |                33 |                  17 |           9 |             5 |                 5

ST_Simplify

-- http://www.postgis.net/docs/ST_Simplify.html
-- 对数据进行抽稀，使用Douglas-Peucker算法返回给定几何的“简化”版本。
-- “保留已折叠”标志将保留对象，否则该对象在给定的公差范围内会太小。例如，一条1m长的线简化为10m的公差。如果给出了保留标志，该行将不会消失。该标志对于渲染引擎很有用，可避免大量非常小的对象从地图上消失，从而留下令人惊讶的间隙。
geometry ST_Simplify(geometry geomA, float tolerance);

-- 示例
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_NPoints

-- http://www.postgis.net/docs/ST_NPoints.html
-- 返回几何中的点数。适用于所有几何形状。
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)'));
----------
4

--三维空间中的多边形
SELECT ST_NPoints(ST_GeomFromEWKT('LINESTRING(77.29 29.07 1,77.42 29.26 0,77.27 29.31 -1,77.29 29.07 3)'))
----------
4

ST_Buffer

-- http://www.postgis.net/docs/ST_Buffer.html
-- ST_Buffer 获取几何对象和距离，然后返回表示围绕源对象的缓冲区的几何对象。
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_NPoints ( ST_Buffer ( ST_GeomFromText ( 'POINT(100 90)' ), 50 )) AS promisingcircle_pcount,
    ST_NPoints ( ST_Buffer ( ST_GeomFromText ( 'POINT(100 90)' ), 50, 2 )) AS lamecircle_pcount;
------------------------+-------------------
promisingcircle_pcount  | lamecircle_pcount
------------------------+-------------------
             33         |                9