mysql 根据经纬度查询范围点

一.根据经纬度查询一定范围内所有点

mybatis写法 

  <select  parameterType="PtStore" resultMap="PtStoreResult">
    select
        id,
        store_no,
        store_name,
        open_id,
        store_image,
        store_img_url,
        province,
        city,
        district,
        county,
        Community,
        address,
        id_no,
        longitude,
        latitude,
        store_category_id,
        tel,
        phone,
        busno,
        store_status,
        synopsis,
        store_type,
        ranks,
        create_time,
        update_time
        <if test="longitude != null  and longitude != ''  and latitude != null  and latitude != ''">
            ,ROUND(
            6378.138 * 2 * ASIN(
            SQRT(
            POW(
            SIN(
            (
            #{latitude} * PI() / 180 - latitude * PI() / 180
            ) / 2
            ),
            2
            ) + COS( #{latitude} * PI() / 180) * COS(latitude * PI() / 180) * POW(
            SIN(
            (
            #{longitude}  * PI() / 180 - longitude * PI() / 180
            ) / 2
            ),
            2
            )
            )
            ) * 1000
            ) AS m
        </if>
        from pt_store
        <where>
            <if test="storeNo != null  and storeNo != ''"> and store_no = #{storeNo}</if>
            <if test="storeName != null  and storeName != ''"> and store_name like concat('%', #{storeName}, '%')</if>
            <if test="openId != null  and openId != ''"> and open_id = #{openId}</if>
            <if test="storeImage != null  and storeImage != ''"> and store_image = #{storeImage}</if>
            <if test="storeImgUrl != null  and storeImgUrl != ''"> and store_img_url = #{storeImgUrl}</if>
            <if test="province != null  and province != ''"> and province = #{province}</if>
            <if test="city != null  and city != ''"> and city = #{city}</if>
            <if test="district != null  and district != ''"> and district = #{district}</if>
            <if test="county != null  and county != ''"> and county = #{county}</if>
            <if test="community != null  and community != ''"> and Community = #{community}</if>
            <if test="address != null  and address != ''"> and address = #{address}</if>
            <if test="idNo != null  and idNo != ''"> and id_no = #{idNo}</if>
            <if test="longitude != null  and longitude != ''  and latitude != null  and latitude != ''"> and
                (
                acos(
                sin(( #{latitude} * 3.1415 )/ 180 ) * sin(( latitude * 3.1415 )/ 180 ) + cos(( #{latitude} * 3.1415 )/ 180 ) * cos(( latitude * 3.1415 )/ 180 ) * cos(( #{longitude} * 3.1415 )/ 180 - ( longitude * 3.1415 )/ 180 )
                )* 6370.996
                )&lt;=3
             </if>
            <if test="storeCategoryId != null "> and store_category_id = #{storeCategoryId}</if>
            <if test="tel != null  and tel != ''"> and tel = #{tel}</if>
            <if test="phone != null  and phone != ''"> and phone = #{phone}</if>
            <if test="busno != null "> and busno = #{busno}</if>
            <if test="storeStatus != null "> and store_status = #{storeStatus}</if>
            <if test="synopsis != null  and synopsis != ''"> and synopsis = #{synopsis}</if>
            <if test="storeType != null "> and store_type = #{storeType}</if>
            <if test="ranks != null "> and ranks = #{ranks}</if>
        </where>
        order by ranks desc
    </select>

sql 

SELECT
	id,
	store_no,
	store_name,
	open_id,
	store_image,
	store_img_url,
	province,
	city,
	district,
	county,
	Community,
	address,
	id_no,
	longitude,
	latitude,
	store_category_id,
	tel,
	phone,
	busno,
	store_status,
	synopsis,
	store_type,
	ranks,
	create_time,
	update_time,
	ROUND(
		6378.138 * 2 * ASIN(
			SQRT(
				POW( SIN( ( 46.577759 * PI() / 180 - latitude * PI() / 180 ) / 2 ), 2 ) + COS( 46.577759 * PI() / 180 ) * COS( latitude * PI() / 180 ) * POW( SIN( ( 125.166985 * PI() / 180 - longitude * PI() / 180 ) / 2 ), 2 ) 
			) 
		) * 1000 
	) AS m from pt_store 
WHERE
	(
		acos(
			sin(( 46.577759 * 3.1415 )/ 180 ) * sin(( latitude * 3.1415 )/ 180 ) + cos(( 46.577759 * 3.1415 )/ 180 ) * cos(( latitude * 3.1415 )/ 180 ) * cos(( 125.166985 * 3.1415 )/ 180 - ( longitude * 3.1415 )/ 180 ) 
		)* 6370.996 
	)<= 3 
	
	limit 0, 1

二 取范围内最近距离

SELECT
	`id`,
	`province`,
	`city`,
	`district`,
	`county`,
	`Community`,
	`ranks`,
	`address`,
	`longitude`,
	`latitude`,
	`switchs`,
	`create_time`,
	`update_time`,(
		6371 * acos (
			cos ( radians( 125.161258 ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( 46.571054 ) ) + sin ( radians( 125.161258 ) ) * sin( radians( latitude ) ) 
		) 
	) AS distance 
FROM
	js_estate 
HAVING
	1.6 > distance 
ORDER BY
	distance 
	LIMIT 0,
	1;

1.6公里数