Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] finding customers (constituents) within a x-mile radius of some point

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] finding customers (constituents) within a x-mile radius of some point
 Login/Join
 
Silver Member
posted
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>,


WebFOCUS 7.7.05
Windows 2008
 
Posts: 39 | Registered: March 02, 2005Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Silver Member
posted Hide Post
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.


WebFOCUS 7.7.05
Windows 2008
 
Posts: 39 | Registered: March 02, 2005Report This Post
Platinum Member
posted Hide Post
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.


WF 7.7.04, WF 8.0.7, Win7, Win8, Linux, UNIX, Excel, PDF
 
Posts: 175 | Location: Pomona, NY | Registered: August 06, 2003Report This Post
Virtuoso
posted Hide Post
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...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
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!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Well Leo, did you make your gift officers happy?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
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,


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Silver Member
posted Hide Post
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!).


WebFOCUS 7.7.05
Windows 2008
 
Posts: 39 | Registered: March 02, 2005Report This Post
Member
posted Hide Post
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


WebFOCUS 8.1.0.5m
 
Posts: 26 | Registered: October 09, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] finding customers (constituents) within a x-mile radius of some point

Copyright © 1996-2020 Information Builders