Jump to content
davewx

Update MySQL Table from another table

Recommended Posts

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 by davewx

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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

 

Thanks, 

 

Dave

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.