Suppose I have a address dataset(addr_lst) which has 100,000 US addresses and the following fields: idx, address, city, state, zip, x, y.
I want to find the addresses within 30 miles radius of each address.
Here is my sql to do this:
proc sql;
create table addr_30m as
select a.idx,
b.idx as idx2,
geodist(a.y, a.x, b.y, b.x,'M') as dist
from addr_lst a, addr_lst b
where geodist(a.y, a.x, b.y, b.x, 'M')<=30
; quit;
It is very slow because it uses Cartesian product. Is there any better way to speed up the computation?
... View more