CODEWITHSUNDEEP
mysql
david
david

Reputation: 23

sql query: cluster lat lng in specific area, order by points

I have this mysql table:
CREATE TABLE markers (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
lat FLOAT( 10, 6 ) NOT NULL ,
lng FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;

with following data:

lat     lng       id
37.0010 -122.0010 1
37.0020 -122.0020 2

37.1010 -122.1010 3
37.1020 -122.1020 4
37.1030 -122.1030 5

37.2010 -122.2010 6

38.9000 -123.9000 7
38.9010 -123.9010 8

I know how to get the nearest locations:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

from http://code.google.com/intl/en/apis/maps/articles/phpsqlsearch.html

But how to cluster those nearest locations if there are more points inside a specific distance? what i want is this result:

newlat     newlng       count_points
37.1020    -122.1020    3
37.0015    -122.0015    2
37.2010    -122.2010    1

Any input is greatly appreciated. Thanks

Upvotes: 2

Views: 1127

Answers (2)

Cybercartel
Cybercartel

Reputation: 12592

You can use a quadkey or a geohash to cluster spatial data. A quadkey is often use to subdivide the map but you can also use it to cluster points of interest. There are many ways to compute a quadkey or geohash. The simplest is a morton curve.

Upvotes: 0

Matthew Scragg
Matthew Scragg

Reputation: 4638

SELECT 
id, 
( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance, 
COUNT(*) as count_points  
FROM markers 
GROUP BY newlat, newlng 
HAVING distance < 25 
ORDER BY distance ASC, count_points DESC
LIMIT 0 , 20;

Upvotes: 1

Related Questions