当前经纬度:#{startLatitude},#{startLongitude}
半径范围:#{radius}
地球半径:111195
单位:米
st_distance 计算的结果单位是度,需要乘111195(地球半径6371000*PI/180)是将值转化为米。
SELECT
t.name,
t.start_longitude,
t.start_latitude,
( st_distance ( point ( t.start_longitude, t.start_latitude ), point ( #{startLatitude},#{startLongitude} ) ) * 111195 ) AS distance
FROM
kws_trip t
HAVING
distance < #{radius}
ORDER BY
distance
<select id="selectTripListByKeywords" parameterType="com.1111.test.app.1111.pojo.knt.request.SearchTripListRequest" resultType="com.1111.test.app.1111.domain.Trip">
SELECT
trip.app_user_account_uid,
trip.app_username,
trip.device_uid,
trip.device_name,
trip.name,
trip.start_latitude,
trip.start_longitude,
trip.start_address,
trip.start_time,
trip.end_latitude,
trip.end_longitude,
trip.end_address,
trip.end_time,
trip.avg_speed,
trip.total_time,
trip.total_distance,
trip.score,
trip.is_favorite,
trip.images,
trip.is_from_share,
trip.from_share_app_user_account_uid,
trip.from_share_time,
trip.uid,
trip.create_by,
trip.create_time,
trip.visible_type,
trip.like_count,
trip.trim_image_url,
trip.suface_type,
trip.weather,
trip.keywords,
trip.trip_start_idx,
trip.trip_end_idx,
trip.first_name,
trip.last_name,
trip.avatar,
trip.model_name,
trip.device_photo,
trip.is_local
<if test="radius != null">
, ( st_distance ( point ( trip.start_longitude, trip.start_latitude ), point ( #{startLongitude},#{startLatitude} ) ) * 111195 ) AS distance
</if>
from
kws_trip trip
where
trip.is_deleted=0 and trip.visible_type != 2
<if test="keywords != null and keywords != ''">
and (
trip.keywords like CONCAT('%',#{keywords},'%')
or trip.name like CONCAT('%',#{keywords},'%')
or trip.start_address like CONCAT('%',#{keywords},'%')
or trip.end_address like CONCAT('%',#{keywords},'%')
)
</if>
<if test="productUid != null and productUid != ''">
and trip.product_uid = #{productUid}
</if>
<if test="startDate != null and endDate != null">
<![CDATA[ and trip.start_time >= #{startDate} and trip.end_time < #{endDate} ]]>
</if>
<if test="radius != null">
HAVING
<![CDATA[ distance < #{radius} ]]>
ORDER BY
distance
</if>
<if test="radius == null">
order by trip.create_time desc
</if>
</select>