Find the closest coordinates in a MySQL database-CodePudding

I am creating a simple script that needs to find the users current zipcode by their location. I have a massive database of all the zipcodes with their coordinates. I need to find the closest zipcode to their location. The site is a wordpress site so I am using their functions.

I have tried the following code but it is not accurate enough. It is off by about 15-20 miles and picks the wrong zipcode.

$SQL = $wpdb->prepare(
    "SELECT zip, ( abs(lat - %d)   abs(lon - %d) ) as distance ".
    "FROM {$wpdb->prefix}zipcodes ORDER BY distance LIMIT 1",
    $data['lat'], $data['lon']
);
$closest = $wpdb->get_results( $SQL );

CodePudding user response：

If you have v5.7 , then you can use ST_Distance_Sphere:

https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere

I'm going to guess at an implementation in a fiddle. Take a look at this. These are random zips, and I just picked a random zip to calculate distance from. I didn't adjust your php code, because it looks like you know how to prepare a statement already, so I'm just providing the SQL.

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=a851a6539bfd5028c53cf5ad0e1a1f1f

SELECT zip, lon, lat, 
  ST_Distance_Sphere(
    POINT(-86.558882, 34.668212), POINT (lon, lat)
  ) as dist
FROM zipcodes ORDER BY 4 DESC;

where the arguments to POINT are (lon, lat).

Here is the POINT reference: https://dev.mysql.com/doc/refman/5.7/en/gis-point-property-functions.html

Let me know if this helps.

For future reference, you can find the CREATE statements for your table in the mysql editor (or you can get them from the command line if you need to, but who is using the command line?). If you give those and an INSERT statement with some sample data in your question, along with the expected outcomes, that will give people more insight into your problem. Also, provide your sql version and engine, since some things are engine and version specific.

You can also set up a fiddle with those statements to give people a headstart, but you should also include the statements and data themselves in the question, just in case the fiddle site goes down.

See this discussion on minimum reproducible examples for SQL questions:

Why should I provide a Minimal Reproducible Example for a very simple SQL query?