Jump to content


New Members
  • Content Count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About davewx

  • Rank
  1. davewx

    PHP Mapping

    Hi all, I have a MySQL database of latitude and longitude coordinates that have an associated value. In this case it is temperature. There is a value for each 1.25km or so (effectively a grid). I was wondering if anyone knows of an efficient method to plot these points on a map to create a contour or smoothed temperature map? Are there any useful PHP libraries that would be effective in completing this? The data is simple with just a pair of coordinates and a value. Any info would be awesome. Thanks
  2. davewx

    Efficient MYSQL Join or Update

    Wow thank you very much. The indexing was the issue. Query is basically instantaneous now.
  3. I have two tables of point data. The first main table has about 50,000 rows of data with values that represent the temperature for a grid point. I also have another table with about 25,000 rows of data. I'm trying to update the values from the second table into the first table for the closest point. Doing this using spatial functions in MYSQL would likely be slow, so I decided to do a spatial join in ArcGIS to give them relating ID's. As such, there are repeating id's from the second table in the first table as expected. This is because the grid spacing in the first (main) table is much smaller than the second table and there will be duplicate grid points with the same value...which is what I want. The only problem is, when I try to update the values by id = id, the query takes forever to complete. It seems as if its looping through each row for each time which seems very inefficient. Is there a better method for doing this? UPDATE grid, hrdps_points SET grid.val=hrdps_points.value WHERE grid.hrdpsid = hrdps_points.id
  4. davewx

    Update MySQL Table from another table

    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?
  5. davewx

    Update MySQL Table from another table

    Thanks for the reply Barand. When you say $x and $y, is that a delta distance between reach raw table point? Thanks, Dave
  6. 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,

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.