Sort based on distance using latitude and longitude

[sameer4]

What did you do?

I need to sort table's data based on distance but i have latitude and longitude in table. i looked for answers on internet.and they were with query or sql stored functions.

What did you expect to happen?

Some way to use SQLSpecificExpressible or SQLExpressible for distance sorting without need to write manual string query?

by the way, is there some way to combine objects (SQLSpecificExpressible or SQLExpressible) with string query?? and can you make query generation function public if possible? i want to stick with objects (SQLSpecificExpressible or SQLExpressible) but in some cases i need to write string query like a case mentioned above, so i was thinking about manipulating query after objects (SQLSpecificExpressible or SQLExpressible) handleable part instead of going full string query mode

What happened instead?

Environment

GRDB flavor(s): (GRDB, SQLCipher, Custom SQLite build?) GRDB GRDB version: v4.1.1 Installation method: (CocoaPods, SPM, manual?) CocoaPods Xcode version: 10.3 Swift version: 5.0 Platform(s) running GRDB: (iOS, macOS, watchOS?) iOS macOS version running Xcode: 10.14.6

Demo Project

[groue]

Hello @sameer4,

Do you plan to use R*Tree, the SQLite geolocation extension, or do you want to provide your own distance computation?

In the context of the previous question, what do you lack, specifically, from SQLSpecificExpressible and SQLExpressible? What kind of code do you want to write, that GRDB does not let you express today?

[sameer4]

@groue i just need to sort records based on distance from user location and R*Tree looks like a filter.

1st the distance case mentioned above. 2nd i need to sort data based on titles from other tables.i know i should use Associations but i was not able to implement it the last time when i was working on it due to time constrain 😥so i wrote a workaround to save data and get related entries together. Now i'm stuck on sorting and there will be no work around i think except manually appending query with objects (SQLSpecificExpressible and SQLExpressible).or you can give me a help with "Associations". I'm giving it an other try.

[groue]

@sameer4, OK you don't use RTree. That's our first hard fact. Now, unless you describe your issue in a much more specific way, I can't help you. Please focus on your problem, and provide more hard facts. Don't assume I know anything about your needs, because I do not.

Let me repeat myself:

what do you lack, specifically, from SQLSpecificExpressible and SQLExpressible? What kind of code do you want to write, that GRDB does not let you express today?

[sameer4]

Ok lets discuss things one by one

consider this data from image

i need to sort this data based on distance from user.

i have a class ProtocolAndExtensions which includes helping functions like

static func findAll(withFilter predicate: SQLSpecificExpressible, offSet:Int? = nil, inLimit:Int? = nil, sorted:SQLOrderingTerm...) -> [Self] {

    var objects:[Self] = []

    if let database = getDataBase() {
        do {
            objects = try database.read { (db) in
                var localFilter = filter(predicate)
                if let inLimit = inLimit {
                    localFilter = localFilter.limit(inLimit, offset: offSet)
                }
                if let sort = sorted {
                    localFilter = localFilter.order(sort)
                }
                return try localFilter.fetchAll(db)
            }
        } catch (let error) {
            DLog("Sam \(#function) failed = \(error.localizedDescription)")
        }
    }

    objects.forEach { $0.fillComplexObjects() } // workAround to fill associated objects
    return objects
}

for Record classes.and i can do

TodoTasks.findAll(withFilter: TodoTasks.CodingKeys.title.like(search), sorted:TodoTasks.CodingKeys.title.asc)

now i need to sort this based on distance. how can i do this usign GRDB? then i will modify my function accordingly

and here's CodingKeys

enum CodingKeys: String, CodingKey, ColumnExpression { case id = "id" case title = "title" case longitude = "longitude" case latitude = "latitude" }

[groue]

Thanks for narrowing your trouble. The remaining question is:

now i need to sort this based on distance. how can i do this usign GRDB?

I'm no expert in databases of spatial coordinates: so please become that expert in this discussion. Pretend you forget about GRDB: what SQLite technique do you want to use? What should the executed SQL look like? I'll be able to help you have GRDB produce this SQL.

[groue]

so please become that expert in this discussion

