# Find number of points within certain radius of a set of points

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`

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
``````
• Thanks...this got me what I needed, I think. Not sure what you mean by this or how to do it: "When you want to use a metric system, you have to convert the geometries to geography format" – jonmrich May 11 '15 at 19:29
• Have a look here for further information: gis.stackexchange.com/a/26092/15459 or gis.stackexchange.com/a/6683/15459. In particular the first answers. When you have stored spatial data in WGS 84 (long lat, degrees), you have to convert them into the geography type to calculate e.g. distances in meters. Or in your case points within 10000 meters.Otherwise use the "equivalent" of 10000m in degrees. – Stefan May 11 '15 at 19:48
• Slightly unrelated, but realize I need it. How could I add another column that gives me a percentage that represents the customer count for a specific store divided by the total number of rows in the customer table? Having trouble getting both values together. – jonmrich May 11 '15 at 21:03