postgresql - Count number of points within certain distance ranges from another set of points -

i have following, gives me number of customers within 10,000 meters of store location:

select count(*) customer_count customer_table c exists( select 1 locations_table s st_distance_sphere(s.the_geom, c.the_geom) < 10000 ) 

what need query return not number of customers within 10,000 meters, following. number of customers within...

1. 10,000 meters
2. more 10,000, less 50,000
3. more 50,000, less 10,0000
4. more 100,000

...of location.

i'm open working couple of ways. given customer, count them 1 time (the shortest distance store), count once. realize pretty complex. i'm open having people counted multiple times, accurate values anyway , think should simpler.

thanks direction.

you can both types of queries relatively easily. issue here not know customers associated store locations, seems interesting thing know. if want that, use pk , store_name of locations_table in query. see both options location id , store_name below. emphasize difference between 2 options:

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

this query of o(n x m) running order (implemented cross join between customer_table , locations_table) , become rather slow increasing numbers of rows in either table.

count customers in distance classes

you should make cross join between distances of customers store locations , group them store location id, name , classes of maximum distance define. can create "table" distance classes values command can use in query:

select loc_dist.id, loc_dist.store_name, grps.grp, count(*) (     select s.id, s.store_name, st_distance_sphere(s.the_geom, c.the_geom) dist     customer_table c, locations_table s) loc_dist join (     values(1, 10000.), (2, 50000.), (3, 100000.), (4, 1000000.)   ) grps(grp, dist) on loc_dist.dist < grps.dist group 1, 2, 3 order 1, 2, 3; 

count customers in nearest distance class

if want customers listed in nearest distance class only, should make same cross join on customer_table , locations_table in previous case, select lowest group (i.e. closest store) using case clause in query , group by store location id, name , distance class before:

select    id, store_name,   case     when dist <  10000. 1     when dist <  50000. 2     when dist < 100000. 3     else 4   end grp,   count(*) (     select s.id, s.store_name, st_distance_sphere(s.the_geom, c.the_geom) dist     customer_table c, locations_table s) loc_dist group 1, 2, 3 order 1, 2, 3;