I insist. If you don't know how to perform the ordering you want in SQL, then ask the question, for example, on StackOverflow. Frame your question so that it does not mention GRDB, because it actually is a question about SQLite.

When you know the SQL you need, come back so that we can translate this into Swift code based on GRDB.

[sobri909]

It sounds like this is a question of "how can I do this with SQLite" rather than "how can I do this with GRDB". To which the answer is: R*Tree indexes.

[sameer4]

We can follow https://gist.github.com/statickidz/8a2f0ce3bca9badbf34970b958ef8479

and query is

SELECT id, title, ( 6371 * acos ( cos ( radians(30.193733) )

• cos( radians( latitude ) )
• cos( radians( longitude ) - radians(71.451717) )
• sin ( radians(30.193733) )
• sin( radians( latitude ) ) ) ) AS distance FROM TodoTasks ORDER BY distance LIMIT 0 , 30;

Result

https://i.ibb.co/HY6G32T/image.png

[groue]

It sounds like this is a question of "how can I do this with SQLite" rather than "how can I do this with GRDB".

Yes, I think so as well.

To which the answer is: R*Tree indexes.

I'm not quite sure R*Tree answers the "sorting by distance" question. The doc makes no mention of distances. A quick Google search doesn't provide any hint that makes me think R*Tree can indeed sort by distance.

SpatiaLite looks like it may be a better fit. But, again, I'm no expert and I don't want to sound like I'm giving any advice. And I don't know how to build GRDB so that it uses SpatiaLite instead of the regular SQLite anyway.

We can follow https://gist.github.com/statickidz/8a2f0ce3bca9badbf34970b958ef8479

All right @sameer4.

Did you try to run this SQL query, adapted for your own database? You can run raw SQL with GRDB:

let rows = try dbQueue.read { db in
    try Row.fetchAll(db, sql: """
        SELECT ...
        """)
}
// Look at the result in order to see if they look like correct to you.
print(rows)

If you get an error that tells that some functions are not defined (cos, radians, etc.), then you will have to define the missing functions. See the Custom Functions chapter.

But this technique is somewhat complex, and unlikely to provide your app with the best performance because nothing here is optimized.

👉 Maybe your simplest solution, after all, is to perform an unordered database query, and perform the sorting in Swift.

In any way, as @sobri909 says, this is not a question about GRDB. This is a question about SQLite, or Swift, and StackOverflow is definitely the best place to ask. Remember to NOT mention GRDB in your StackOverflow question(s), because you have plenty of answers to get before you attempt at applying them to GRDB.

I'm closing this issue. Please reopen it when you have clear ideas about what you want to achieve with SQLite, and if you still have trouble using GRDB to express it.

[sameer4]

I'm not quite sure RTree answers the "sorting by distance" question. The doc makes no mention of distances. A quick Google search doesn't provide any hint that makes me think RTree can indeed sort by distance.

you mean can not in the last part?

Did you try to run this SQL query, adapted for your own database? You can run raw SQL with GRDB:

let rows = try dbQueue.read { db in
    try Row.fetchAll(db, sql: """
        SELECT ...
        """)
}
// Look at the result in order to see if they look like correct to you.
print(rows)

acos doesn't exit.query is working fine in Navicat. may be Navicat have some function defined.

If you get an error that tells that some functions are not defined (cos, radians, etc.), then you will have to define the missing functions. See the Custom Functions chapter.

But this technique is somewhat complex, and unlikely to provide your app with the best performance because nothing here is optimized.

i can just use ((lat-$user_lat)*(lat-$user_lat)) + ((lng - $user_lng)*(lng - $user_lng))

👉 Maybe your simplest solution, after all, is to perform an unordered database query, and perform the sorting in Swift.

i'm doing paginations by limiting 30 objects pr request so this solution is not possible i think

In any way, as @sobri909 says, this is not a question about GRDB. This is a question about SQLite, or Swift, and StackOverflow is definitely the best place to ask. Remember to NOT mention GRDB in your StackOverflow question(s), because you have plenty of answers to get before you attempt at applying them to GRDB.

