Crystal UFL to return distance between 2 zip codes 4

[dgillz]

Has anyone ever heard of this? I would like to be able to return the approximate # of miles between 2 zip codes. I have to believe this exists, but I have not seen it anywhere.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[LyleU]

Found this at the US Census Bureau:

http://www.census.gov/geo/www/gazetteer/places2k.html#zcta

This is the file layout for ZCTA's - zip code tabulation areas and it provides latitude and longitude as part of the file. Hopefully that will get you started in the right direction.

Lyle
.............................

The ZCTA file contains data for all 5 digit ZCTAs in the 50 states, District of Columbia and Puerto Rico as of Census 2000. The file is plain ASCII text, one line per record.

* Columns 1-2: United States Postal Service State Abbreviation
* Columns 3-66: Name (e.g. 35004 5-Digit ZCTA - there are no post office names)
* Columns 67-75: Total Population (2000)
* Columns 76-84: Total Housing Units (2000)
* Columns 85-98: Land Area (square meters) - Created for statistical purposes only.
* Columns 99-112: Water Area (square meters) - Created for statistical purposes only.
* Columns 113-124: Land Area (square miles) - Created for statistical purposes only.
* Columns 125-136: Water Area (square miles) - Created for statistical purposes only.
* Columns 137-146: Latitude (decimal degrees) First character is blank or "-" denoting North or South latitude respectively
* Columns 147-157: Longitude (decimal degrees) First character is blank or "-" denoting East or West longitude respectively

 

[dgillz]

OK that is cool, I can import this into a SQL database, but does anyone know how to calculate distance given latitude and longitude?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[dgillz]

OK I found a formula for distance that works perfectly in Excel, so I now need crystal functions for radians() and Arcosine() to make this work. I am using crystal XI Developer.

Are these fnctions available in CR2008?

Does anyone know of a UFL for them?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[lbass]

Don, If you do a search on radians in the Help section, it references multiple functions that returns values in radians. One is called Acos (inverse cosine)--could this be what you are looking for? This is in XI.

-LB

 

[dgillz]

I have XI developer and acos() is not a valid function.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[lbass]

That's what I have. Help->search->acos says that it is OLAP syntax, so I think you would have to use the OLAP Cube Report Wizard on the start menu.

-LB

 

[kenhamady]

Millet Software has just added a Distance() function that returns the distance between 2 points, given their latitude and longitude. I got the message tonight so maybe this thread inspired him.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[dgillz]

Update: I got this to work, I found a distance forsmula on the web that did not require and new UFLs.

Interesting report request. My customer gets a call from say, Louisville, KY and he needs to refer the caller to a distributor. He knows off the top of his head he has no distributors in KY but where does he send them?

So now he has a report that prompts for a zip code and returns all distributors, sorted by the closest one to the caller. Lots of other data on his distributors is also displayed: address, phone, sales volume in the last 12 months, etc.

It was one of the most interesting reports I have written in a while.

Thanks to all that helped - Ido, LB and LyleU.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[kenhamady]

Don,

I would be interested in seeing the formula you found for distance. Is it this or did you find another?

http://www.meridianworlddata.com/Distance-calculation.asp

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[dgillz]

Ken,

Yes that's the one. I wanted to use the great circle formula, the most accurate of the formulas, but alas no arccosine function in crystal. However I was very please with my results.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[chelseatech]

My trig is a little rusty, but I think you can derive an acos function from atan. I'll need to drag out some old text books to check.

Found this page on Wiki that explained it all nicely...

http://en.wikipedia.org/wiki/Trig_functions#Reciprocal_functions

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[CR85user]

You can use the Atn() function instead can't you?

Numbervar x:= (sin(lat1/57.2958) * sin(lat2/57.2958)) + (cos(lat1/57.2958) * cos(lat2/57.2958) * cos(lon2/57.2958 - lon1/57.2958))

3963.0 * atn(sqr(1-x^2)/x)

CR8.5 / CRXI - Discovering the impossible

 

[CR85user]

Sorry forgot the ;

Numbervar x:= (sin(lat1/57.2958) * sin(lat2/57.2958)) + (cos(lat1/57.2958) * cos(lat2/57.2958) * cos(lon2/57.2958 - lon1/57.2958));

3963.0 * atn(sqr(1-x^2)/x)

CR8.5 / CRXI - Discovering the impossible

 

[MJRBIM]

What is the MEASUREMENT that the Distance is returned in by this formula (Miles, or Kilometers)...?

Senior Business Intelligence Consultant
Toronto, Canada

SeagateInfo 7 -> Crystal Enterprise -> BOE-XI (R2) / (R3.1) | Crystal ( 7 -> 2008) design | WebI design | Best practices for Java / .Net / Web Services

 

[CR85user]

With the value 3963.0 it is given in Miles - For kilometres use 6378.7

'J

CR8.5 / CRXI - Discovering the impossible

 

[dgillz]

I cannot use variables as I need to sort by the group totals. But yes, this is what I said, I found an option that does not require UFLs.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[kenhamady]

If you declare the variable local and use WhileReadingRecords instead of WhilePrintingRecords, then this formula should not affect sorting.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[dgillz]

Ken,

Even for a TopN/Group sort?

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[kenhamady]

You can total a formula that uses local variables as long as it is not WhilePrinting. And if you can total it you can use the total in a Group Sort.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/