How to Use SQL Where Conditional For Distance Between a Row's Lat & Long, & Current Lat & Long

Question

So I'm writing an Android app and want to write a SQLite query such that it selects only rows that contain event location info utilizing a distance constraint in miles and current longitude and latitude and ordering by that calculated distance. Eventually once I've gotten further along the SQLite database will instead make this query on a database on Azure which may or may not open up some functionality, rather than querying a SQLite database that's inserted with mock data. But since I'm working with SQLite ATM I'm trying to come up with a Android-SQLite solution.

The SQLite table schema is such:
event_id | event_name | event_latitude | event_longitude

Below is some pseudocode mixed with real code to get an idea of what I'm trying to create:

    sqlite_table = "some_table";
    double distanceConstraint = 50.00; //in miles

     //using Decimal Degrees (DDD) as it's probably easiest to calculate with
    currentLat = 41.40338;
    currentLong = 2.17403;

    mCursor = mDb.query(true, 
                        SQLITE_TABLE, 
                        new String[] {event_id, event_name},
                        /* (where) current lat & long is within distanceContraint of event_latitude & event_longitude*/, 
                        null, 
                        null, 
                        null, 
                        /* distanceBetweenPointsResult */ + " DESC", 
                        null);

How do I write the query to calculate the distance (in miles) between current location and the event's and filter and order accordingly?

Answer 1

Background: Using the Haversine Formula

To calculate the distance between two coordinates, it seems common practice to use the Haversine formula. Here's a specific post on a MySQL Haversine-based query: https://stackoverflow.com/a/574736/3784227

The implementation it suggests is based on the following Google Maps API article: https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

A Proposed Solution to Your Question

Here's my (untested) attempt at a SQL query to solve your problem. I use the Haversine formula mentioned above; specifically the version Google Maps API discusses.

"SELECT event_id, event_name, event_latitude, event_longitude, 
(3959 * acos( cos( radians(event_latitude) ) 
* cos( radians(" + currentLat + ") ) * cos( radians(" + currentLon + ") 
- radians(event_longitude) ) + sin( radians(event_latitude) ) 
* sin(radians(" + currentLat + ")) ) ) AS distance 
FROM " + sqlite_table + " 
HAVING distance < " + distanceConstraint + " 
ORDER BY distance LIMIT 0, 20;" 

If I'm not mistaken, this should return event_id, event_name, event_latitude, and event_longitude for up to 20 locations within a distanceConstraint radius from {currentLat, currentLon}, ordered by distance.

I'm not familiar with how exactly to implement this query with Android / SQLite, but hopefully this helps point you in the right direction.