0

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...

  1. 10,000 meters
  2. more than 10,000, but less than 50,000
  3. more than 50,000, but less than 10,0000
  4. 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.

  • All vs all is going to be a heavy query. You might consider to limit it somehow with ST_DWithin (e.g. few degrees) so spatial indices (if set properly) would be used. Then you'd calculate the distance for all pairs and use it with case/when/then/else statements to calculate distance group column. Finally, you'd group on that in an outer query. – mlt May 12 '15 at 0:17
  • One table is a couple hundred (stores) and the other could be much larger (1MM max, but usually around 100k). Does that make it any different? – jonmrich May 12 '15 at 0:22
1

Here is a pseudo SQL. Unfortunately SQL fiddle doesn't support PostGIS.

create table customer (
  loc real,
  name text
);
create index on customer(loc);
create table location (
  loc real,
  name text
);
create index on location(loc);
insert into customer values (1,'c1'), (2,'c2');
insert into location values (1.1, 'l1'), (1.2, 'l2');
select cat, count(*) from (
  select bussiness, "name", dist ,
    case when dist>.5 then 'far'
         when dist > .11 then 'nearby'
         else 'local' end cat
  from (
    select l.name bussiness, c.name "name", abs(l.loc - c.loc) dist -- Use your ST_Distance_Sphere
    from location l, customer c
    where abs(l.loc - c.loc)< .89 -- ST_DWithin is here
  ) foo
) bar
group by cat

This will return 2-column table with distance group and # of customers within.

1

A little help: Create buffer views: b1 (10000), b2 (50000), b3(100000) example:

CREATE OR REPLACE VIEW b1 as 
SELECT ST_Buffer(the_geom,10000) as geom, store_name, fid 
FROM stores;

Create ring views: r2, r3 example:

CREATE OR REPLACE VIEW r2 as 
SELECT ff.fid, ff.store_name, ST_Difference(ff.the_geom, g.the_geom) as new_geom  
FROM b2 ff
JOIN b1 g ON (g.fid= ff.fid)
GROUP BY ff.fid, ff.store_name, g.the_geom, ff.the_geom;

Count customers in b1, r2, r3, st_disjoint(b3)

It will generate duplicates if the stores are closer to each other than the maximum distance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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