php - Find the nearest postcodes to given postcode using ordance survey easting and northing system -

i have database table of uk postcodes has 4 fields:

postcode, east, north, pqi  

example values: st1 6bq, 388605, 349057,10

the primary key postcodes , east , north bothint(11)` fields.

does have efficient mysql query return nearest 5 postcodes given post?

i have seen many examples using long , lat, not northing , easting.

convert northings/eastings lat/long, remembering os grid based on osgb36 rather wgs84.

i use following class:

<?php  namespace osgb36;  use \geodetic\datum;  class converter {     private $_osref;     private $_fromdatum;     private $_todatum;      public function __construct()     {         $this->_osref = new osref();         $this->_fromdatum = new datum(datum::osgb36);         $this->_todatum = new datum(datum::wgs84);     }      /**      * converts easting/northing lat/long      *       * @param integer $eastings      * @param integer $northings      * @return \geodetic\latlong      */     public function calculatelatlong($eastings, $northings)     {         $this->_osref->setnorthings($northings)                      ->seteastings($eastings);         $osgb36latlong = $this->_osref->tolatlong(             $this->_fromdatum->getreferenceellipsoid()         );          $ecef = $osgb36latlong->toecef($this->_fromdatum);         $ecef->towgs84($this->_fromdatum);         $wgs84latlong = $ecef->tolatlong($this->_todatum);          return $wgs84latlong;     } } 

and

<?php  namespace osgb36;  use \geodetic\latlong\coordinatevalues;  class osref {     private $_northings;     private $_eastings;      public function __construct($northings = null, $eastings = null)     {         $this->_northings = $northings;         $this->_eastings  = $eastings;     }      public function setnorthings($northings)     {         $this->_northings = $northings;          return $this;     }      public function seteastings($eastings)     {         $this->_eastings = $eastings;          return $this;     }       private function _sinsquared($x) {         return sin($x) * sin($x);     }      private function _tansquared($x) {         return tan($x) * tan($x);     }      private function _secant($x) {         return 1.0 / cos($x);     }      private function _cosecant($x) {         return 1.0 / sin($x);     }      private function _cotangent($x) {         return 1.0 / tan($x);     }      public function tolatlong(\geodetic\referenceellipsoid $airy1830) {         $osgb_f0       = 0.9996012717;    //  central meridan scale factor         $n0            = -100000.0;       //  true origin northing         $e0            = 400000.0;        //  true origin easting         $phi0          = deg2rad(49.0);   //  true origin latitude         $lambda0       = deg2rad(-2.0);   //  true origin longitude         $semimajoraxis = $airy1830->getsemimajoraxis();         $semiminoraxis = $airy1830->getsemiminoraxis();         $esquared      = $airy1830->getfirsteccentricitysquared();         $easting       = $this->_eastings - $e0;         $northing      = $this->_northings - $n0;         $n             = ($semimajoraxis - $semiminoraxis) / ($semimajoraxis + $semiminoraxis);         $m             = 0.0;         $phiprime      = ($northing / ($semimajoraxis * $osgb_f0)) + $phi0;          {             $m = ($semiminoraxis * $osgb_f0) *                 (((1 + $n + ((5.0 / 4.0) * $n * $n) + ((5.0 / 4.0) * $n * $n * $n)) *                 ($phiprime - $phi0)) -                 (((3 * $n) + (3 * $n * $n) + ((21.0 / 8.0) * $n * $n * $n)) *                 sin($phiprime - $phi0) *                 cos($phiprime + $phi0)) +                 ((((15.0 / 8.0) * $n * $n) + ((15.0 / 8.0) * $n * $n * $n)) *                 sin(2.0 * ($phiprime - $phi0)) *                 cos(2.0 * ($phiprime + $phi0))) -                 (((35.0 / 24.0) * $n * $n * $n) *                 sin(3.0 * ($phiprime - $phi0)) *                 cos(3.0 * ($phiprime + $phi0))));             $phiprime += ($northing - $m) / ($semimajoraxis * $osgb_f0);         } while (($northing - $m) >= 0.001);          $v = $semimajoraxis * $osgb_f0 * pow(1.0 - $esquared * $this->_sinsquared($phiprime), -0.5);         $rho = $semimajoraxis * $osgb_f0 * (1.0 - $esquared) *             pow(1.0 - $esquared * $this->_sinsquared($phiprime), -1.5);         $etasquared = ($v / $rho) - 1.0;         $vii = tan($phiprime) / (2 * $rho * $v);         $viii = (tan($phiprime) / (24.0 * $rho * pow($v, 3.0))) *             (5.0 + (3.0 * $this->_tansquared($phiprime)) + $etasquared - (9.0 * $this->_tansquared($phiprime) * $etasquared));         $ix = (tan($phiprime) / (720.0 * $rho * pow($v, 5.0))) *             (61.0 + (90.0 * $this->_tansquared($phiprime)) + (45.0 * $this->_tansquared($phiprime) * $this->_tansquared($phiprime)));         $x = $this->_secant($phiprime) / $v;         $xi = ($this->_secant($phiprime) / (6.0 * $v * $v * $v)) * (($v / $rho) + (2 * $this->_tansquared($phiprime)));         $xii =  ($this->_secant($phiprime) / (120.0 * pow($v, 5.0))) *             (5.0 + (28.0 * $this->_tansquared($phiprime)) + (24.0 * $this->_tansquared($phiprime) * $this->_tansquared($phiprime)));         $xiia = ($this->_secant($phiprime) / (5040.0 * pow($v, 7.0))) *             (61.0 + (662.0 * $this->_tansquared($phiprime)) + (1320.0 * $this->_tansquared($phiprime) * $this->_tansquared($phiprime)) +             (720.0 * $this->_tansquared($phiprime) * $this->_tansquared($phiprime) * $this->_tansquared($phiprime)));         $phi = $phiprime - ($vii * pow($easting, 2.0)) + ($viii * pow($easting, 4.0)) - ($ix * pow($easting, 6.0));         $lambda = $lambda0 +             ($x * $easting) - ($xi * pow($easting, 3.0)) + ($xii * pow($easting, 5.0)) - ($xiia * pow($easting, 7.0));          $latlongcoordinates = new coordinatevalues(             $phi,             $lambda,             \geodetic\angle::radians,             0.0,             \geodetic\distance::metres         );         return new \geodetic\latlong($latlongcoordinates);     }      function togridref()     {         $hundredkme = floor($this->_eastings / 100000);         $hundredkmn = floor($this->_northings / 100000);         $firstletter = "";         if ($hundredkmn < 5) {             if ($hundredkme < 5) {                 $firstletter = "s";             } else {                 $firstletter = "t";             }         } else if ($hundredkmn < 10) {             if ($hundredkme < 5) {                 $firstletter = "n";             } else {                 $firstletter = "o";             }         } else {             $firstletter = "h";         }          $secondletter = "";         $index = 65 + ((4 - ($hundredkmn % 5)) * 5) + ($hundredkme % 5);         $ti = $index;         if ($index >= 73)         {             $index++;         }         $secondletter = chr($index);          $e = round(($this->_eastings - (100000 * $hundredkme)) / 100);         $n = round(($this->_northings - (100000 * $hundredkmn)) / 100);          return sprintf("%s%s%03d%03d", $firstletter, $secondletter, $e, $n);     }      public static function createosreffromgridref($gridref)     {         $char1 = substr($gridref, 0, 1);         $char2 = substr($gridref, 1, 1);         $east  = substr($gridref, 2, 3) * 100;         $north = substr($gridref, 5, 3) * 100;         if ($char1 == 'h') {             $north += 1000000;         } else if ($char1 == 'n') {             $north += 500000;         } else if ($char1 == 'o') {             $north += 500000;             $east  += 500000;         } else if ($char1 == 't') {             $east += 500000;         }         $char2ord = ord($char2);         if ($char2ord > 73)         {             $char2ord--; // adjust no         }         $nx = (($char2ord - 65) % 5) * 100000;         $ny = (4 - floor(($char2ord - 65) / 5)) * 100000;         return new osref($north + $ny, $east + $nx);     } } 

combined geodetic library.

it's pretty quick chundering through entirety of codepoint open give me database of postcode details including wgs84 lat/long coordinates use openstreetmap.