根据地图经纬度获取打卡范围-向日葵屋

根据地图经纬度获取打卡范围

小鱼儿 2023-07-20 12:25 17阅读 0赞

如：现在有数据表

school学校表：schoolId学校id，schoolName学校名称，is_delete删除标志；

school_location学校位置表：id自增长id，school_id学校id，latitude(decimal)纬度，longitude(decimal)经度，description具体位置描述；

现需要根据用户当前位置（经度longitude、纬度latitude）获取1000米内可以打卡的学校列表：

SELECT
    *
FROM
    (
        SELECT
            s.school_id AS schoolId,
            s.school_name AS schoolName,
            l.id AS locationId,
            l.longitude AS longitude,
            l.latitude AS latitude,
            l.description AS description,
            6378137 * 2 * ASIN(
                SQRT(
                    POWER(
                        SIN(
                            (
                                #{latitude} * PI() / 180 - l.latitude * PI() / 180
                            ) / 2
                        ),
                        2
                    ) + COS(
                        #{latitude} * PI() / 180) * COS(l.latitude * PI() / 180) * POWER(
                        SIN(
                            (
                                #{longitude} * PI() / 180 - l.longitude * PI() / 180
                            ) / 2
                        ),
                        2
                    )
                )
            ) AS distance
        FROM
            school s
        INNER JOIN school_location l ON s.school_id = l.school_org_id
        WHERE
            s.is_delete = 0
    ) m
WHERE
    m.distance<1000