Forum Discussion

vicpinto's avatar
vicpinto
Regular Visitor
2 years ago

Finding distance between 2 points on the same table with additional criteria.

Hello all,

 

I have a table called SECTORS_4G_ENM with LAT and LON as well as some additional information like this:

 

 

Now I can calculate the shortest distance between any of these points using a formula I picked up from this forum. The formula is below and has been modified to work with this table:

 

Shortest Distance Type=
    VAR __baseLat = SECTORS_4G_ENM[LAT]
    VAR __baseLng = SECTORS_4G_ENM[LON]
    VAR __goldname = SECTORS_4G_ENM[GOLD_NAME]
    VAR P = DIVIDE ( PI(), 180 )
    VAR __distance =
    ADDCOLUMNS (
    FILTER(ALLSELECTED ( SECTORS_4G_ENM ),SECTORS_4G_ENM[GOLD_NAME]<>__goldname),
    "Distance",
        VAR Lat1 = __baseLat
        VAR Lng1 = __baseLng
        VAR Lat2 = SECTORS_4G_ENM[LAT]
        VAR Lng2 = SECTORS_4G_ENM[LON]
        VAR A = 0.5 - COS ( ( Lat2-Lat1 ) * P ) /2 +
            COS ( Lat1 * P ) * COS ( lat2 * P ) * ( 1 - COS ( ( Lng2- Lng1 ) * P ) ) / 2
        VAR final = 12742 * ASIN ( ( SQRT ( A ) ) )
        RETURN final
    )
    VAR minVal_ = MINX ( __distance, [Distance] )
RETURN
MINX ( FILTER ( __distance, [Distance] = minVal_), SECTORS_4G_ENM[SITE_TYPE] )
 

However, this returns the shortest distance to any SITE_TYPE - Big or Small. This is not what i want to acomplish.

 

What I want to acomplish is the for every GOLD_NAME, regardless of whether it is Big or Small type, return the shortest distance to the nearest SITE_TYPE that is Big.

 

Thanks in advance.

Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that fully covers your issue.
    Please show the expected outcome based on the sample data you provided.