-1

I have a bunch of coordinates(lat and lon) in the database, and I would like to check if there have coordinates inside the certain radios circle area. I think there has a range of latitude and longitude, and I can filter the data in the SQL query statement.

The code will be something like the below,

center_point = ["37.7577627", "-122.4726194"] #lat and lon
radios = 5 #kilometer

range = someFunction(radios, center_point) 
#it return a maximum and minimum of lat and lon as tuple

lat1, lat2, lon1, lon2 = range[0], range[1], range[2], range[3]

sql_statement = "SELECT * FROM tables WHERE latitude BETWEEN lat1 AND lat2 AND longitude BETWEEN lon1 AND lon2;"

I do found a similar solution, but this is not what I prefer. How to check if coordinate inside certain area Python

KPB98115
  • 7
  • 2
  • 1
    *"I think there has a range of latitude and longitude"*: No, such a range never describes a circular area. Your SQL statement will check for an almost rectangular area, no matter what the function `someFunction` returns. – trincot Jun 06 '22 at 18:43

1 Answers1

0

The first issue in your program is that you're checking for presence in a rectangle with lat BETWEEN lat1 and lat2 and not in a circle, which is (ori_x - x)^2 + (ori_y - y)^2 <= r.

Then, the check you are talking about is completely doable in SQL (for instance SQLite3 compiled with and without SQLITE_ENABLE_MATH_FUNCTIONS option).

Firstly, create a table and load some coordinates.

import sqlite3

con = sqlite3.connect(":memory:")

cur = con.cursor()
cur.execute(
    "CREATE TABLE coordinates ("
    "latitude REAL NOT NULL, "
    "longitude REAL NOT NULL)"
    )
cur.executemany(
    "INSERT INTO coordinates VALUES (?, ?)",
    [(0, 0), (3, 1), (0, 5), (-2, -2), (5, 5)],
    )
cur.close()
con.commit()

Set the target and circle size.

lat = 0
lon = 0
r = 5

Then, we fetch the coordinates with a CTE (no need for SQLITE_ENABLE_MATH_FUNCTIONS).

cur = con.cursor()

cur.execute(
    "WITH d (lat, lon, dlat, dlon) AS ("
    "  SELECT c.latitude, c.longitude, c.latitude - ?, c.longitude - ?"
    "  FROM coordinates AS c)"
    "SELECT d.lat, d.lon FROM d "
    "WHERE (d.dlat * d.dlat + d.dlon * d.dlon) <= ?",
    (lat, lon, r ** 2),
    )

print(cur.fetchall())

or with the POW() math function (if SQLite was compiled with SQLITE_ENABLE_MATH_FUNCTIONS).

cur = con.cursor()

cur.execute(
    "SELECT c.latitude, c.longitude "
    "FROM coordinates AS c"
    "WHERE (POW(c.latitude - ?, 2) + POW(c.longitude - ?, 2) <= ?",
    (lat, lon, r ** 2),
    )

print(cur.fetchall())
ljmc
  • 4,830
  • 2
  • 7
  • 26
  • Seems like the SQL can do the calculation, so can I apply the same logic in the SQL statement? (cause I am using SQL database) Furthermore, can I apply the haversine formula to replace the X^2+Y^=R^2 formula? I would like to get accurate coordination ASAP. – KPB98115 Jun 07 '22 at 05:52
  • You did ask for two points in a circle, so that’s what I gave you… But if you need distance on a sphere, then yes you can swap the calculation for the haversine formula. How you’ll do that depends on the math functions in your SQL dialect (SQLite, PostgreSQL, t-SQL, etc). – ljmc Jun 07 '22 at 06:41