SQL query to query nearby points of interest based on lat/long - SQLite

Question

Given a database that contains three fields:

Latitude Longitude Proximity

Where Lat and Long are GPS coordinates, and Proximity is (some unit - feet? Seconds? Minutes?)

And given the user's current GPS lat/long...

I want to write a SQL query that will retrieve all rows where the user is within "Proximity" of those rows.

And the trick: This has to work in SQLite, which only supports fairly primitive data types. No cheating and relying on SQL Server (or some other product that provides better geospace functions).

Any suggestions?

Answer 1

The Haversine formula is what you want. Using simple Euclidian distance formula isn't sufficient, because the curvature of the earth affects the distance between two points.

Creating a Store Locator with PHP, MySQL & Google Maps is an article about implementing this solution with MySQL, but SQLite doesn't support trig functions.

Check out the answers in Calculating Great-Circle Distance with SQLite here on Stack Overflow for further tips on extending SQLite functions so you can solve this.

Answer 2

You also know that there is a plugin for SQLite called Spatialite which has all the same functions as PostGIS and SQLServer? I assume you are trying to use SQLLite on the iPhone or in the browser or something. If not they I HIGHLY reccomend spatialite.

Answer 3

This: http://www.thismuchiknow.co.uk/?p=71 is the answer. A C-based custom function for sqlite. This can be used within an iOS app. It assumes you have columns named latitude and longitude, and calculates the difference between them and any lat/long coordinates you provide. Excellent write-up, works as-is.