Get all locations within certain radius from SQLite Database in XCode

Question

I have a SQLite Database which contains latitude and longitude of several locations.

I want to find the latitude and longitude of all locations which fall within 15km radius from my current location from my SQLite Database.

What will be my SQLite Query for this?

Pradeep Singh · Answer 1 · 2015-06-20T08:14:51.027

2

One degree difference of latitude is equal to 111 km distance, and One degree difference of longitude is equal to 94 km. So Now check for all the latitude which are at (+/-)(15/111) difference and longitudes which are (+/-)(15/94) difference from your current location. Important link for reference.

edited Jun 20 '15 at 08:14

answered Jun 20 '15 at 08:08

Pradeep Singh

752
1
10
23

.. what he said (basically, look for all locations in a square centered at the location you are interested in. the square has to enclose the circle with the radius you are interested in. when you get the results from the DB, you run then filter out the ones that are inside the square, but not inside the circle (diffLong^2 + diffLat^2 > radius^2). – hoijui Jun 20 '15 at 08:13
@Pradeep I am getting all locations latitude and longitude which fall within 15km radius. But how can i arrange all locations in ascending order.? – Programming Learner Jun 20 '15 at 08:34
Apologies but I didn't do the sorting of the coordinates but I think this might help you http://stackoverflow.com/questions/12456153/how-to-sort-set-of-coordinates-in-ascending-order-by-distance-from-point-x1-y1 Its in C++ but it might help you to achieve what you need. – Pradeep Singh Jun 20 '15 at 08:41

Mohamed Salah · Answer 2 · 2018-02-20T07:56:15.230

For anyone still looking for an answer:

You can get nearby locations with this method adapted from @breceivemail's answer [JAVA]

Here is the translated version in Swift:

private func calculateDerivedPosition(point: CGPoint, range: Double, bearing: Double)-> CGPoint {
    let EarthRadius: Double = 6371000

    let latA = Double(point.x.degreesToRadians)
    let lonA = Double(point.y.degreesToRadians)
    let angularDistance = range / EarthRadius
    let trueCourse = bearing.degreesToRadians

    var lat = asin(sin(latA) * cos(angularDistance) +
                   cos(latA) * sin(angularDistance) *
                   cos(trueCourse))

    let dlon = atan2(sin(trueCourse) * sin(angularDistance) * cos(latA),
                     cos(angularDistance) - sin(latA) * sin(lat))

    var lon = ((lonA + dlon + Double.pi).truncatingRemainder(dividingBy: (Double.pi * 2))) - Double.pi

    lat = lat.radiansToDegrees
    lon = lon.radiansToDegrees

    let newPoint = CGPoint(x: lat, y: lon)

    return newPoint
}

radiansToDegree Extension

And You can use it like this with FMDB (or any Sqlite lib):

    let center = CGPoint(x: currentLocation.latitude, y: currentLocation.longitude)
    let mult: Double = 1 // mult = 1.1 is more reliable
    let radius: Double = 5000 //in meters

    let point1 = calculateDerivedPosition(point: center, range: radius * mult, bearing: 0)
    let point2 = calculateDerivedPosition(point: center, range: radius * mult, bearing: 90)
    let point3 = calculateDerivedPosition(point: center, range: radius * mult, bearing: 180)
    let point4 = calculateDerivedPosition(point: center, range: radius * mult, bearing: 270)

    let result = try!db.executeQuery("SELECT * FROM items WHERE lat > ? AND lat < ? AND lng < ? AND lng > ?" , values: [point3.x, point1.x, point2.y, point4.y])

Note: The results aren't sorted.

score 1 · Answer 3 · answered Jun 29 '15 at 05:35

To arrange them in Ascending order, store all the distcnace in a dictionary with key. like,

 distanceDic = [[NSMutableDictionary alloc]initWithObjectsAndKeys:[NSNumber numberWithFloat:distanceVal],@"distance",nil];

 [distanceArray addObject:distanceDic];

Here, distanceDic is my Dictionary and distaceVal is string containing distance. distanceArray is NSMutableArray Containing Distance. Now Sort the distanceDic like,

NSArray *array = [NSArray arrayWithArray:[distanceArray mutableCopy]]; // Copying NSMutableArray to NSArray.
NSSortDescriptor *sortDescriptor;

sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"distance" ascending:YES];

NSArray *sortDescriptors = [NSArray arrayWithObject:sortDescriptor];

array = [array sortedArrayUsingDescriptors:sortDescriptors];

NSLog(@"#The Final Distance Array After Sorting :%@",array);

This will give you resulting Distance Array sorted in Ascending order.

Get all locations within certain radius from SQLite Database in XCode

3 Answers3

For anyone still looking for an answer: