Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Identify geographic influence

Hello guys. I'm new here and in PB. I work  for the Brazillian government in the departament of transports. We are developing a project to relate car accidents registered locations with our middle schools locations to act in those schools in order to educate and prevent our children get in these accidents. I have latitude and longitude of many accidents and of all the schools in Brazil but I can't find a way to analyse the accidents in a 100 km radius of the schools to determine the type and the amount of accidents of each type. Can you guys give me some light to solve this  ?

Need Help
Tutorial Requests
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    When we want to calculate the distance between two locations, we need to know the coordinates of these two locations.

    Here’s the sample data:

     

     

    We now have the coordinates of current locations and destinations, using below formula we could get the distance between these locations.

    Distince (KM) =
12742 -- The diameter of the Earth (KM).
    * ASIN (
        -- Returns the arcsine, or inverse sine, of a number.
        SQRT (
            -- Returns the square root of a number.
            POWER (
                -- Returns the result of a number raised to a power.
                SIN ( -- Returns the sine of the given angle.
                'Table'[Current Lat] - 'Table'[Destination Lat] )
                    * PI () / 360,
                2
            ) -- PI () Returns the value of Pi, 3.14159265358979, accurate to 15 digits.
                + COS (
                    -- Returns the cosine of the given angle.
                    'Table'[Current Lat] * PI () / 180
                )
                    * COS ( 'Table'[Destination Lat] * PI () / 180 )
                    * POWER (
                        SIN ( 'Table'[Current Long] - 'Table'[Destination Long] )
                            * PI () / 360,
                        2
                    )
        )
    )

     

     

4 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    When we want to calculate the distance between two locations, we need to know the coordinates of these two locations.

    Here’s the sample data:

     

     

    We now have the coordinates of current locations and destinations, using below formula we could get the distance between these locations.

    Distince (KM) =
12742 -- The diameter of the Earth (KM).
    * ASIN (
        -- Returns the arcsine, or inverse sine, of a number.
        SQRT (
            -- Returns the square root of a number.
            POWER (
                -- Returns the result of a number raised to a power.
                SIN ( -- Returns the sine of the given angle.
                'Table'[Current Lat] - 'Table'[Destination Lat] )
                    * PI () / 360,
                2
            ) -- PI () Returns the value of Pi, 3.14159265358979, accurate to 15 digits.
                + COS (
                    -- Returns the cosine of the given angle.
                    'Table'[Current Lat] * PI () / 180
                )
                    * COS ( 'Table'[Destination Lat] * PI () / 180 )
                    * POWER (
                        SIN ( 'Table'[Current Long] - 'Table'[Destination Long] )
                            * PI () / 360,
                        2
                    )
        )
    )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for the tip. It worked very well.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your help!