sql - 连接两个表

Question

我下面的表格是postgres(postgis)。这两个表都有第 1、2、3 列字符类型 varying(9000) 和第 4 列“几何”类型(包含(纬度、经度)的点几何):

Table1
Column1      Column2             Column3    the_geom
Oklahoma     numericalValue1     719        NULL
Oklahoma     geometry            NULL       (34.6,95.3)
Oklahoma     liesIn              America    NULL

Table2
Column1        Column2            Column3     the_geom
Mississippi    liesIn             America     NULL
Mississippi    geometry           NULL        (32.7,-89.53)
Mississippi    numericalValue2    15.3        NULL

我想运行以下查询，对表 1 和表 2 执行空间连接。问题中显示的 Table1 和 Table2 是玩具表，在我的实际数据集中，它们各包含一百万行。当我运行下面给出的查询时，我发现我的查询运行时间特别长(超过 10 小时)。有人可以建议我是否可以通过重新制定查询来优化查询。

select * from Table1 s1, Table1 s2, Table1 s3, Table2 s4, Table2 s5, Table2 s6 where
s1.column2='numericalValue1' and     
s2.column2='geometry' and 
s3.column2='liesIn' and 
s1.column1=s2.column1 and 
s2.column1=s3.column1 and 
s4.column2='liesIn' and 
s5.column2='geometry' and 
s6.column2='numericalValue2' and 
s4.column1=s5.column1 and 
s5.column1=s6.column1 and 
ST_DWithin(s2.the_geom, s5.the_geom, 5) 
order by (cast(s1.column3 as double precision)+cast(s6.column3 as double precision)) 
limit 1;

Answer 1

1. 交换轴顺序，使您的几何图形为 (X Y) 或 (long lat)
2. 如果您没有 GiST 空间索引，请研究如何添加一个
3. 我假设您使用的是 SRID=4326 或 EPSG:4326 ，它有度数单位。所以 50000000 的距离是不合逻辑的，而且是巨大的，并且会进行交叉连接并且需要很长时间才能完成。要么尝试更小的距离，例如 0.01 度，要么调查使用 geography 类型以使用线性距离或其他技巧。