I need some help with a project. I have a table li...

# exposed

[DownloadPizza]

I need some help with a project. I have a table like this

object Parks : Table("p_parks") {
    val id = varchar("p_id", 45)
    val longitude = double("longitude")
    val latitude = double("latitude")

    override val primaryKey = PrimaryKey(id)
}

and im trying to select them by distance to a coordinate c with latitude lat and longitude lon , but i dont want to filter them by smth like "less than 10km away" but i want the 10 nearest. Is there any way to do this without selecting all?

[Emil Kantis]

I'd look into using GIS datatypes in a db that supports it, it should enable you to write queries like that. IIRC MySQL supports GIS out of the box and postgres has extensions for it. I'm not sure how to interface with it using exposed, if it's at all possible even 🙂

[DownloadPizza]

thanks for the hints, im using mysql so ill liik into that

[Emil Kantis]

I guess if all you wanna do is order by distance to a point then you can simply use maths.. like, ORDER BY sqrt(pow(longitude - X, 2) + pow(latitude - Y, 2)) ASC where X and Y should be the point you want to be the center of your query? There's some info here on running native SQL: https://github.com/JetBrains/Exposed/wiki/FAQ . might be useful 🙂

[Emil Kantis]

running that ORDER BY would run into issues when querying around the international date line, and around the north and south poles though 😁

[DownloadPizza]

I have a working formula, the native SQL stuff is exactly what I needed tho. Thank you very much

[than_]

have you explored geohashing option?