mysql 根据经纬度查询范围点

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

mybatis写法 

  
    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(
            (
            #{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
        
        from pt_store
        
             and store_no = #{storeNo}
             and store_name like concat('%', #{storeName}, '%')
             and open_id = #{openId}
             and store_image = #{storeImage}
             and store_img_url = #{storeImgUrl}
             and province = #{province}
             and city = #{city}
             and district = #{district}
             and county = #{county}
             and Community = #{community}
             and address = #{address}
             and id_no = #{idNo}
             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
                )<=3
             
             and store_category_id = #{storeCategoryId}
             and tel = #{tel}
             and phone = #{phone}
             and busno = #{busno}
             and store_status = #{storeStatus}
             and synopsis = #{synopsis}
             and store_type = #{storeType}
             and ranks = #{ranks}
        
        order by ranks desc
    

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公里数