Doris--基础--13.3--SQl函数--地理位置函数

Doris–基础–13.3–SQl函数–地理位置函数

---

1、ST_AsText(GEOMETRY geo)

将一个几何图形转化为WKT(Well Known Text)的表示形式

MySQL [test_db]> SELECT ST_AsText(ST_Point(24.7, 56.7));
+---------------------------------+
| st_astext(st_point(24.7, 56.7)) |
+---------------------------------+
| POINT (24.7 56.7)               |
+---------------------------------+
1 row in set (0.00 sec)

2、ST_Circle(DOUBLE center_lng, DOUBLE center_lat, DOUBLE radius)

将一个WKT(Well Known Text)转化为地球球面上的一个圆。

1. center_lng：圆心的经度
2. center_lat：圆心的纬度
3. radius：圆的半径，单位是米，最大支持99999994

MySQL [test_db]> SELECT ST_AsText(ST_Circle(111, 64, 10000));
+--------------------------------------------+
| st_astext(st_circle(111.0, 64.0, 10000.0)) |
+--------------------------------------------+
| CIRCLE ((111 64), 10000)                   |
+--------------------------------------------+
1 row in set (0.01 sec)

3、ST_Contains(GEOMETRY shape1, GEOMETRY shape2)

1. 判断几何图形shape1是否完全能够包含几何图形shape2
2. 几何图形是二维的。

# 这里面的坐标点 (0,0) 表示原点，(10,0) 表示X轴为10，(10,10) 表示X轴为10，Y轴为10。总体就是一个边长为10的正方形。
MySQL [test_db]> SELECT ST_Contains(ST_Polygon("POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))"), ST_Point(5, 5));
+----------------------------------------------------------------------------------------+
| st_contains(st_polygon('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))'), st_point(5.0, 5.0)) |
+----------------------------------------------------------------------------------------+
|                                                                                      1 |
+----------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

MySQL [test_db]>  
MySQL [test_db]> SELECT ST_Contains(ST_Polygon("POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))"), ST_Point(50, 50));
+------------------------------------------------------------------------------------------+
| st_contains(st_polygon('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))'), st_point(50.0, 50.0)) |
+------------------------------------------------------------------------------------------+
|                                                                                        0 |
+------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

4、ST_Distance_Sphere(DOUBLE x_lng, DOUBLE x_lat, DOUBLE y_lng, DOUBLE y_lat)

1. 计算地球两点之间的球面距离，单位为 米。
2. x_lng：X点的经度
3. x_lat：X点的纬度
4. y_lng：Y点的经度
5. y_lat：Y点的纬度。

MySQL [test_db]> select st_distance_sphere(116.35620117, 39.939093, 116.4274406433, 39.9020987219);
+----------------------------------------------------------------------------+
| st_distance_sphere(116.35620117, 39.939093, 116.4274406433, 39.9020987219) |
+----------------------------------------------------------------------------+
|                                                         7336.9135549995917 |
+----------------------------------------------------------------------------+
1 row in set (0.01 sec)

5、ST_GeometryFromText(VARCHAR wkt)

将一个WKT(Well Known Text)转化为对应的内存的几何形式

MySQL [test_db]> SELECT ST_AsText(ST_GeometryFromText("LINESTRING (1 1, 2 2)"));
+---------------------------------------------------------+
| st_astext(st_geometryfromtext('LINESTRING (1 1, 2 2)')) |
+---------------------------------------------------------+
| LINESTRING (1 1, 2 2)                                   |
+---------------------------------------------------------+
1 row in set (0.01 sec)

6、ST_LineFromText(VARCHAR wkt)

将一个WKT(Well Known Text)转化为一个Line形式的内存表现形式

MySQL [test_db]> SELECT ST_AsText(ST_LineFromText("LINESTRING (1 1, 2 2)"));
+-----------------------------------------------------+
| st_astext(st_linefromtext('LINESTRING (1 1, 2 2)')) |
+-----------------------------------------------------+
| LINESTRING (1 1, 2 2)                               |
+-----------------------------------------------------+
1 row in set (0.01 sec)

7、ST_Point(DOUBLE x, DOUBLE y)

1. 通过给定的X坐标值，Y坐标值返回对应的Point。 当前这个值只是在球面集合上有意义，X/Y对应的是经度/纬度(longitude/latitude);
2. 注意：直接select ST_Point()会卡住，慎重！！！

MySQL [test_db]> SELECT ST_AsText(ST_Point(24.7, 56.7));
+---------------------------------+
| st_astext(st_point(24.7, 56.7)) |
+---------------------------------+
| POINT (24.7 56.7)               |
+---------------------------------+
1 row in set (0.01 sec)

8、ST_Polygon(VARCHAR wkt)

将一个WKT(Well Known Text)转化为对应的多边形内存形式

MySQL [test_db]> SELECT ST_AsText(ST_Polygon("POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))"));
+------------------------------------------------------------------+
| st_astext(st_polygon('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))')) |
+------------------------------------------------------------------+
| POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))                          |
+------------------------------------------------------------------+
1 row in set (0.01 sec)

9、ST_X(POINT point)

当point是一个合法的POINT类型时，返回对应的X坐标值

MySQL [test_db]> SELECT ST_X(ST_Point(24.7, 56.7));
+----------------------------+
| st_x(st_point(24.7, 56.7)) |
+----------------------------+
|                       24.7 |
+----------------------------+
1 row in set (0.00 sec)

10、ST_Y(POINT point)

当point是一个合法的POINT类型时，返回对应的Y坐标值

MySQL [test_db]> SELECT ST_Y(ST_Point(24.7, 56.7));
+----------------------------+
| st_y(st_point(24.7, 56.7)) |
+----------------------------+
|                       56.7 |
+----------------------------+
1 row in set (0.01 sec)