Forum Discussion

gnotcirdec's avatar
gnotcirdec
Frequent Visitor
4 years ago
Solved

Comparing latitude and longitude in a table against different table of latitude and longitudes

Hello.

 

I'm trying to compare data points in different tables to see if they are within each other. If tableA latitude and longitude are inside any of the points inside fence indicated by tableB, "isInside" column will return 1, if not 0. I'm looking to do this all in Power Query

 

Here is an example of tableA:

DeviceLatitudeLongitudeisInside
A10333.77519-118.117820
A10434.01295-118.275391
A10533.71391-118.263180

 

Here is tableB (fence):

TopLatitudeTopLongitudeBottomLatitudeBottomLongitude
34.02513433-118.291683134.01119056-118.2682514
34.03056715-118.255505634.02529439-118.2521689

 

Any help would be appreciated!

 

Thank you.

Need Help
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi gnotcirdec 

     

    Here is a DAX method. You can create a new column with below code.

    Is Inside = 
VAR _table =
    ADDCOLUMNS (
        TableB,
        "IsInsideFlag",
            IF (
                TableA[Latitude] >= TableB[BottomLatitude]
                    && TableA[Latitude] <= TableB[TopLatitude]
                    && TableA[Longitude] >= TableB[TopLongitude]
                    && TableA[Longitude] <= TableB[BottomLongitude],
                1,
                0
            )
    )
RETURN
    IF ( COUNTROWS ( FILTER ( _table, [IsInsideFlag] = 1 ) ) >= 1, 1, 0 )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Try changing the last line of [Is Inside] to

    MINX ( FILTER ( _table, [IsInsideFlag] = 1 ), [Location] )

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi gnotcirdec 

     

    Here is a DAX method. You can create a new column with below code.

    Is Inside = 
VAR _table =
    ADDCOLUMNS (
        TableB,
        "IsInsideFlag",
            IF (
                TableA[Latitude] >= TableB[BottomLatitude]
                    && TableA[Latitude] <= TableB[TopLatitude]
                    && TableA[Longitude] >= TableB[TopLongitude]
                    && TableA[Longitude] <= TableB[BottomLongitude],
                1,
                0
            )
    )
RETURN
    IF ( COUNTROWS ( FILTER ( _table, [IsInsideFlag] = 1 ) ) >= 1, 1, 0 )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

    • gnotcirdec's avatar
      gnotcirdec
      Frequent Visitor
      4 years ago

      Thank you! This works just as well.

    • gnotcirdec's avatar
      gnotcirdec
      Frequent Visitor
      4 years ago

      Hi v-jingzhang ,

      Is there a way to modify the solution to include Text as the result?

       

      For example:

       

       

       

       

      Any help would be appreciated!

       

      Thanks,

      Cedric

       

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        Try changing the last line of [Is Inside] to

        MINX ( FILTER ( _table, [IsInsideFlag] = 1 ), [Location] )