Hi,
On occasion our gift officers want to find prospects that live within a certain radius of some point. For example, if they are travelling through New York City, they might want to reach out to their prospects that live within a 10-mile radius. Is there a way to do that in WebFOCUS if you have customer (prospect) addresses and zip codes? Do we need any add-ons to WebFOCUS (like the Google Maps API)?

Thanks for any advice/suggestions.

This message has been edited. Last edited by: <Kathryn Henning>,

Leo,

There are a few posts on this subject that covered - in depth - how to calculate distance over the surface of the earth.

To locate them, search on "F_DIST".

T

Hi, Tony
Sorry, I read the original post from 2007 and the responses but I'm not sure how that helps me. If I am currently at zip code 12345 and I want to know what customers in my database live within a 10 mile radius of that zip code, how do I find that out? I don't have latitude and longitude for all zip codes, which it looks like you need for the f_dist function. I guess what I am looking for is information on GIS adapters and how they work with WebFOCUS. Since I have never used these before and don't know a lot about them any advice on where to start would be much appreciated. I am trying to leverage our investment in WebFOCUS rather than having to purchase software specific to gift officers searching for donors.

In order to calculate distance you need to have latitude and longitude. If you have addresses of your customers, there are utilities and web services online that will provide you that data for free if your looking for just a few addresses. For large conversions there are for fee services as well.

Leo,
You can download a csv file containing zip codes lat. and long. from:
http://greatdata.com/free-zip-code-database
From then, it is Pythagoras...

Leo,
Taking Tony's hint (one should always do...) and looking up F_DIST, you do find the necessary calculations.
The "free-zip-code-database" is an Excel sheet.
You can define a MASTER to read it by using WF's Excel direct retrieval interface.
This will give you:

  
FILENAME=ZIPCODES, SUFFIX=DIREXCEL,
 DATASET=C:\ibi\apps\zipcodes\free-zip-code-database.xls, $
  SEGMENT=ZIPCODES, SEGTYPE=S0, $
    FIELDNAME=ZIPCODE, ALIAS=ZIPCode, USAGE=I8, ACTUAL=A11,
      MISSING=ON, $
    FIELDNAME=STATE, ALIAS=State, USAGE=A2, ACTUAL=A2,
      MISSING=ON, $
    FIELDNAME=CITY, ALIAS=City, USAGE=A37, ACTUAL=A37,
      MISSING=ON, $
    FIELDNAME=COUNTY, ALIAS=County, USAGE=A31, ACTUAL=A31,
      MISSING=ON, $
    FIELDNAME=LATITUDE, ALIAS=Latitude, USAGE=D8.2, ACTUAL=A64,
      MISSING=ON, $
    FIELDNAME=LONGITUDE, ALIAS=Longitude, USAGE=D9.2, ACTUAL=A64,
      MISSING=ON, $

There are some odd codes there with 0 values for Lat and Long, so I copied the data into a Focus file deleting the o's on the way.

  
FILENAME=ZIP, SUFFIX=FOC     ,
 DATASET=zipcodes/zip.foc, $
  SEGMENT=SEG01, SEGTYPE=S1, $
    FIELDNAME=FOCLIST, ALIAS=E00, USAGE=I5, $
    FIELDNAME=ZIPCODE, ALIAS=E01, USAGE=I8,
      MISSING=ON, $
    FIELDNAME=STATE, ALIAS=E02, USAGE=A2,
      MISSING=ON, $
    FIELDNAME=CITY, ALIAS=E03, USAGE=A37,
      MISSING=ON, $
    FIELDNAME=COUNTY, ALIAS=E04, USAGE=A31,
      MISSING=ON, $
    FIELDNAME=LATITUDE, ALIAS=E05, USAGE=D8.2,
      MISSING=ON, $
    FIELDNAME=LONGITUDE, ALIAS=E06, USAGE=D9.2,
      MISSING=ON, $

Then for the coding:

  
-*dist.fex
-DEFAULT &Pi = 3.141592
-DEFAULT &MYZIP=10001
-DEFAULT &LATITUDE=0, &LONGITUDE=0
DEFINE FUNCTION F_SINE(angle/I3)
Rads/D33.12 = angle / 180 * &Pi ;
F_SINE/D20.12 = Rads - ((Rads ** 3)/(1*2*3))
                     + ((Rads ** 5)/(1*2*3*4*5))
                     - ((Rads ** 7)/(1*2*3*4*5*6*7))
                     + ((Rads ** 9)/(1*2*3*4*5*6*7*8*9));
