Find closest longitude and latitude in a database?

Question

I am trying to build a quote system that quotes based off the distance from a delivery hub.

I have one table (table1) with all the postal codes and their corresponding long and lat in the country

I have another table (table2) with all my shipping hubs with their post codes and corresponding long and lat.

So when the user inputs their zip code I can get their long and lat from my database with this code:

$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT name, lng, lat FROM posts WHERE code=:code");
$stmt->execute(['code' => $post1]); 

while ($row = $stmt->fetch()) {
    $lng1 = $row['lng'];
    $lat1 =  $row['lat'];
    $name1 =  $row['name'];
}

echo "name 1: ".$name1;
echo "<br>";
echo "post code 1: ".$lng1;
echo "<br>";
echo "post code 1: ".$lat1;

How do I use that long and lat to then find the closest hub in table 2?

I would just add one more idea to the duplicate qurstion: if you already know all delivery address coordinates and all your hub coordinates, then you can pre-calculate and store all possible distances in a table. Hub and post code coordinates rarely change and in such event, you can refresh your table. — Shadow, Sep 18 '20 at 22:04

score 0 · Answer 1 · answered Sep 18 '20 at 22:05

MySQL has spatial support; you can use build points from the coordinates, and then use st_distance_sphere() to compute the distances:

select *
from (
    select p.name post_name, p.lng post_lng, p.lat post_lat, 
        h.name, hub_name, h.lng hub_lng, h.lat hub_lat,
        row_number() over(
            order by st_distance_sphere(point(p.lng, p.lat), point(h.lng, h.lat))
        ) rn
    from posts p
    cross join hubs h 
    where code =: code
) t
where rn = 1

You can also do this with a subquery:

select p.name post_name, p.lng post_lng, p.lat post_lat, 
    h.name, hub_name, h.lng hub_lng, h.lat hub_lat
from posts p
inner join hubs h on h.id = (
    select id
    from hubs h1
    order by st_distance_sphere(point(p.lng, p.lat), point(h1.lng, h1.lat))
    limit 1
)

Find closest longitude and latitude in a database?

1 Answers1