i can just use ((lat-$user_lat)*(lat-$user_lat)) + ((lng - $user_lng)*(lng - $user_lng)) but can i do this in Expressible way?? handling Expressible and raw query depending on object will be a mess

[groue]

i can just use ((lat-$user_lat)(lat-$user_lat)) + ((lng - $user_lng)(lng - $user_lng))

Euclidian distance of (lat, lng) pairs will give you inaccurate results, but let's assume you are ready to deal with that approximation (again, such question is NOT about GRDB).

but can i do this in Expressible way??

Of course!

GRDB already knows how to perform arithmetic calculations. Just declare columns, and write your computations:

let latColumn = Column("latitude")
let longColumn = Column("longitude")
let userLat: CLLocationDegrees = ...
let userLong: CLLocationDegrees = ...

try dbQueue.read { db in
    let distance = (latColumn - userLat) * (latColumn - userLat)
        + (longColumn - userLong) * (longColumn - userLong)
    let records = try MyRecord
        .order(distance)
        .limit(30)
        .fetchAll(db)
}

That's all.

[sobri909]

@sameer4 The Gist that you link will require SQLite to read the value of every latitude and longitude in the table. So in order to sort the table, it will have to read every row. Normal SQLite indexes won't help with that. So if it's a large table, the performance will be unpleasant.

@groue That's why I think probably something like RTree would be the first step, to first* reduce the results set by limiting the results to a bounding box. Then once the results have been limited, the results in that bounding box can be sorted, perhaps with the algorithm from the Gist.

But yeah, either way, it's something to solve at the SQLite level rather than GRDB level.

[groue]

@groue That's why I think probably something like R*Tree would be the first step, to first reduce the results set by limiting the results to a bounding box. Then once the results have been limited, the results in that bounding box can be sorted, perhaps with the algorithm from the Gist.

Thanks @sobri909, that's a nice optimization technique. I'll try to remember it.

[sameer4]

i can just use ((lat-$userlat)(lat-$user_lat)) + ((lng - $userlng)(lng - $user_lng))

Euclidian distance of (lat, lng) pairs will give you inaccurate results, but let's assume you are ready to deal with that approximation (again, such question is NOT about GRDB).

i was but found a better solution. Sqlite, Distance calculations

GRDB already knows how to perform arithmetic calculations. Just declare columns, and write your computations:

let latColumn = Column("latitude")
let longColumn = Column("longitude")
let userLat: CLLocationDegrees = ...
let userLong: CLLocationDegrees = ...

try dbQueue.read { db in
    let distance = (latColumn - userLat) * (latColumn - userLat)
        + (longColumn - userLong) * (longColumn - userLong)
    let records = try MyRecord
        .order(distance)
        .limit(30)
        .fetchAll(db)
}

working. thanks

[sameer4]

@groue about the second issue

2nd i need to sort data based on titles from other tables.i know i should use Associations but i was not able to implement it the last time when i was working on it due to time constrain 😥

is there some way to define joining in expressible without defining Associations??

consider workpool and WorkPlanEntries.

i want to sort workpool entries based on WorkPlanEntries title.

[groue]

@sameer4,

The only way for GRDB to generate SQL joins is Associations. If you don't want to use associations, then you can write joins with custom SQL queries - they are very well supported by GRDB, as all raw SQL requests.

I don't know if this is the answer you look for, because I don't understand your question at all. I'm sorry but you simply don't provide enough information.

Instead of considering we are your coworkers and have plenty of time to spend with you, make the completely opposite assumption: we know nothing about your needs, nothing about your database, nothing about the way you use it, nothing about your app, nothing about you, nothing about your skills, nothing about the solutions you have already tried and failed.

On top of that, we gracefully answer your questions on our free time, when we have some. It is rude to abuse our free time.

You thus need to actively help us understand your questions if you want us to help you. Be terse, be complete, spare our time. This is the etiquette of online questions.

I beg you to train hard asking better questions: this skill will greatly help you, here and in the future. Thanks in advance.

[sameer4]

@groue hope so this is enough information? let me know if i need to add something else

I have a class TodoTasks (No foreign key defined in any table)