END
-*
DEFINE FUNCTION F_DIST(Lat1/D16.6, Long1/D16.6, Lat2/D16.6, Long2/D16.6)
-* Earth's mean radius in km
Earth_R/D8    = 6371;
dLat/D20.6    = Lat2 - Lat1;
dLong/D20.6   = Long2 - Long1;
F_DIST/D20.4 = SQRT(dLat * dLat + dLong * dLong) * 1/(F_SINE(90 - dLat / 2)) / 360 * (2 * &Pi * Earth_R);
END
-RUN
TABLE FILE ZIP
IF ZIPCODE EQ &MYZIP
PRINT LATITUDE LONGITUDE
ON TABLE HOLD
END
-RUN
-READFILE HOLD
-CLOSE HOLD
DEFINE FILE ZIP
DIST/D7.2=F_DIST(&LATITUDE, &LONGITUDE, LATITUDE, LONGITUDE);
END
TABLE FILE ZIP
PRINT ZIPCODE STATE CITY COUNTY LATITUDE LONGITUDE DIST 
WHERE DIST LE 10;
END

Now, download the zipcodes, change the value of &MYZIP et voila!

Well Leo, did you make your gift officers happy?

Notes:

As stated, the formula yields distances in kilometers; for miles, adjust accordingly.

Actually, for the present exercise involving a short distance like 10 miles, and given that the geo coordinates are only approximate (zip centroids), the adjustment from straight-line distance [drilling-through-the-earth] to length of the arc along the Earth's surface can be ignored.

See http://www.math.ksu.edu/~dbski/writings/haversine.pdf for a clear exposition.

Edit:

F_DIST/D20.4 = SQRT(dLat * dLat + dLong * dLong) * 1/(F_SINE(90 - dLat / 2)) / 360 * (2 * &Pi * Earth_R);

looks suspicious to me, in that the argument of SQRT() treats difference in longitude and latitude as contributing equally to the distance, which is clearly untrue except near the Equator.

Here's a set of functions, based on the cited paper, that should yield chord distance in miles:

-DEFAULT &Pi = 3.141592
DEFINE FUNCTION F_SIN(Degrees/D3)
Rads/D6  = Degrees * &Pi / 180;
F_SIN/D6 = Rads  - ((Rads ** 3)/(1*2*3))
                 + ((Rads ** 5)/(1*2*3*4*5))
                 - ((Rads ** 7)/(1*2*3*4*5*6*7))
                 + ((Rads ** 9)/(1*2*3*4*5*6*7*8*9));
END
DEFINE FUNCTION F_COS(Degrees/D3)
Rads/D6  = Degrees * &Pi / 180;
F_COS/D6 = 1     - ((Rads ** 2)/(1*2))
                 + ((Rads ** 4)/(1*2*3*4))
                 - ((Rads ** 6)/(1*2*3*4*5*6))
                 + ((Rads ** 8)/(1*2*3*4*5*6*7*8));
END
DEFINE FUNCTION F_MILES(Lat1/D6, Long1/D6, Lat2/D6, Long2/D6)
-* Earth's mean radius in miles
Earth_R/D6    = 3963;

X1/D6 = F_COS(Lat1)* F_COS(Long1);
Y1/D6 = F_COS(Lat1)* F_SIN(Long1);
Z1/D6 = F_SIN(Lat1);

X2/D6 = F_COS(Lat2)* F_COS(Long2);
Y2/D6 = F_COS(Lat2)* F_SIN(Long2);
Z2/D6 = F_SIN(Lat2);
F_MILES/D12.2 = SQRT( (X1-X2)**2 + (Y1-Y2)**2 + (Z1-Z2)**2 ) * Earth_R;
END

Edited 7/25/16 --

Removed extraneous blank in "F_MILES" (was "F_ MILES/D12.2 = "...)

This message has been edited. Last edited by: j.gross,

Thanks for all of the responses. I am in the process of moving into a new office so I haven't had a chance to try things out yet, but am looking forward to working on this (sure beats unpacking boxes!).

quote:

Originally posted by Danny-SRL:
Leo,
You can download a csv file containing zip codes lat. and long. from:
http://greatdata.com/free-zip-code-database
From then, it is Pythagoras...

This link no longer works