https://kotlinlang.org logo
#exposed
Title
# exposed
d

DownloadPizza

03/19/2020, 4:42 PM
I need some help with a project. I have a table like this
Copy code
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?
e

Emil Kantis

03/19/2020, 5:53 PM
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 🙂
d

DownloadPizza

03/19/2020, 7:24 PM
thanks for the hints, im using mysql so ill liik into that
e

Emil Kantis

03/19/2020, 7:36 PM
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 🙂
running that
ORDER BY
would run into issues when querying around the international date line, and around the north and south poles though 😁
d

DownloadPizza

03/19/2020, 8:48 PM
I have a working formula, the native SQL stuff is exactly what I needed tho. Thank you very much
t

than_

03/20/2020, 10:05 AM
have you explored geohashing option?