cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DaniloS
New Member

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  ?

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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:

Vlianlmsft_0-1634879854721.png

 

 

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
                    )
        )
    )

Vlianlmsft_1-1634879854591.png

 

 

View solution in original post

4 REPLIES 4
V-lianl-msft
Community Support
Community Support

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:

Vlianlmsft_0-1634879854721.png

 

 

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
                    )
        )
    )

Vlianlmsft_1-1634879854591.png

 

 

View solution in original post

Thank you for the tip. It worked very well.

rajendran
Community Champion
Community Champion

Hi

 Pls refer this https://community.powerbi.com/t5/Desktop/Calculate-distance-between-Zip-Codes/td-p/1099351

It has the solution to calculate the distance between the locations . Use the latitude & longitude of school & accident location to derive the distance.

 

Thanks
Raj

Thank you for your help!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors