# exposed

Sergey Akhapkin

03/23/2020, 12:07 AM
@DownloadPizza if it works for you, here is how to calculate distance for a row using 'haversine' formula (exposed 0.21.1 / PostgreSQL):
Copy code
object LocationTable: IntIdTable("LOCATION") {
    val latitude = double("latitude")
    val longitude = double("longitude")

    private val doubleT by lazyOf(DoubleColumnType())

    fun Expression<Double>.limitToOne() = CustomFunction<Double>("least", doubleT, this, 1.0.lit())
    fun Expression<Double>.radians() = CustomFunction<Double>("radians", doubleT, this)
    fun Expression<Double>.sin() = CustomFunction<Double>("sin", doubleT, this)
    fun Expression<Double>.cos() = CustomFunction<Double>("cos", doubleT, this)
    fun Expression<Double>.acos() = CustomFunction<Double>("acos", doubleT, this)

    fun Double.lit() = LiteralOp(doubleT, this)
    infix fun Expression<Double>.times(v: Expression<Double>) = TimesOp(this, v, doubleT)
    infix fun Expression<Double>.times(v: Double) = TimesOp(this, v.lit(), doubleT)
    infix fun Expression<Double>.plus(v: Expression<Double>) = PlusOp(this, v, doubleT)
    infix fun Column<Double>.minus(v: Double) = BracketOp(MinusOp(this, LiteralOp(doubleT, v), doubleT))

    fun distance(coords: Coordinates) =
        (latitude.radians().sin() times Math.sin(Math.toRadians(coords.latitude)) plus
         latitude.radians().cos() times Math.cos(Math.toRadians(coords.latitude)) times (longitude minus coords.longitude).radians().cos()).limitToOne().acos() times Coordinates.TO_METRIC
Copy code
data class Coordinates(val latitude: Double, val longitude: Double) {
    companion object {
        const val TO_METRIC = 1852.0 * 60.0 * 180.0 / Math.PI    
and this function can be used as following:
Copy code
query.andWhere { LocationTable.distance(basePoint) lessEq distanceInMeters }
DISCLAIMER: 1. I'm not sure that's 100% right (or idiomatic) way to do that with the exposed (still newcomer for the exposed) 2. I'm pretty sure that's kind of filtering 'closest/nearest' objects is very inefficient (indexes cannot be used), so let consider to use postgis extension or optimizing search in someway e.g. with bounding boxes.


03/24/2020, 4:29 PM
I found my own way. 1. I use mysql, 2. As said I need to sort and then limit by number of returns, not distance, still thanks