如何按地理坐标过滤的名称查找重复项

Question

我需要按名称查找重复的条目（住宿），操作如下：

CREATE TABLE tbl_folded AS
SELECT name
     , array_agg(id) AS ids
FROM   accommodations
GROUP  BY 1;

获取所有同名住宿的id是可以的，不幸的是，它们需要进一步过滤。我只需要在一个地方找到同名的住处。每个住处都有一个地址（地址表有外键、accommodation_id和地理坐标的lonlat列）。为了找到最近的位置，我会选择像这样的

ORDER BY ST_Distance(addresses.lonlat, addresses.lonlat)

那么，我如何扩展上面的查询以应用此位置筛选呢？非常感谢您的帮助。

   Column    |          Type          |                          Modifiers                          
-------------+------------------------+-------------------------------------------------------------
 id          | integer                | not null default nextval('accommodations_id_seq'::regclass)
 name        | character varying(255) | 
 category    | character varying(255) |

                                        Table "public.addresses"
      Column      |            Type             |                       Modifiers                        
------------------+-----------------------------+--------------------------------------------------------
 id               | integer                     | not null default nextval('addresses_id_seq'::regclass)
 formatted        | character varying(255)      | 
 city             | character varying(255)      | 
 state            | character varying(255)      | 
 country_code     | character varying(255)      | 
 postal           | character varying(255)      | 
 lonlat           | geography(Point,4326)       | 
 accommodation_id | integer                     | 

Answer 1

您可以首先从addresses表中通过lonlat列（如）分组获得重复的accommodation_id

select accommodation_id
from addresses
group by lonlat
having count(*) > 1

然后将这个结果与accommodation表连接起来，得到如下所示的names列

CREATE TABLE tbl_folded AS
select ac.id,
ac.names
from accommodation ac 
join (
select accommodation_id
from addresses
group by lonlat
having count(*) > 1
) tab on ac.id = tab.accommodation_id

Answer 2

我就是这样解决的。我只过滤半径内的坐标

SELECT
  lower(name)                  AS base_name,
  array_agg(accommodations.id) AS ids
FROM accommodations
  INNER JOIN addresses ON accommodations.id = addresses.accommodation_id
GROUP BY 1, round(ST_X(lonlat::geometry)::numeric,2), round(ST_Y(lonlat::geometry)::numeric,2)