check whether given latitude and longitude is in a set of lat/lon in sqlite

In my iphone app, I have a sqlite table with latitudes and longitudes of USA(All weather stations of USA) . What is the sqlite query to check whether given latitude and longitude is in a set of lat/lon in sqlite?

I mean I have the lat/lon set of New York as (42.75,73.80),(37,-122) but am searching with a lat/lon which is near New York, like (42.10,73.20)

How to find if this (42.10,73.20) is near New York?

• Answer for mtoepper

Custom Functions in SQLITE (Eg.ACOS)

//Include this code in your project
static void ACOSFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    assert(argc == 1);
    if (sqlite3_value_type(argv[0]) == SQLITE_NULL) {
        sqlite3_result_null(context);
        return;
    }
    double input = sqlite3_value_double(arg开发者_开发百科v[0]);
    sqlite3_result_double(context, acos(input)  );
}

And add this after creating database connection.

sqlite3_create_function(db.sqliteHandle, "ACOS", 1, SQLITE_UTF8, NULL, &ACOSFunc, NULL, NULL);

---

I have not used this before but try below query:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

This query is working fine in SQL so just try this.. Hope it will work in SQLite.

Cheers, Pragnesh

---

For performance, you may want to consider also storing lat/long pairs that represent a bounding box around each real lat/long point, that have the point of interest in the center.

Then you are just doing a simple check to see if the lat/long of interest is inside a box.

After you find a hit you could do a more complex circular calculation to see if it's really "near", instead of that approximation. The pairs can be calculated to whatever rough distance you consider "near".

---

Great Pragnesh. Its working very well. It seems you have a good hand on iphone issues.

---

Assuming your table looks a bit like this:

stations:
id      | name     | lat     | lng
1617760 | Hilo, HI | 19.7303 | -155.0558

the query would be

SELECT COUNT(*) FROM stations WHERE lat=? AND lng=?

(where the "?" are parameters)

If there's no match, you will get a 0, else a 1 (assuming you don't have multiple stations at one point ;))