合并两个MySQL查询并获得两个邮政编码之间的距离

Question

i want to get distance between two zip codes.

HTML form have a field i.e ZIP CODE, Miles(distance).\

DB structure with 7000 records.

id | name | zip_code | lat | lng | status|

MySQL query

   $query =  "SELECT *, 3963 * acos(cos(radians(90-lat ))*cos(radians(90-'".$result['lat']."'))".
              "+sin(radians(90-lat ))* sin(radians(90-'".$result['lat']."'))".
              "*cos(radians(lng- '".$result['lng']."'))) AS distance FROM table_name".
              " WHERE memberLevel='basic' HAVING (distance < '".$miles."') 
                ORDER BY distance ASC";

I don't know what is the right MySQL query to compare distance with input and stored latitude,longitude.

Answer 1

You can use the spacial functions and spacial type columns http://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html

Here it's a great example to use spacial functions! http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

Or you can define a custom function

CREATE DEFINER=`test`@`%` FUNCTION `geoDistance`(`lon1` DOUBLE, `lat1` DOUBLE, `lon2` DOUBLE, `lat2` DOUBLE) RETURNS double LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE v DOUBLE; SELECT cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)) INTO v; RETURN IF(v > 1, 0, 6371000 * acos(v)); END

then call

SELECT geoDistance(X(point1), Y(point1), X(spoint2), Y(point2)) result comes in meters