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 ;))