MySQL Forums
Forum List  »  GIS

Re: list of unique points with a count
Posted by: andrew lorien
Date: August 11, 2008 04:54AM

For the other 300-odd people who found this in the first few google hits with no answer...

SELECT location, count(*) AS totals
FROM locations
GROUP BY location
ORDER BY totals;

will return a list of all distinct points with their counts... possibly the answer you were looking for but not the answer Cor wants. For that you'd have to make the GROUP BY line above use some very complex math functions to find clusters of latitude and longitude first. A coarse first try might be

SELECT DISTINCT ceil(latitude/10) AS cluster, count(ceil(latitude/10))
FROM locations
GROUP BY cluster

which would give you an idea of how many points lay within each 10-degree grid. You'd have to do it for longitude as well, then find the rows which matched clusters in both directions, then decide whether the really dense clusters deserve more or less detail...
anyone else want a go?

Options: ReplyQuote

Written By
Re: list of unique points with a count
August 11, 2008 04:54AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.