跨MySQL数据库两表关联：经纬度匹配多边形ST_WITHIN语法报错如何解决

报错原因排查 #

你的SQL语法错误来自以下几个常见写法问题：

• SELECT子句末尾多余逗号：st_astext(geometry), 后面不需要加逗号，直接接FROM即可
• Point函数用法错误：
  1. MySQL/MariaDB不支持用方括号[]包裹Point参数，直接传入经纬度即可
  2. 构造带SRID的点对象不能直接把SRID写在Point参数里，需要用ST_SRID函数指定，或者使用ST_PointFromText构造
  3. WGS84(4326)坐标系下空间函数的点参数要求经度在前，纬度在后，你写反了经纬度顺序会导致匹配结果完全错误
• 连接逻辑不符合需求：你现在用的内连接JOIN会过滤掉没有匹配到多边形的点位，要保留所有table_1的记录需要用LEFT JOIN，且左表为database_1.table_1
• 标识符引用错误：MySQL不支持用方括号[]包裹库表/字段名，只能用反引号`做标识符引用，你报错信息里的[database_1].[Table_1]就是错误写法导致的

修正后的代码 #

调整SQL语句，同时补充table_1全字段的查询逻辑：

cnx = mysql.connector.connect(user='root', password='', unix_socket='/data/mysql/mysql.sock', database='database_2')

test=pd.read_sql("""
SELECT 
    Table1.*,
    table_2.tier_name,
    table_2.tier2_name
FROM `database_1`.`table_1` as Table1
LEFT JOIN `table_2`
ON ST_WITHIN(
    ST_SRID(Point(Table1.Longitude, Table1.Latitude), 4326),
    table_2.geometry
)
""", cnx)

可选优化建议 #

如果表数据量较大，查询效率低可以做如下优化：

• 给table_2的geometry字段加空间索引：CREATE SPATIAL INDEX idx_geo ON table_2(geometry)
• 提前给table_1生成带SRID的点字段并加索引，避免每次查询实时构造点对象

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