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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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