Jump to content
Sign in to follow this  
JTSmith

Problem with an If statement in a calculation...

Recommended Posts

I have two tables, Properties and Quotes. On the Quotes table, I have a portal that sorts the properties by distance. For example, I put in a Quote for Miami, Florida, and it pulls the Longitude and Latitude from a third table of Zip Codes, then sorts them in distance from Miami. This part works great, but now what I want to do is have a search that sorts the same properties by distance from an exact longitude and latitude, for example a starbucks in Miami. When I enter the If statement, it doesn't work correctly. It either shows a mix of the properties, or they all show a distance of zero. I'm not sure what i'm doing wrong...

Here is the calculation, which works fine (_lat & _lon are fields on the property records:

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _Lat2 = ( Quotes 2::_Lat2 * Pi ) / 180

; _Lon2 = ( Quotes 2::_Lon2 * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

]

;

Round ( _Final / 1000 ; 2 )

)

-----------------------------------------

When I make it:

If ( Quotes 2::Target Longitude = "" ;

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _Lat2 = ( Quotes 2::_Lat2 * Pi ) / 180

; _Lon2 = ( Quotes 2::_Lon2 * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

]

;

Round ( _Final / 1000 ; 2 )

)

;

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _Lat2 = ( Quotes 2::Target Latitude * Pi ) / 180

; _Lon2 = ( Quotes 2::Target Longitude * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

]

;

Round ( _Final / 1000 ; 2 )

)

)

It doesn't work. Am I missing something?

Any advice would be great...

Thanks!

Share this post


Link to post
Share on other sites

How about consolidating those calcs (not tested):

Let (

[

_R = 6367000

; _Lat1 = ( _Lat1 * Pi ) / 180

; _Lon1 = ( _Lon1 * Pi ) / 180

; _TARGLON = Quotes 2::Target Longitude

; _TARGLAT = Quotes 2::Target Latitude

; _QLON = Case( IsEmpty( TARGLON ) ; Quotes 2::_Lon2 ; TARGLON

; _QLAT = Case( IsEmpty( TARGLON ) ; Quotes 2::_Lat2 ; TARGLAT

; _Lat2 = ( _QLAT * Pi ) / 180

; _Lon2 = ( QLON * Pi ) / 180

; _dlon = _Lon2 - _Lon1

; _dlat = _Lat2 - _Lat1

; _a = ( Sin ( _dlat / 2 ) ) ^ ( 2 ) + Cos ( _Lat1 ) * Cos ( _Lat2 ) * ( Sin ( _dlon / 2 ) ) ^ ( 2 )

; _Res = 2 * Asin ( Min ( 1 ; _a ^ ( 1 / 2 ) ) )

; _Final = _R * _Res

; result = Round ( _Final / 1000 ; 2 )

]

;

result

)

I like to make the result a variable so that I can easily swap it out with other vars in the data viewer while testing.

Share this post


Link to post
Share on other sites

this works perfectly for me and return is in miles

lat2 and lon2 is the starting coords, destination is lat1 and lon1


If(Lat1 ="" or Lon1="" or lat2="" or Lon2="";"";

If (Sin((Lat1/57.29577951))*Sin(lat2/57.29577951)+Cos(Lat1/57.29577951)*Cos(lat2/57.29577951)*Cos(Lon1/57.29577951-Lon2/57.29577951) > 1

; 3963.1*Acos(1);3963.1*Acos(Sin(Lat1/57.29577951)*Sin(lat2/57.29577951)+Cos(Lat1/57.29577951)*Cos(lat2/57.29577951)*Cos(Lon1/57.29577951-Lon2/57.29577951))))

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.