另一组点的半径内的点数

I have two tables. One is a list of stores (with lat/long). The other is a list of customer addresses (with lat/long). What I want is a query that will return the number of customers within a certain radius for each store in my table. This gives me the total number of customers within 10,000 meters of ANY store, but I'm not sure how to loop it to return one row for each store with a count.

我有两张桌子。一个是商店列表(lat / long)。另一个是客户地址列表(使用lat / long)。我想要的是一个查询,它将返回表格中每个商店的特定半径范围内的客户数量。这给了我任意商店10000米范围内的客户总数,但我不知道如何循环它以为每个商店返回一行计数。

Note that I'm doing this queries using cartoDB, where the_geom is basically long/lat.

请注意,我正在使用cartoDB进行此查询,其中the_geom基本上是long / lat。

SELECT COUNT(*) as customer_count FROM customer_table 
WHERE EXISTS(
    SELECT 1 FROM store_table
    WHERE ST_Distance_Sphere(store_table.the_geom, customer_table.the_geom) < 10000
)

This results in a single row :

这导致单行:

customer_count
4009

Suggestions on how to make this work against my problem? I'm open to doing this other ways that might be more efficient (faster).

关于如何解决我的问题的建议?我愿意采取其他可能更有效(更快)的方式。

For reference, the column with store names, which would be in one column is store_identifier.store_table

作为参考,具有商店名称的列将在store_identifier.store_table中,该列将位于一列中

1 个解决方案

#1

I'll assume that you use the_geom to represent the coordinate (lat/lon) of store and customer. I will also assume that the_geom is of geography type. Your query will be something like this

我假设您使用the_geom来表示商店和客户的坐标(纬度/经度)。我还假设the_geom属于地理类型。您的查询将是这样的

select s.id, count(*) as customer_count
from customers c
inner join stores s 
  on st_dwithin(c.the_geom, s.the_geom, 10000)
group by s.id

This should give you neat table with a store id and count of customers within 10,000 meters from the store.

这应该给你整洁的桌子与商店ID和商店的10,000米范围内的客户数量。

If the_geom is of type geometry, you query will be very similar but you should use st_distance_sphere() instead in order to express distance in kilometers (not degrees).

如果the_geom是几何类型,则查询将非常相似,但您应该使用st_distance_sphere()来表示以千米(而不是度)为单位的距离。

#1