MYSQL 兼容

空间关系

MBRContains

语法

boolean MBRContains(geom1 geometry,geom2 geometry)

geom1 的最小边界矩形是否包含 geom2 的最小边界矩形,如果包含则返回 true，否则返回 false

示例：

SELECT MBRContains('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

mbrcontains
-------------
t
(1 row)

MBRCoveredBy

语法

boolean MBRCoveredBy(geom1 geometry,geom2 geometry)

geom1 的最小边界矩形是否被 geom2 的最小边界矩形所包含,如果包含则返回 true，否则返回 false

示例：

SELECT MBRCoveredBy('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

mbrcoveredby
--------------
f
(1 row)

MBRCovers

语法

boolean MBRCovers(geom1 geometry,geom2 geometry)

geom1 的最小边界矩形是否覆盖 geom2 的最小边界矩形.

示例:

SELECT MBRCovers('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

 mbrcovers
-----------
t
(1 row)

MBRDisjoint

语法

boolean MBRDisjoint(geom1 geometry,geom2 geometry)

两个几何图形 geom1 和 geom2 的最小边界矩形是否相交,相交返回 true,否则返回 false

示例:

SELECT MBRDisjoint('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

 mbrdisjoint
-------------
f
(1 row)

MBREquals

语法

boolean MBREquals(geom1 geometry,geom2 geometry)

表示两个几何图形 geom1 和 geom2 的最小边界矩形是否相同,相同则返回 true ,否则返回 false

示例:

SELECT MBREquals('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

 mbrequals
----------
f
(1 row)

MBRIntersects

语法

boolean MBRIntersects(geom1 geometry,geom2 geometry)

表示两个几何图形 geom1 和 geom2 的最小边界矩形是否相交,相交则返回 true,否则返回 false

示例:

SELECT MBRIntersects('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

mbrintersects
--------------
t
(1 row)

MBROverlaps

语法

boolean MBROverlaps(geom1 geometry,geom2 geometry)

表示两个几何图形 geom1 和 geom2 的最小边界矩形是否重叠,重叠则返回 true,否则返回 false

示例:

SELECT MBROverlaps('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

mbrintersects
--------------
f
(1 row)

MBRTouches

语法

boolean MBRTouches(geom1 geometry,geom2 geometry)

表示两个几何图形 geom1 和 geom2 的最小边界矩形是否相接,相接则返回 true,否则返回 false

示例:

SELECT MBRTouches('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

mbrtouches
--------------
f
(1 row)

MBRWithin

语法

boolean MBRWithin(geom1 geometry,geom2 geometry)

表示两个几何图形 geom1 和 geom2 的最小边界矩形是否在 geom2 的最小边界矩形内,在内则返回 true,否则返回 false

示例:

SELECT MBRWithin('LINESTRING(0 0, 10 10)'::geometry, 'POINT(1 1)'::geometry);

mbrwithin
-----------
f
(1 row)

Geometry 构造

GeomCollection

语法
geometry GeomCollection( VARIADIC geom geometry)
geometry GeometryCollection( VARIADIC geom geometry)

用于构造一个几何集合

示例:

   SELECT ST_AsWKT(GeomCollection('POINT(0 0)', 'POINT(1 1)', 'POINT(2 2)'));
           st_aswkt
-------------------------
MULTIPOINT(0 0,1 1,2 2)
(1 row)

LineString

语法

geometry LineString( VARIADIC geom geometry)

使用多个对象构造一个 LINESTRING 对象

示例:

SELECT ST_AsText(LineString(ST_MakePoint(0, 0), ST_MakePoint(1, 1), ST_MakePoint(2, 2), ST_MakePoint(3, 3), ST_MakePoint(4, 4)));
            st_astext
---------------------------------
LINESTRING(0 0,1 1,2 2,3 3,4 4)
(1 row)

MultiLineString

语法

geometry MultiLineString( VARIADIC geom geometry)

使用多个 geom 构造一个 MULTILINE 对象

示例:

SELECT ST_AsText(MultiLineString(ST_MakeLine(ST_MakePoint(0, 0), ST_MakePoint(1, 1)),ST_MakeLine(ST_MakePoint(2, 2), ST_MakePoint(3, 3))));

              st_astext

--------------------------------------

MULTILINESTRING((0 0,1 1),(2 2,3 3))

(1 row)

MultiPoint

语法

geometry MultiPoint( VARIADIC geom geometry)

使用多个 geom 构造一个 MULTIPOINT 对象

示例:

SELECT ST_AsText(MultiPoint(ST_MakePoint(0, 0), ST_MakePoint(1, 1), ST_MakePoint(2, 2), ST_MakePoint(3, 3), ST_MakePoint(4, 4)));
            st_astext
---------------------------------
MULTIPOINT(0 0,1 1,2 2,3 3,4 4)
(1 row)

MultiPolygon

语法

geometry MultiPolygon( VARIADIC geom geometry)

使用多个 geom 来构造一个 MULTIPOLYGON 对象

示例：

SELECT ST_AsText(MultiPolygon(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'), ST_GeomFromText('POLYGON((2 2, 3 2, 3 3, 2 3, 2 2))')));
                          st_astext
---------------------------------------------------------------
MULTIPOLYGON(((0 0,1 0,1 1,0 1,0 0)),((2 2,3 2,3 3,2 3,2 2)))
(1 row)

Polygon

语法

geometry Polygon( VARIADIC geom geometry)

使用多个 geom 来构造一个 POLYGON 对象

示例：

SELECT ST_AsText(Polygon(ST_GeomFromText('LineString(0 0, 1 0, 1 1, 0 1, 0 0)'), ST_GeomFromText('LineString(2 2, 3 2, 3 3, 2 3, 2 2)')));
                     st_astext
------------------------------------------------------
POLYGON((0 0,1 0,1 1,0 1,0 0),(2 2,3 2,3 3,2 3,2 2))
(1 row)

ST_GeometryCollectionFromText

语法

geometry ST_GeometryCollectionFromText(geom_text text, srid int4 DEFAULT 0)

使用字符串来构造一个对象集合

示例：

SELECT ST_AsText(ST_GeometryCollectionFromText('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0, 1 1, 2 2),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'));
                                      st_astext
---------------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,1 1,2 2),POLYGON((0 0,1 0,1 1,0 1,0 0)))
(1 row)

语法

geometry ST_GeomCollFromTxt(geom_text text, srid int4 DEFAULT 0)

使用字符串来构造一个对象集合

示例：

SELECT ST_AsText(ST_GeomCollFromTxt('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0, 1 1, 2 2),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'));
                                      st_astext
---------------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,1 1,2 2),POLYGON((0 0,1 0,1 1,0 1,0 0)))
(1 row)

ST_GeometryCollectionFromWKB

语法

geometry ST_GeometryCollectionFromWKB(bytea, srid int4 DEFAULT 0)

使用 wkb 来构造一个对象集合

SELECT ST_AsText(ST_GeometryCollectionFromWKB(ST_AsWKB(ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0, 1 1, 2 2),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'))));
                                      st_astext
---------------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,1 1,2 2),POLYGON((0 0,1 0,1 1,0 1,0 0)))
(1 row)

ST_MultipolygonFromWKB

语法

geometry ST_MultipolygonFromWKB(bytea, srid int DEFAULT 0)

使用 wkb 来构造一个 MULTIPOLYGON 对象

示例：

SELECT ST_AsText(ST_MultipolygonFromWKB(ST_AsWKB('MULTIPOLYGON(((0.51 -0.25,1 -0.179078947368,1 0.270149253731,0.6 0.3,0.7 0.7,1 0.7,1 0.6,0.8 0.5,1 0.46,1 1.2,0.5 1.2,0.5 -0.1,0.3 -0.1,0.3 1.3,0 1.26875,0 -0.25,0.51 -0.25)))')));
                                                                                   st_astext
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MULTIPOLYGON(((0.51 -0.25,1 -0.179078947368,1 0.270149253731,0.6 0.3,0.7 0.7,1 0.7,1 0.6,0.8 0.5,1 0.46,1 1.2,0.5 1.2,0.5 -0.1,0.3 -0.1,0.3 1.3,0 1.26875,0 -0.25,0.51 -0.25)))
(1 row)

ST_MultiLineStringFromWKB

语法

geometry ST_MultiLineStringFromWKB(bytea, srid int DEFAULT 0)

使用 wkb 来构造一个 MULTILINE 对象

示例：

SELECT ST_AsText(ST_MultiLineStringFromWKB(ST_AsWKB('MULTILINESTRING((0 0,1 1,2 2),(3 3,4 4,5 5))')));
                 st_astext
----------------------------------------------
MULTILINESTRING((0 0,1 1,2 2),(3 3,4 4,5 5))
(1 row)

ST_LineStringFromText

语法

geometry ST_LineStringFromText(geom_text text, srid int4 DEFAULT 0)

使用字符串构造一个 LINE 对象

示例：

 SELECT ST_ASTEXT(ST_LINESTRINGFROMTEXT(' LINESTRING(3 8,9 2,3 8,3 3,7 6,4 7,4 7,8 1) '));
                 st_astext
---------------------------------------------
LINESTRING(3 8,9 2,3 8,3 3,7 6,4 7,4 7,8 1)
(1 row)

ST_GeometryFromWKB

语法

geometry ST_GeometryFromWKB(bytea)

使用 wkb 构造一个 geometry 对象

示例：

SELECT ST_AsText(ST_GeometryFromWKB(ST_AsWKB(ST_GeomFromText('POINT(0 1)'))));
st_astext
------------
POINT(0 1)
(1 row)

Geometry 操作

ST_X

语法

geometry ST_X(point geometry, new_x float)

设置点的横坐标值

示例：

SELECT ST_ASTEXT(ST_X(ST_GEOMFROMTEXT('POINT(0 0)'), 0.5));
 st_astext
--------------
POINT(0.5 0)
(1 row)

ST_Y

语法

geometry ST_Y(point geometry, new_y float)

设置点的纵坐标值

示例：

   SELECT ST_ASTEXT(ST_Y(ST_GEOMFROMTEXT('POINT(0 0)'), 0.5));
 st_astext
--------------
POINT(0 0.5)
(1 row)

ST_POINTATDISTANCE

语法

geometry ST_POINTATDISTANCE(line geometry, distance float8)

计算一条线上距离起始点 distance 距离的点

示例：

SELECT ST_ASTEXT(ST_POINTATDISTANCE(ST_GEOMFROMTEXT('LINESTRING(0 0,0 1)'), 0.5));
 st_astext
--------------
POINT(0 0.5)
(1 row)

ST_Longitude

语法

float ST_Longitude(point geometry)

计算一个点的经度值

示例：

SELECT ST_Longitude('Point(0 1)');
 st_longitude
--------------
            0
(1 row)

ST_Latitude

float ST_Latitude(point geometry)

计算一个点的纬度值

示例：

SELECT ST_Latitude('Point(0 1)');
 st_latitude
-------------
           1
(1 row)

ST_Longitude

geometry ST_Longitude(point geometry, new_x float)

设置一个点的经度值

示例：

SELECT ST_AsText(ST_Longitude('Point(0 1)'::geometry, 2));
 st_astext
------------
 POINT(2 1)
(1 row)

ST_Latitude

geometry ST_Latitude(point geometry, new_y float)

设置一个点的纬度值

示例：

SELECT ST_AsText(ST_Latitude('Point(0 1)'::geometry, 2));
 st_astext
------------
 POINT(0 2)
(1 row)

ST_GeoHash

语法

TEXT ST_GeoHash(x float8, y float8, maxchars int4 DEFAULT 0)

计算一个点的 geohash 字符串

示例：

SELECT ST_GEOHASH(100,45,5);
st_geohash
------------
y0p05
(1 row)

ST_LongFromGeoHash

语法

float8 ST_LongFromGeoHash(geohash_str text)

根据 geohash 字符串计算对应的经度值

示例：

SELECT ST_LONGFROMGEOHASH('0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z0z');
st_longfromgeohash
--------------------
 -145.161290322581
(1 row)

ST_LatFromGeoHash

语法

float8 ST_LatFromGeoHash(geohash_str text)

根据 geohash 字符串计算对应的纬度值

示例：

SELECT ST_LATFROMGEOHASH('zzzzzzzzzz0000000000');
st_latfromgeohash
-------------------
 89.9999946355821
(1 row)

ST_Validate

语法

geometry ST_Validate(geom geometry)

验证 geom 是否有效

示例：

SELECT ST_ASTEXT(ST_VALIDATE(ST_GEOMFROMTEXT('POINT(0 0)')));
 st_astext
------------
 POINT(0 0)
(1 row)

ST_SwapXY

geometry ST_SwapXY(geom geometry)

交换 geom 对象的 x,y 坐标

示例：

SELECT ST_ASTEXT(ST_SWAPXY(ST_GEOMFROMTEXT('POINT(0 1)')));

 st_astext

------------

  POINT(1 0)

  (1 row)

ST_Distance_Sphere

float8 ST_Distance_Sphere(geom1 geometry, geom2 geometry)

计算 geom1 和 geom2 之间的球面距离

示例：

SELECT ST_Distance_Sphere(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POINT(1 1)'));
 st_distance_sphere
--------------------
    157249.59776851
(1 row)

ST_MakeEnvelope

语法

geometry ST_MakeEnvelope(point1 geometry, point2 geometry)

由 point1 和 point2 图形构造一个矩形框

示例：

SELECT ST_ASTEXT(ST_MAKEENVELOPE(ST_GEOMFROMTEXT('POINT(0.0 -0.0)'), ST_GEOMFROMTEXT('POINT(0 0)')));
st_astext
------------
POINT(0 0)
(1 row)

ST_AsWKB

语法

bytea ST_AsWKB(geom geometry)

输出 geom 的 WKB 字符

示例：

SELECT ST_AsWKB(ST_GeomFromText('POINT(0 0)'));
                  st_aswkb
----------------------------------------------
\x010100000000000000000000000000000000000000
(1 row)

ST_AsWKT

语法

text ST_AsWKT(geom geometry)

输出 geom 的 WKT 形式字符串

示例：

SELECT ST_AsWKT(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 2)'));
       st_aswkt
-------------------------
LINESTRING(0 0,1 1,2 2)
(1 row)

ST_SRID

语法

geometry ST_SRID(geom geometry, srid int4)

设置 geom 对象的 SRID

示例：

SELECT ST_AsEWKT(ST_SRID(ST_MakePoint(0, 0), 4326));
     st_asewkt
----------------------
SRID=4326;POINT(0 0)
(1 row)