Update MySQL Table from another table

[davewx]

I have a MySQL table called "grid" with a set amount of grid points (there are a lot), each row has its associated centroid latitude and longitude, as well as a unique id and an empty column that is a placeholder for a "value". Each point occupies a 1.25 kilometre grid spacing. 

 

I also have another table called "raw" that has a latitude longitude grid spacing, although not an equal spacing as my other format (roughly 2.5km) (this is data that I don't have control over). The "value" for each row from this table is what I want to insert into the "grid" table, however I need it to be the nearest value from this table. There will obviously be some duplicate values (1.25km < 2.5km), however that is not an issue. I just need to ensure that the nearest point from the the raw table is inserted into the grid table. 

 

So far I have as follow 

$mysqli = new mysqli("localhost", "db", "user", "sowx_grid");

$results = mysqli_query($mysqli, "SELECT * FROM grid")
    or die (mysqli_error($mysqli));

//fetch the results
$row = mysqli_fetch_all($results);


    foreach ($row as $values) {

	$lon = $values[2];
	$lat = $values[3];
	$id = $values[1];
}

/* close connection */
$mysqli->close();

What I was going to do is use a foreach loop and loop through each pair of lat/lon/id and use a spatial function to make a mysql query on the raw table and select the value that is closest. This was going to be done by

$sql = "SELECT  value ," . "(@distance:=(3963*ACOS(SIN(a.latitude/57.3)*SIN($lat/57.3)+COS(a.latitude/57.3)*COS($lat/57.3)*COS($lon/57.3 - a.longitude/57.3)))) AS distance FROM rawlam as a " . 
                                                   " ORDER BY distance limit 0,1";		
$res = mysqli_query($mysqli, $sql)
    or die (mysqli_error($mysqli));
$raw = mysqli_fetch_all($res);

With this, I would get the raw "value" and update the initial grid database where the ID in the foreach loop is the same. 

 

With a code that would look like this, however when I run this...it updates the table but it is painfully slow. (Running on a 4gb VPS). 

<?php

$mysqli = new mysqli("localhost", "user", "pw", "sowx_grid");


$results = mysqli_query($mysqli, "SELECT * FROM temp_grid")
    or die (mysqli_error($mysqli));

//fetch the results
$row = mysqli_fetch_all($results);


    foreach ($row as $values) {

	$lon = $values[2];
	$lat = $values[3];
	$id = $values[1];
	
	$sql = "SELECT  value ," . "(@distance:=(3963*ACOS(SIN(a.latitude/57.3)*SIN($lat/57.3)+COS(a.latitude/57.3)*COS($lat/57.3)*COS($lon/57.3 - a.longitude/57.3)))) AS distance FROM rawlam as a " . 
                                                   " ORDER BY distance limit 0,1";		
$res= mysqli_query($mysqli, $sql)
    or die (mysqli_error($mysqli));
$raw = mysqli_fetch_all($res);

$value  = $raw[0][0];


$update = "UPDATE grid SET value=$value WHERE id=$id";
$out = mysqli_query($mysqli, $update)
    or die (mysqli_error($mysqli));
}

/* close connection */
$mysqli->close();

?>	

Is there a more efficient way to update a MySQL table based on a distance between a set of coordinates?

 

Any help would be greatly appreciated.

 

Thanks, 

Edited October 28, 2017 by davewx

[Barand]

Each grid centroid will lie in one raw grid square (unless on a boundary in which case it is equidistant from the centres of two). You could match grid records against the raw record by joining on the containing square

UPDATE grid 
JOIN raw
ON
    grid.lat BETWEEN (raw.lat - $y) AND (raw.lat + $y) 
    AND grid.long BETWEEN (raw.long  - $x) AND (raw.long + $x) 
SET grid.value = raw.value

For small distance like these, 1 degree of longitude is 111 Km and 1 degree of latitude is 111*cos(lat).

 

You can use those to calculate $x and $y in the query

Edited October 28, 2017 by Barand

[davewx]

Thanks for the reply Barand. When you say $x and $y, is that a delta distance between reach raw table point?

 

Thanks, 

 

Dave

[Barand]

It will be half of the 2.5Km gridsize in degrees

[davewx]

Thanks again Barand. I worked on the code some today and came up with a working example that is basically the same as your code, however the duration is still very long to complete. Is there a way to do this same thing without "joining" the tables? Or can this be done by using a CSV file and matching the grid point to the closest raw point in the csv file?