-1

I have a table in SQL Server with a huge dataset containing accident data with columns such as:

id, accidentNumber, accidentDate, latitude, longitude

The requirement is to get the hotspots based on a selected date. is there a way I can group the accidents nearby each other and count them. to show that point x with 500 meter radius has N amount of accidents. and order them by the highest number of accidents.

the desired data set should look like

geopoint (lat,lng) accient Count
##.#### , ##.##### 8540
##.#### , ##.##### 8134
##.#### , ##.##### 7751

I already merged the latitude and longitude into a point

SELECT TOP 100
    CASE
        WHEN ((Latitude IS NOT NULL) AND (Longitude IS NOT NULL))
            THEN geography::Point(Latitude, Longitude, 4326)
        ELSE NULL           
    END AS geopoint, 
    accidentID, AccidentNumber 
FROM
    accidents
ORDER BY
    accidentID

But I could not figure out how to group and count them.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohammed Hussein
  • 1
  • Do you have information about vehicle type or driver associated with each accident? Do you want to group accidents by a set of points drawn on the earth's surface? Can you associate the accidents with an intersection of two roads? – Paul Williams Nov 08 '21 at 17:18
  • What about when `x` is within radius of `y`, and `y` with `z`, but not `x` with `z`? – Charlieface Nov 08 '21 at 17:21

1 Answers1

0

Maybe you could use a reference "grid" of points and calculate for each point of this grid the distance between this point and the accident location. This should allow you to count the number of accidents within a certain range from the reference points.

Distance calculation example for Sql Server : Calculating distance between two points (Latitude, Longitude)

Ali Hmaou
  • 1