topic Re: find addresses within 30 miles radius in SAS Programming

find addresses within 30 miles radius

MarcTC — Wed, 04 May 2011 00:02:45 GMT

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?

Re: find addresses within 30 miles radius

Doc_Duke — Wed, 04 May 2011 01:09:48 GMT

your less than or equal to sign chopped of most of your SQL and question.

Re: find addresses within 30 miles radius

chang_y_chung_hotmail_com — Wed, 04 May 2011 22:03:53 GMT

...

> It is very slow because it uses Cartesian product. Is

> there any better way to speed up the computation?

...

See Howard Schreier's excellent NESUG 16 (2003) paper.

Re: find addresses within 30 miles radius

MarcTC — Wed, 04 May 2011 23:19:01 GMT

"See Howard Schreier's excellent NESUG 16 (2003) paper. "

Thanks, Chung expert.