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.
Note that I'm doing this queries using cartoDB, where
the_geom is basically 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 :
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