Matching closest long/lat
Posted: Sat Feb 12, 2011 1:34 pm
Using Maxmind's GeoIP software, we can narrow down the LONG/LAT to relative accuracy within 25 miles around 80% of the time.
Now, we don't want to use any of the other information provided by MaxMind, because there are a lot of discrepancies between feature names, i.e., cities, to perform a look up. We plan on attempting such a look up if other methods fail to locate a feature, but for performance reasons, look ups on floats are much faster than strings.
Now, I'm a little clueless on how we can find the closest matching LAT/LONG given from Maxmind to our database. The problem is, our datbase features has a much higher precision compared to that of Maxmind, therefore a straight comparison might not be effective. If we try applying a ROUND() to the column during query, that will obviously be really slow.
Given the following data, would the fastest way simply be something like
LAT 79.93213
LONG 39.13111
SELECT `feature_name` FROM `geo_features`
WHERE long BETWEEN 79.93213 AND 79.79.93214
AND last BETWEEN 39.13111 AND 39.13112
Can anyone thing of an elegant solution that will be blazing fast? I know there are some new spatial storage types in MySQL 5, perhaps anyone can provide a solution beyond the blinders I've seem to put up on myself.
Now, we don't want to use any of the other information provided by MaxMind, because there are a lot of discrepancies between feature names, i.e., cities, to perform a look up. We plan on attempting such a look up if other methods fail to locate a feature, but for performance reasons, look ups on floats are much faster than strings.
Now, I'm a little clueless on how we can find the closest matching LAT/LONG given from Maxmind to our database. The problem is, our datbase features has a much higher precision compared to that of Maxmind, therefore a straight comparison might not be effective. If we try applying a ROUND() to the column during query, that will obviously be really slow.
Given the following data, would the fastest way simply be something like
LAT 79.93213
LONG 39.13111
SELECT `feature_name` FROM `geo_features`
WHERE long BETWEEN 79.93213 AND 79.79.93214
AND last BETWEEN 39.13111 AND 39.13112
Can anyone thing of an elegant solution that will be blazing fast? I know there are some new spatial storage types in MySQL 5, perhaps anyone can provide a solution beyond the blinders I've seem to put up on myself.