Find closest match in MySQL by decimal columns

Question

After noon,

I am having difficulty resolving this issue. I have a MySQL table with a list of UK postal codes and their longitude and latitude. I want to be able to search in a table that finds the closest zip code to a given long / lat pair.

The query I'm trying to use is:

"SELECT id, outcode AS thecode, @la := MATCH(lat) AGAINST(?) AS score_lat, @ln := MATCH(lng) AGAINST(?) AS score_lng, @la + @ln AS score_total FROM postcodes ORDER BY score_total DESC LIMIT 10 

This, however, simply returns what seems like a random zip code, e.g. with Lat: 55.775549 and Long: -4.047556

 Array ( [0] => Array ( [id] => 929 [thecode] => FK14 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [1] => Array ( [id] => 2785 [thecode] => UB3 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [2] => Array ( [id] => 993 [thecode] => G70 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [3] => Array ( [id] => 2849 [thecode] => WC2B [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [4] => Array ( [id] => 1057 [thecode] => GU29 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [5] => Array ( [id] => 2913 [thecode] => WS13 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [6] => Array ( [id] => 1121 [thecode] => HP20 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [7] => Array ( [id] => 1185 [thecode] => IG6 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [8] => Array ( [id] => 1249 [thecode] => IV25 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) [9] => Array ( [id] => 1313 [thecode] => KA8 [score_lat] => 0 [score_lng] => 0 [score_total] => 0 ) ) 

Database Schema:

 CREATE TABLE `postcodes` ( `id` int(11) NOT NULL auto_increment, `outcode` varchar(4) NOT NULL, `lat` varchar(20) NOT NULL, `lng` varchar(20) NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `lat` (`lat`), FULLTEXT KEY `lng` (`lng`) ) ENGINE=MyISAM AUTO_INCREMENT=2975 DEFAULT CHARSET=latin1 AUTO_INCREMENT=2975 ; 

I hope someone can help! If you need more information, please just ask ...

Thanks,

tip2tail

Answer 1

The MySQL MATCH() function is used for full-text search according to character strings. (Therefore, it is not surprising that it returns a value of zero.)

If “closest” means that you want to calculate the distance (as measured “like a crow”) between two points on the map, with coordinates given in (decimal degrees) latitude and longitude, you really need to use long distance calculation (GCD).

http://en.wikipedia.org/wiki/Great-circle_distance

You can skip all these gory details and just use my implementation. The following is an excerpt from the SELECT list of one of my SQL statements, this expression calculates the distance (in miles) between two points ...

  , ACOS( COS(RADIANS( d2.latitude )) * COS(RADIANS( d1.latitude )) * COS(RADIANS( d2.longitude ) - RADIANS( d1.longitude )) + SIN(RADIANS( d2.latitude )) * SIN(RADIANS( d1.latitude )) )*3958.82 AS distance_miles 

In this example, d1 represents the origin, and d2 represents the destination. latitude and longitude supplied as DECIMAL values.

With one “known” point for d1 , I can order this expression to get the “closest” d2 . (For several points of origin, I can order d1.id , and then use this expression to get the closest d2 for each d1 . But enough about my problem ...

---

I copied the request from your question and changed it (below). Basically, I removed the “score” columns and replaced it with an expression that calculates the distance:

 SELECT id , outcode AS thecode , ACOS( COS(RADIANS( d2.latitude )) * COS(RADIANS( @d1_latitude )) * COS(RADIANS( d2.longitude ) - RADIANS( @d1_longitude )) + SIN(RADIANS( d2.latitude )) * SIN(RADIANS( @d1_latitude )) )*3958.82 AS distance_miles FROM postcodes d2 JOIN (SELECT @d1_latitude := ?, @d1_longitude := ?) v ORDER BY distance_miles LIMIT 10 

In this case, the @d1_ variables (assigned from the bind variables) are the latitude and longitude of your “known” point. For each row in your postcodes table (which I used as d2 for convenience), this expression calculates the distance between the lat / long in the table and the "known" point.

NOTE: the built-in view with an alias like v is there, so you can snap the latitude only once and assign values ​​to custom variables that can be referenced. This inline view may be omitted, and you can see where you will need to snap the latitude twice.

NOTE: this calculates the distance in miles. You can easily get the distance in kilometers (km) by replacing a different value instead of the constant 3958.82 .

NOTE. No need to return the distance; you can simply put this expression in the ORDER BY clause if you are only interested in returning the 10 closest in order by distance, for example.

 SELECT id , outcode AS thecode FROM postcodes d2 JOIN (SELECT @d1_latitude := ?, @d1_longitude := ?) v ORDER BY ACOS( COS(RADIANS( d2.latitude )) * COS(RADIANS( @d1_latitude )) * COS(RADIANS( d2.longitude ) - RADIANS( @d1_longitude )) + SIN(RADIANS( d2.latitude )) * SIN(RADIANS( @d1_latitude )) )*3958.82 AS distance_miles LIMIT 10 

Please let me know if you are looking for something other than the distance between two points, because in this case this answer really will not help you.