I have the following, which gives me the number of customers within 10000 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 )
What I need is for this query to return not only the number of customers within 10000 meters, but also the following. The number of customers within...
- 10,000 meters
- more than 10,000, but less than 50,000
- more than 50,000, but less than 10,0000
- more than 100,000
...of any location.
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.