MySQL 8.0.30+ SRID4326下POINT与POLYGON空间查询无结果问题

问题描述 #

使用修复了空间索引问题的MySQL 8.0.30版本，将POINT类型的location列转换为SRID 4326，确保无NULL值并添加了空间索引。此前未指定SRID的查询能返回20+行（但未使用索引速度慢），现在指定SRID 4326的新查询无报错但返回0行，且已确认location列和POLYGON的SRID均为4326，排查操作错误点。

原查询（无SRID，返回20+行） #

SELECT r.id
FROM record r
WHERE ST_CONTAINS(ST_GeomFromText('POLYGON((-74.5 40.5, -73 40.5, -73 39.5, -74.5 39.6, -74.5 40.5))'), r.location);

新查询（指定SRID 4326，返回0行） #

SELECT r.id
FROM record r
WHERE ST_CONTAINS(ST_GeomFromText('POLYGON((-74.5 40.5, -73 40.5, -73 39.5, -74.5 39.6, 
-74.5 40.5))', 4326), r.location);

表结构及索引信息 #

mysql> describe record;
+-------------------+------------------+------+-----+-------------------+-----------------------------+
| Field             | Type             | Null | Key | Default           | Extra                       |
+-------------------+------------------+------+-----+-------------------+-----------------------------+
| id                | int unsigned     | NO   | PRI | NULL              | auto_increment                                    |
| location          | point            | NO   | MUL | NOT NULL          |                             
+-------------------+------------------+------+-----+-------------------+-----------------------------+

mysql> show indexes from record;
+--------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| record       |          1 | my_spatial                  |            1 | location    | A         |       14351 |       32 |   NULL |      | SPATIAL    |         |               | YES     | NULL       |
+--------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

SRID检查结果 #

mysql> select ST_SRID(location) from record limit 1;
+--------------------+
| ST_SRID(location) |
+--------------------+
|               4326 |
+--------------------+

mysql> select ST_SRID(ST_GeomFromText('POLYGON((-74.5 40.5, -73 40.5, -73 39.5, -74.5 39.6, -74.5 40.5))', 4326));
+-----------------------------------------------------------------------------------------------------+
| ST_SRID(ST_GeomFromText('POLYGON((-74.5 40.5, -73 40.5, -73 39.5, -74.5 39.6, -74.5 40.5))', 4326)) |
+-----------------------------------------------------------------------------------------------------+
|                                                                                                4326 |
+-----------------------------------------------------------------------------------------------------+

排查方向及解决方法 #

1. 坐标顺序不匹配（最可能原因） #

SRID 4326属于WGS84地理坐标系，标准坐标格式为**(经度, 纬度)**，若location列误存为(纬度, 经度)，会导致点与多边形的坐标逻辑完全错位。

• 验证方式：取出原查询返回的某条记录，查看坐标顺序：

  SELECT ST_X(location), ST_Y(location) FROM record WHERE id = [原查询返回的某条ID];
  

  若返回结果为(40.5, -74.5)这类（纬度在前），与多边形的(-74.5 40.5)（经度在前）顺序相反，即为问题根源。
• 解决方法：更新location列的坐标顺序，匹配4326标准：

  UPDATE record SET location = ST_Point(ST_Y(location), ST_X(location));
  

2. 多边形有效性问题 #

当前定义的多边形顶点顺序可能导致几何图形无效（如自相交、方向不符合地理坐标系规则），MySQL在严格SRID模式下会忽略无效多边形内的点。

• 验证方式：检查多边形是否有效：

  SELECT ST_IsValid(ST_GeomFromText('POLYGON((-74.5 40.5, -73 40.5, -73 39.5, -74.5 39.6, -74.5 40.5))', 4326));
  

  返回1为有效，0为无效。
• 解决方法：修正多边形顶点为逆时针方向且无自相交，例如调整为规则矩形：

  POLYGON((-74.5 40.5, -73 40.5, -73 39.5, -74.5 39.5, -74.5 40.5))
  

3. SRID转换过程错误 #

若转换location列时仅直接设置SRID（如用ST_SetSRID），未对原坐标进行坐标系转换（比如原坐标是平面坐标系），会导致坐标被错误解释为经纬度，位置完全偏离。

• 验证方式：对比原无SRID查询时的点坐标，与现在的实际地理位置是否匹配。
• 解决方法：若原坐标属于其他坐标系，使用ST_Transform完成转换：

  UPDATE record SET location = ST_Transform(ST_SetSRID(location, [原坐标系SRID]), 4326);
  

4. 空间索引重建（可能性较低） #

若索引建立时基于错误的坐标或SRID，可尝试删除后重新建立：

DROP INDEX my_spatial ON record;
CREATE SPATIAL INDEX my_spatial ON record(location);

内容的提问来源于stack exchange，提问作者Bruno Leveque