5

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

1 Answer 1

9

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.

enter image description here

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
3
  • 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, 2015 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, 2015 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, 2015 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.