如何查找MySQL中含错误纬度-95.712891的二进制格式坐标？

方法一：直接在MySQL中筛选错误纬度值 #

你的location列是POINT类型，无需拉取所有数据到Python处理，直接用MySQL空间函数即可高效筛选：

• MySQL中POINT格式为ST_Point(经度, 纬度)，用ST_Y()提取纬度，ST_X()提取经度
• 精确匹配查询：

SELECT uuid, location
FROM algoData
WHERE ST_Y(location) = -95.712891;

• 若存在精度误差，用范围匹配：

SELECT uuid, location
FROM algoData
WHERE ST_Y(location) BETWEEN -95.712891 - 0.000001 AND -95.712891 + 0.000001;

方法二：在Python中正确解析MySQL POINT二进制数据 #

MySQL的POINT二进制遵循WKB标准，可通过shapely库解析：

1. 安装依赖：

pip install shapely

2. 解析代码：

from shapely.wkb import loads

# 示例二进制点数据
point_bytes = b'\x00\x00\x00\x00\x01\x01\x00\x00\x00\xacTPQ\xf5\x8b\t\xc0i\xac\xfd\x9d\xed\xf9K@'
point = loads(point_bytes)
longitude = point.x
latitude = point.y

print(f"经度: {longitude}, 纬度: {latitude}")

修复现有代码的问题 #

你当前将locationPoints转为字符串的操作会破坏二进制数据，修改后代码：

import pymysql
from shapely.wkb import loads

conn = pymysql.connect(
    host=endpoint,
    user=username,
    password=password,
    port=port,
    database=database
)

cursor = conn.cursor()

read_command = """
    SELECT uuid, location
    FROM algoData
"""

cursor.execute(read_command)
results = cursor.fetchall()

error_latitude = -95.712891
tolerance = 1e-6  # 处理精度误差

for uuid, point_bytes in results:
    if point_bytes is None:
        continue
    point = loads(point_bytes)
    lat = point.y
    if abs(lat - error_latitude) <= tolerance:
        print(f"找到错误纬度的UUID: {uuid}, 纬度: {lat}")

修复距离查询报错问题 #

错误纬度值（超出-90~90范围）会导致空间函数报错，可在查询中先过滤无效纬度：

SELECT uuid, ERASEnabled, useractivity
FROM algoData
WHERE gender = %s
AND dob BETWEEN %s AND %s
AND ST_Y(location) BETWEEN -90 AND 90  -- 过滤无效纬度
AND ST_Distance_Sphere(location, ST_PointFromText(%s)) / 1609.34 <= %s
LIMIT %s;

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