0

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.

Naresh
  • 2,761
  • 10
  • 45
  • 78
  • Can you please be more specific about the result set you require? What controls the number of rows? What should each row contain? Exactly what parameters will you present to the query? – O. Jones May 12 '14 at 18:48

1 Answers1

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

Simón Urzúa
  • 1,556
  • 2
  • 11
  • 16