Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL中使用空间位置需注意的问题

MySQL中使用空间位置需注意的问题

空间位置数据类型

MySQL支持的空间数据类型[1](#fn-641-1)
**GEOMETRY**
**POINT**
**LINESTRING**
**POLYGON**

最常用的为GEOMETRY,POLYGON,POINT。因为目前很多应用都是判断是否某个点在所画范围内,或者是两个多边形范围的交叉情况。

GEOMETRY

GEOMETRY是一种强大的空间数据类型,可以用来表示POINT, LINESTRING, and POLYGON,即另外的三种空间数据类型均可以使用GEOMETRY来表示(线上也推荐使用此种模式,避免出现兼容性问题

.
.

常见问题

空间索引

所有的空间字段不能为NULL,不然在创建空间索引的时候会报错:

1
2
3
mysql> create table spatest(id int auto_increment primary key, userspace geometry default null,  SPATIAL KEY `idx_userspace` (`userspace`));
ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL

关于使用polygon数据类型

polygon中的打点,必须为首尾相连,形成闭合图形(在应用中容易打点时忽略最后一个点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select st_astext(ST_geomfromtext('POLYGON(( 119.41667 24.078513,115.416761 26.078578,115.41725 24.078327,113.4177 24.078027,115.418154 25.077749, 119.41667 26.078513))')); 
ERROR 3037 (22023): Invalid GIS data provided to function st_geomfromtext.


mysql> select st_astext(ST_geomfromtext('POLYGON(( 119.41667 24.078513,115.416761 26.078578,115.41725 24.078327,113.4177 24.078027,115.418154 25.077749, 119.41667 24.078513))')); 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| st_astext(ST_geomfromtext('POLYGON(( 119.41667 24.078513,115.416761 26.078578,115.41725 24.078327,113.4177 24.078027,115.418154 25.077749, 119.41667 24.078513))')) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((119.41667 24.078513,115.416761 26.078578,115.41725 24.078327,113.4177 24.078027,115.418154 25.077749,119.41667 24.078513))                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



注意

MySQL8.0将astext函数转换为st_astext。

空间相关函数

常用的空间相关函数2
ST_Intersects(g1, g2):
Returns 1 or 0 to indicate whether g1 spatially intersects g2.

ST_Contains(g1, g2):
Returns 1 or 0 to indicate whether g1 completely contains g2. This tests the opposite relationship as ST_Within().

- - - - - - 1. [MySQL空间数据类型](https://dev.mysql.com/doc/refman/5.6/en/spatial-type-overview.html) [↩︎](#fnref-641-1) 2. [MySQL支持的空间函数](https://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html#function_st-intersects) [↩︎](#fnref-641-2)

comments powered by Disqus