How to find GPS-coordinates within a bounding circle

Question

I have a series of GPS coordinates in decimal dotted format, multiplied by 1.000.000. For example a latitude of 51.1 and a longitude of 4.1 would be saved as Y 51100000 and X 4100000. These coordinates are saved in an SQlite 3 database.

Using Ruby 1.9.2 and Rails 3.0.8, I need to be able to get all records that are within a certain radius of a certain center point. For instance, given a center point of latitude 51 and longitude 4, I need to find all records within a 10 kilometer radius.

This article explains pretty well how to perform a query to get those records, but SQlite does not seem to support the mathematical functions that are used: http://www.movable-type.co.uk/scripts/latlong-db.html

Is there any other way I would be able to retrieve the proper records from the database that does not involve iterating through the entire table?

Thanks!

Answer 1

Unless you MUST use SQLite, try it with Sphinx:

I would convert GPS coordinates to lat/lng coordinates, and then use Sphinx (there is gem thinking-sphinx for rails: http://freelancing-god.github.com/ts/en/). With Sphinx you can search points within a given circle with Sphinx's function: @geodist

A brilliant example of how to do it you will see here: http://joeyschoblaska.com/blog_posts/220-thinking-sphinx-searching-by-location-and-keyword