高德地图经纬度距离数据库排序，Spring Data JPA，Kotlin 实现版本

1.数据库函数定义

//继承默认的数据库Dialect，注册自定义sql函数
class CustomMysql8Dialect : MySQL8Dialect() {
    init {
        val factor = "?1"
        val latitude1 = "?2"
        val longitude1 = "?3"
        val latitude2 = "?4"
        val longitude2 = "?5"

        //高德地图距离计算
        registerFunction("dis", SQLFunctionTemplate(DoubleType.INSTANCE,
            """
                asin(sqrt(
            pow(
                cos($latitude1 * $factor) * cos($longitude1 * $factor)-
                cos($latitude2 * $factor) * cos($longitude2 * $factor),2
            ) +
                pow(
                    cos($latitude1 * $factor) * sin($longitude1 * $factor)-
                    cos($latitude2 * $factor) * sin($longitude2 * $factor),2
                ) +
                pow(
                    sin($latitude1 * $factor)-
                    sin($latitude2 * $factor),2
                ))
            / 2.0) * 1.27420015798544E7
            """.trimIndent()))
    }
}


//高德地图经纬度类
class LatLng(latitude: Double, longitude: Double) {
    private val latitude: Double
    private val longitude: Double

    init {
        this.longitude = if (-180.0 <= longitude && longitude < 180.0) formatDouble(longitude)
        else formatDouble(((longitude - 180.0) % 360.0 + 360.0) % 360.0 - 180.0)
        this.latitude = formatDouble(max(-90.0, min(90.0, latitude)))
    }

    companion object {
        private var decimalFormat: DecimalFormat = DecimalFormat("0.000000", DecimalFormatSymbols(Locale.US))
        private fun formatDouble(double: Double): Double = java.lang.Double.parseDouble(decimalFormat.format(double))
    }

    //高德地图距离计算
    fun calculateLineDistance(other: LatLng): Double {
        val longitude1 = this.longitude
        val latitude1 = this.latitude
        val longitude2 = other.longitude
        val latitude2 = other.latitude
        val factor = 0.01745329251994329
        return asin(sqrt(
            pow(
                cos(latitude1 * factor) * cos(longitude1 * factor) -
                    cos(latitude2 * factor) * cos(longitude2 * factor), 2.0
            ) +
                pow(
                    cos(latitude1 * factor) * sin(longitude1 * factor) -
                        cos(latitude2 * factor) * sin(longitude2 * factor), 2.0
                ) +
                pow(
                    sin(latitude1 * factor) -
                        sin(latitude2 * factor), 2.0
                ))
            / 2.0) * 1.27420015798544E7
    }
}

2.配置JPA使用自定义Dialect

spring.jpa.properties.hibernate.dialect=包名.CustomMysql8Dialect

3.项目中使用案例

val order = mutableListOf<Order>()
if (latitude != null && longitude != null) {
    val function = cb.function("dis", Double::class.java,
        cb.literal(0.01745329251994329),
        //前端传来的经纬度
        cb.literal(latitude),
        cb.literal(longitude),
        //数据库的经纬度字段
        shopInfo[ShopInfo_.latitude],
        shopInfo[ShopInfo_.longitude]
    )
    order.add(when (distanceSort) {
        "desc" -> cb.desc(function)
        "asc" -> cb.asc(function)
        else -> cb.asc(function)
    })
}
query
    .orderBy(*order.toTypedArray())
    .groupBy(root[Merchant_.shop][Shop_.id])
    .where(*restrictions.toTypedArray())
    .restriction