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

Question

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(argv[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);

Answer 1

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

Answer 2

Hi Sijo,

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

Answer 3

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".

Answer 4

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

Answer 5

Sijo,

could you elaborate on how to extend sqlite with userdefined functions? That'd be great, I'm not getting into it.

Thanx and happy coding! Marcus