Sort by distance on multiple zipcode PHP & MySql

Question

Hi Friends I need help on this logic
Problem Definition:

There are 4 tables:

1. Project
   • Project Name
   • Project Id
   • Zipcode
2. Vendor
   • Vendor Id
   • Vendor Name
   • Phone
   • Service area distance
3. Vendor Service Areas
   • Vendor Id
   • Service area zipcode
4. Zipcode Details
   • zipcode
   • latitude
   • longitude

• Every project have one zipcode
• A vendor can have multiple service area zipcodes

1. When a vendor login, I am getting all the projects within vendor's service area.

   Ex: If vendor has two zipcode in his service area 22032, 10031 and his service area distance is 20 Miles then all the project within these service area around 20 Miles.

2. I am calculating distance between project zipcode and vendor zipcodes and showing Minimum distance in a column

   Ex: project zipcode 22032 and vendor zipcodes are 22031 and 22040:
   22032 to 22031 = 3.01 miles
   22032 to 22040 = 7.98 miles
   Then Distance = 3.01

Problem: Now client want to SORT BY DISTANCE

My Try:

1. I tried to do this with PHP arrays and object but with large data It is slow down the process
2. I tried to calculate it from MYSQL distance calculation haversine formula, but not able to figure out
3. I tried to map the distance in seperate table but there are too many zipcodes

Please suggest any logic which can take less time.

Answer 1

You could try a Nearest neighbor search

Take a look here and at this related SO question

Answer 2

I suggest to define distance formula in your mysql using some function like this:

CREATE DEFINER=`root`@`localhost` FUNCTION `calc_distance`(`p1lat` DECIMAL(10,5), `p1long` DECIMAL(10,5), `p2lat` DECIMAL(10,5), `p2long` DECIMAL(10,5)) RETURNS decimal(10,5)
    NO SQL
BEGIN
    DECLARE radius INT;
    DECLARE deg_per_rad DECIMAL;
    SET radius = 3958;
    SET deg_per_rad = 57.29578;
    RETURN      (radius * PI() * SQRT(
                (p1lat-p2lat)
                * (p1lat - p2lat)
                + cos(p1lat / deg_per_rad)
                * cos(p2lat / deg_per_rad)
                * (p1long - p2long)
                * (p1long - p2long)
                )/180);

END

Then you can call this function to calculate and order by distance in your custom column.

reference

Answer 3

I had the same problem too much selecting the whole table to just figure which one's are closer, i answered similar question here before.

1. First you will need to cut 95% of useless rows that are way too far by my approach here. How to efficiently find the closest locations nearby a given location

2. Collect all zipcodes that are within your distance in array and do another query of that array this time with all the info you need, calculate the distance of each to the target and sort it by ASC.