April 16, 2017 mysql 0

Create a stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE FUNCTION distance (latA double, lonA double, latB double, LonB double)
    RETURNS double DETERMINISTIC
BEGIN
    SET @RlatA = radians(latA);
    SET @RlonA = radians(lonA);
    SET @RlatB = radians(latB);
    SET @RlonB = radians(LonB);
    SET @deltaLat = @RlatA - @RlatB;
    SET @deltaLon = @RlonA - @RlonB;
    SET @d = SIN(@deltaLat/2) * SIN(@deltaLat/2) +
        COS(@RlatA) * COS(@RlatB) * SIN(@deltaLon/2)*SIN(@deltaLon/2);
    RETURN 2 * ASIN(SQRT(@d)) * 6371.01;
END

query:

1
2
3
SELECT * FROM `address_coordinates`
WHERE DISTANCE(lat, lng, 51.501009, -0.141588) > 500
limit 20

Limit the number of rows by limiting the proximity range directly with php first:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
     function findLatBoundary($dist, $lat, &$lat1, &$lat2)
     {
        $d = ($dist / 6371.01 * 2 * M_PI) * 360;
        $lat1 = $lat - $d;
        $lat2 = $lat + $d;
     }
     
     
     function findLonBoundary($dist, $lat, $lon, $lat1, $lat2, &$lon1, &$lon2)
     {
        $d = $lat - $lat1;
       
        $d1 = $d / cos(deg2rad($lat1));
        $d2 = $d / cos(deg2rad($lat2));
       
        $lon1 = min($lon - $d1, $lon - $d2);
        $lon2 = max($lon + $d1, $lon + $d2);
     }
     


$dist = $_GET['distance-proximity'];
     $lat = 51.501009;
     $lon = -0.141588;
     
     findLatBoundary($dist, $lat, $lat1, $lat2);
     findLonBoundary($dist, $lat, $lon, $lat1, $lat2, $lon1, $lon2);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
$sql_distance_stored_procedure ="SELECT *, DISTANCE(lat, lng, $lat, $lon) as distance, sd.category, us.user_nicename, ac.id, ac.user_id, um1.meta_value as area_text, um2.meta_value as company, um3.meta_value as company_name FROM address_coordinates as ac
                                LEFT JOIN igrv_usermeta as um1 on  um1.user_id = ac.user_id
                LEFT JOIN igrv_usermeta as um2 on  um2.user_id = ac.user_id
                LEFT JOIN igrv_usermeta as um3 on  um3.user_id = ac.user_id
                LEFT JOIN igrv_users as us on um1.user_id = us.ID
                LEFT JOIN scraped_data as sd on sd.name = us.user_login
                WHERE um1.meta_key = 'area_text'
                AND um2.meta_key = 'company'
                AND um3.meta_key = 'first_name'
                                AND lat BETWEEN $lat1 AND $lat2
                                AND lng BETWEEN $lon1 AND $lon2
                                HAVING distance < $dist
                                $order_by $asc_desc
                                limit 20"
;

Ueful resources:

http://www.geopostcode.org.uk/api.php

https://developers.google.com/maps/documentation/geocoding/intro

http://stackoverflow.com/questions/17481890/how-to-write-a-stored-procedure-in-phpmyadmin