postgresql - ST_contains 花费太多时间

Question

我正在尝试使用以下查询将纬度/经度与特定的相邻位置相匹配

create table address_classification as (
select distinct buildingid,street,city,state,neighborhood,borough
from master_data
join 
Borough_GEOM
on st_contains(st_astext(geom),coordinates) = 'true'
);

在此，坐标格式如下

ST_GeometryFromText('POINT('||longitude||' '||latitude||')') as coordinates

而geom是column类型的geometry。
我已经创建了如下索引

CREATE INDEX coordinates_gix ON master_data USING GIST (coordinates);
CREATE INDEX boro_geom_indx ON Borough_GEOM USING gist(geom);

我在主表中有近300万条记录，在GEOM表中有200条几何信息。解释查询分析花费了如此多的时间(2 小时)。请让我知道，我该如何优化这个查询。

提前致谢。

Answer 1

• 如评论中所述，不要使用 ST_AsText():那不属于那里。它将 geom 转换为文本，然后返回到 geom。但是，更重要的是，该过程可能会弄乱索引。
• 如果您仅在列上是唯一的，则使用 DISTINCT ON，无需比较其他列。
• 如果您在 ID 列上是唯一的，并且您唯一的加入是为了增加选择性，那么请考虑使用 EXISTS。这些列是否来自 borough_GEOM 而不是 geom？

我会从这样的事情开始，

CREATE TABLE address_classification AS 
  SELECT DISTINCT ON (buildingid),
    buildingid,
    street,
    city,
    state,
    neighborhood,
    borough
  FROM master_data
  JOIN borough_GEOM
    ON ST_Contains(geom,coordinates);