typealias customObject = DatabaseHandlerWithCreateFromJSON & Record

DatabaseHandlerWithCreateFromJSON is my protocol to use functions on top of GRDB

class TodoTasks : customObject {

static let team = hasOne(Teams.self, using: Teams.teamForeignKey)

var id : Int = 0
var team: Int = 0
......

enum CodingKeys: String, CodingKey, ColumnExpression {
    case id = "id"
    case team = "team"
    ......
}
}

and Teams class

class Teams: customObject {

static let teamForeignKey = ForeignKey([Teams.CodingKeys.id], to: [TodoTasks.CodingKeys.team])

static let todoTask = belongsTo(TodoTasks.self, using: teamForeignKey)

var id:Int = 0
var title:String = ""

 enum CodingKeys: String, CodingKey, ColumnExpression {
    case id = "id"
    case title = "title"
}
}

and a function from DatabaseHandlerWithCreateFromJSON

static func findAll2<Destination>(join:GRDB.HasOneAssociation<Self, Destination>? = nil, withFilter predicate: SQLSpecificExpressible, offSet:Int? = nil, inLimit:Int? = nil, sorted:[SQLOrderingTerm]? = nil) -> [Self] {

    var objects:[Self] = []

    if let database = getDataBase() {
        do {
            objects = try database.read { (db) in
                var localFilter = all()
                if let join = join {
                    localFilter = localFilter.including(optional: join)
                }
                localFilter = filter(predicate)
                if let inLimit = inLimit {
                    localFilter = localFilter.limit(inLimit, offset: offSet)
                }
                if let sort = sorted {
                    localFilter = localFilter.order(sort)
                }
                return try localFilter.fetchAll(db)
            }
        } catch (let error) {
            DLog("Sam \(#function) failed = \(error.localizedDescription)")
        }
    }
    return objects
}

and when i try to use

let request = TodoTasks.team.order(Teams.CodingKeys.title)
let data = TodoTasks.findAll2(join: request, withFilter: TodoTasks.CodingKeys.id > 0)

it's not joining or ordering based on Teams's title.at least according to query..

query from logs

sqlQuuery = SELECT * FROM "TodoTasks" WHERE "id" > 0

i need to sort TodoTasks data based on Teams.title

here's table views

and

[groue]

Hi again @sameer4,

What is the logged SQL when you remove all the layers and just run the following query?

let tasks = try TodoTasks
    .including(optional: TodoTasks.team.order(Teams.CodingKeys.title))
    .filter(TodoTasks.CodingKeys.id > 0)
    .fetchAll(db)

[sameer4]

What is the logged SQL when you remove all the layers and just run the following query?

let tasks = try TodoTasks
    .including(optional: TodoTasks.team.order(Teams.CodingKeys.title))
    .filter(TodoTasks.CodingKeys.id > 0)
    .fetchAll(db)

works fine as

SELECT "TodoTasks"., "Teams". FROM "TodoTasks" JOIN "Teams" ON "Teams"."id" = "TodoTasks"."team" WHERE "TodoTasks"."id" > 0 ORDER BY "Teams"."title" LIMIT 30 OFFSET 0

from

let tasks = try TodoTasks
                    .including(required: TodoTasks.team.order(Teams.CodingKeys.title))
                    .filter(TodoTasks.CodingKeys.id > 0)
                    .limit(30, offset: 0)
                    .fetchAll(db)

what do you think could be issue in my function?

[groue]

works fine

Good.

what do you think could be issue in my function?

Now it's up to you, @sameer4. Use debugging techniques such as Xcode breakpoints, maybe?

I provide GRDB support for free, but not private debugging sessions. I'm sure you understand. But if your company is willing to start a regular business relationship for paid consulting, we can find an agreement.

[sameer4]

Now it's up to you, @sameer4. Use debugging techniques such as Xcode breakpoints, maybe?

I provide GRDB support for free, but not private debugging sessions. I'm sure you understand. But if your company is willing to start a regular business relationship for paid consulting, we can find an agreement.

ok let me try

[sameer4]

problem solved. Thank you for your time and GRDB 🙂