Most effective pathway to compare geographic locations? solutions pls

Question

Say I have a location A, with IP address, and a geolocator API with latitude and longitude. Now I want to find all instances that are within a 25-mile radius of location A. How can I compute this with the least amount of steps?

solution A: I can compute all distances between location A and all instances in the database, and display instances within 25 radius. (way too slow especially if I want a dynamic location, with a large database of locations)

solution B: I can group all instances in terms of zip code in addition to IP, and (lat, long). so that fewer distances between location A and instances needed to be computed. (better, but what if the IP address is at the border of another zip code, this will add to the amount of needed computation)

solution C: I can use trigonometry. using the latitude and longitude of location A. i can find each instance with in the 25 mile radius.

Can someone please describe a better way of comparing distances? ideas and suggestions are much appreciated (if further explanation is needed, pls ask) Thanks.

Answer 1

I'd use a combination of your proposed solutions a and c. You can query your database directly using a filter that only selects the locations within a 25 miles radius (or any other radius). Calculating the longitudinal difference in miles is a little tricky because the mileage of one degree in longitude differs with latitude. Kudos go out to this explanation: https://gis.stackexchange.com/questions/142326/calculating-longitude-length-in-miles#142327

Assuming you have the following DB schema with existing locations (only latitude and longitude as columns):

CREATE TABLE location (
    lat REAL,
    lon REAL
);

You're able to filter only the locations within a 25 mile radius using this query:

query = """
    SELECT (lat - ?) AS difflat, 
           (lon - ?) AS difflon
    FROM location
    WHERE POWER(POWER(difflat * 69.172, 2) + POWER(difflon * COS(lat*3.14/180.) * 69.172, 2), 0.5) < ?;
    """

Using the query then like this:

radius = 25 #miles
cursor.execute(query, (querylocation['lat'], querylocation['lat'], radius))

SQLite3 unfortunately doesn't support basic mathematical functions like COS and POWER but they can easily be created:

import math
con = sqlite3.connect(db_path)
con.create_function('POWER', 2, math.pow)
con.create_function('COS', 1, math.cos)