MySQL中查询与指定linestring相交的所有linestring的最优方法

你现有方案完全谈不上最优，甚至属于不推荐的低效写法，问题主要有三点：

1. ST_Intersection会计算两个几何的实际相交部分，运算开销远高于仅做相交判定的函数
2. 转WKT对比空集合的操作属于完全冗余的额外开销
3. 该写法无法命中空间索引，数据量稍大时查询速度会慢到无法使用

最优实现方案 #

直接用PostGIS内置的ST_Intersects函数做判定即可，这个函数专门用于几何相交判断，内部做了大量性能优化，且支持空间索引命中，性能比你现有写法高几个数量级。

分两种常见场景给对应写法：

• 场景1：匹配和某一条指定linestring相交的所有linestring
  示例（假设你指定的是rivers表中gid为123的河流）：

  SELECT * FROM trains t
  WHERE ST_Intersects(t.SHAPE, (SELECT SHAPE FROM rivers WHERE gid = 123));
  

• 场景2：匹配所有两两相交的火车线路和河流配对

  SELECT * FROM trains t
  INNER JOIN rivers r ON ST_Intersects(t.SHAPE, r.SHAPE);
  

额外优化建议 #

• 提前给trains和rivers表的SHAPE字段建立GIST空间索引，大表下查询性能会提升几十上百倍，建索引语句：

  -- 给火车线路表建空间索引
  CREATE INDEX idx_trains_shape ON trains USING GIST(SHAPE);
  -- 给河流表建空间索引
  CREATE INDEX idx_rivers_shape ON rivers USING GIST(SHAPE);
  

• 两个表的几何字段需要使用相同的SRID坐标系，如果不一致需要先用ST_Transform转换后再做判断，避免结果错误。

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