Geek asks and answers

Like averages based on location proximity

I have an SQL table with geotags (longitude, latitude, value). The table accumulates quickly and has thousands of records. Therefore, querying a table for values ​​in a certain area returns a very large data set.

I would like to know a mean value method with close proximity to a single value, here is an illustration:

Table:

Long            lat           value
10.123001       53.567001      10
10.123002       53.567002      12
10.123003       53.567003      18
10.124003       53.568003      13

says my current location is 10.123004, 53.567004. If I ask for values ​​nearby, I will get four files with values ​​10, 12, 18 and 13. This works if the data set is relatively small. If the data is big, I would like to query sql for the rounded location (10.123, 53.567) and require sql to return something like

Long            lat           value
10.123       53.567      10 (this is the average of 10, 12, and 18)
10.124       53.568      13

Is it possible? How can we average a large dataset based on locations?

sql ?

+3
3

GROUP BY , AVG :

SELECT ROUND(Long, 3) Long, 
       ROUND(Lat, 3) Lat, 
       AVG(value) 
 FROM Table
 GROUP BY ROUND(Long, 3), ROUND(Lat, 3)

WHERE .

+2

, . SQL, , , 3, Round, - , , .

(, 3), (, 3), () (, 3), (, 3)

+1

- - , .

- :

select *
from table
where long between @MyLong - @DeltaLong and @MyLong + @DeltaLong and
      lat between @MyLat - @DeltaLat and @MyLat + @DeltaLat

To do this, you need to define @DeltaLong and @DeltaLat.

Rounding works great for summing up if that is your problem.

+1
source

More articles:


All Articles