Using Longitude & latitude to create location based map.
I am working on crash data from Source Signal4. I have attached sample data picture for reference. So I am trying to create a report where I can locate multiple crashes between 500ft radius. I have longitude & latitude of each crash loaction. How do I create a map which will show me only location on a single roadway for e.g 5mile streach & show me hotspots where I have more that 5 incidents withing 500ft oof each other. I have used power bi for just few months & have understanding of how dax works but could'nt find any resolution for similar issue in community. This map helps me work on safety features for roadway strech.
Velocity of Each User every time longitude & latitude is captured (Excel Example/Solution Provided)
I have GPS locations for many users arranged by datetime. I would like to pull this from from the DB in Power BI and create a new column for distance and another for velocity for each user between two longitude and latitude coordinates. B C D E F G H agent_user_id longitude latitude location_action verification_id verification_type created_at 3799 3.42061 6.42999 IN_TRANSIT 457265 ADDRESS 08/11/2021 18:01 47593 6.9825 4.81352 IN_TRANSIT 457435 ADDRESS 08/11/2021 18:01 3799 3.42067 6.43015 IN_TRANSIT 457265 ADDRESS 08/11/2021 18:03 40823 5.73639 5.54055 ONLINE 0 NONE 08/11/2021 18:03 42677 5.58848 6.2923 ONLINE 0 NONE 08/11/2021 18:03 47593 6.9825 4.81352 IN_TRANSIT 457435 ADDRESS 08/11/2021 18:03 26035 7.06534 4.84438 ONLINE 0 NONE 08/11/2021 18:04 26035 7.06534 4.84438 ONLINE 0 NONE 08/11/2021 18:04 In excel I did this by sorting the columns first by agent_user_id then by created_at. I then used the below formulas to calculate time between pings, distance between coordinates and velocity. For time and distance, the if statement does the calculation only if its the same agent_user_id else results in 0. Columns listed above begin with B. time_hr distance_km kmph =IF(B2=B1,(H2-H1)*24,0) =IF(B2=B1,ACOS(COS(RADIANS(90-D2)) * COS(RADIANS(90-D1)) + SIN(RADIANS(90-D2)) * SIN(RADIANS(90-D1)) * COS(RADIANS(C2-C1))) * 6371,0) =IFERROR(J2/I2,0)
