[GIS] Find number of points within certain radius of a set of points

Question

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 :

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

Answer 1

There is simple query for this case.

SELECT a.id AS store,count(b.*) AS customer_count FROM stores a, customers b 
    WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000) GROUP BY a.id

I have stored my sample data in the projection WGS 84 (4326). When you want to use a metric system, you have to convert the geometries to geography format.

The result of my query:

store   customer_count
1       2
2       4
3       2

EDIT To get the percentage of all customers and customers within a certain area you can use a WITH query (not tested!):

WITH all_customers AS (
    SELECT count(*) AS all_customers 
    FROM customers
    )
        SELECT 
            a.id AS store,
            count(b.*) AS customer_count,
            count(b.*)/c.all_customers*100 AS percentage
        FROM stores a, customers b, all_customers c
            WHERE ST_DWithin(a.geom::geography,b.geom::geography,5000)
                GROUP BY a.id