计算距另一组点在一定距离范围内的点数 [英] Count number of points within certain distance ranges from another set of points

问题描述

我有以下内容，这使我可以了解任何商店位置10,000米以内的客户数量:

I have the following, which gives me the number of customers within 10,000 meters of any store location:

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

我需要的是此查询不仅返回10,000米以内的客户数量，而且还返回以下内容.内的客户数量...

What I need is for this query to return not only the number of customers within 10,000 meters, but also the following. The number of customers within...

1. 10,000米
2. 大于10,000，但小于50,000
3. 大于50,000，但小于10,0000
4. 超过100,000个

...在任何位置.

我愿意以多种方式进行这项工作.对于给定的客户，只计算一次(到任何商店的最短距离)，这将对每个人准确地计数一次.我意识到这可能很复杂.我也愿意对人进行多次计数，无论如何，这确实是准确的值，并且认为应该更简单.

I'm open to this working a couple of ways. For a given customer, only count them one time (the shortest distance to any store), which would count everyone exactly once. I realize this is probably pretty complex. I'm also open to having people be counted multiple times, which is really the accurate values anyway and think should be much simpler.

感谢任何方向.

推荐答案

您可以相对轻松地进行两种类型的查询.但是这里的问题是，您不知道哪些客户与哪些商店位置相关联，这似乎是一件很有趣的事情.如果需要，请在查询中使用locations_table的PK和store_name.请在下面查看具有位置ID和store_name的两个选项.要强调两个选项之间的区别:

You can do both types of queries relatively easily. But an issue here is that you do not know which customers are associated with which store locations, which seems like an interesting thing to know. If you want that, use the PK and store_name of the locations_table in the query. See both options with location id and store_name below. To emphasize the difference between the two options:

• 第一个选项表示每个距离级别的每个商店位置有多少客户，每个商店位置的所有客户.
• 第二个选项表示每个距离类别中每个商店位置的顾客数量，仅每个顾客最近的商店位置.

• The first option indicates how many customers are in every distance class for every store location, for all customers for every store location.
• The second option indicates how many customers are in every distance class for every store location, for the nearest store location for each customer only.

这是对O(n x m)运行顺序的查询(由CROSS JOIN在customer_table和locations_table之间实现)，并且随着任一表中行数的增加而变慢.

This is a query of O(n x m) running order (implemented with the CROSS JOIN between customer_table and locations_table) and likely to become rather slow with increasing numbers of rows in either table.

您应在顾客距商店位置的距离之间进行CROSS JOIN，然后按商店位置ID，名称和您定义的最大距离的类别对他们进行分组.您可以使用VALUES命令从距离类中创建一个表格"，然后可以将其简单地用于任何查询中:

You should make a CROSS JOIN between the distances of customers from store locations and then group them by the store location id, name and classes of maximum distance that you define. You can create a "table" from your distance classes with the VALUES command which you can then simply use in any query:

SELECT loc_dist.id, loc_dist.store_name, grps.grp, count(*)
FROM (
    SELECT s.id, s.store_name, ST_Distance_Sphere(s.the_geom, c.the_geom) AS dist
    FROM customer_table c, locations_table s) AS loc_dist
JOIN (
    VALUES(1, 10000.), (2, 50000.), (3, 100000.), (4, 1000000.)
  ) AS grps(grp, dist) ON loc_dist.dist < grps.dist
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

计算距离最近的舱位的客户

如果只想让客户列在最近的距离类别中，则应该在前一情况下在customer_table和locations_table上使用相同的CROSS JOIN，但是只需选择最低的组(即最近的商店) )在查询中使用CASE子句，并像以前一样GROUP BY存储位置ID，名称和距离类别:

Count customers in the nearest distance class

If you want customers listed in the nearest distance class only, then you should make the same CROSS JOIN on customer_table and locations_table as in the previous case, but then simply select the lowest group (i.e. the closest store) using a CASE clause in the query and GROUP BY store location id, name and distance class as before:

SELECT 
  id, store_name,
  CASE
    WHEN dist <  10000. THEN 1
    WHEN dist <  50000. THEN 2
    WHEN dist < 100000. THEN 3
    ELSE 4
  END AS grp,
  count(*)
FROM (
    SELECT s.id, s.store_name, ST_Distance_Sphere(s.the_geom, c.the_geom) AS dist
    FROM customer_table c, locations_table s) AS loc_dist
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

这篇关于计算距另一组点在一定距离范围内的点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！