软件开发架构师

mysql 新特性之geometry

数据库 25 2019-02-26 04:04

1、获取矩形两个点的数据(左上角和右下角)

SELECT  *
    FROM    t_location
    WHERE   MBRContains
                    (
                    LineString(
                            Point (22.169,114.019 ),
                            Point ( 23.669, 114.939 )
                            ), Location
                    )

2、获取周围10公里的数据,并按距离排序

SELECT  
    address,X(address_loc),Y(address_loc),  
(2 * 6378.137 * ASIN(SQRT(POW(SIN(PI() * (:lat - X(p.shop_point)) / 360),2) + COS(PI() * :lat / 180) * COS(X(p.shop_point) * PI() / 180) * POW(SIN(PI() * (:lng - Y(p.shop_point)) / 360),2)))) as distance  
FROM  
    address  
WHERE  
    MBRContains (  
        LineString (  
            Point (  
                30.620076 + 10 / (  
                    111.1 / COS(RADIANS(104.067221))  
                ),  
                104.067221 + 10 / 111.1  
            ),  
            Point (  
                30.620076 - 10 / (  
                    111.1 / COS(RADIANS(104.067221))  
                ),  
                104.067221 - 10 / 111.1  
            )  
        ),  
        address_loc  
    )  
ORDER BY distance;



3、详细查看
http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/

原文:https://blog.csdn.net/u012274656/article/details/78529160

文章评论