CODEWITHSUNDEEP
phpmysqlfunction
user2058002
user2058002

Reputation:

Creating a custom MySQL function?

I've just started using MySQL with PHP and I'd like to know if it's possible to create a custom function. This code snippet should illustrate what I'm trying to do.

// a somewhat complicated formula not suitable to embed into the query
function Distance($latA, $lonA, $latB, $lonB)
{
  // earth's radius
  $radius = 3956;

  $latA = deg2rad($latA);
  $lonA = deg2rad($lonA);
  $latB = deg2rad($latB);
  $lonB = deg2rad($lonB);

  // calculate deltas
  $deltaLat = $latB - $latA;
  $deltaLon = $lonB - $lonA;

  // calculate Great Circle distance
  $result = pow(sin($deltaLatitude / 2.0), 2) + (cos($latA) * cos($latB) * pow(sin($deltaLon / 2.0), 2));
  $distance = $radius * 2 * atan2(sqrt($result), sqrt(1 - $result));

  return $distance;
}

// how can I call Distance() in my query?
$query = "SELECT lat, lon FROM zipcodes WHERE Distance(lat, lon, 0, 0) < 20";
mysql_query($query);

Thanks in advance for any help!

Upvotes: 8

Views: 10491

Answers (4)

Andy
Andy

Reputation: 17771

You an declare this MySQL function in your application, and it will remain in the database until the database server is restarted.

mysql_query("CREATE FUNCTION Distance(LAT_A INT, LON_A INT, LAT_B INT, LON_B INT, )
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE radius, deltaLat, deltaLon, result, distance BIGINT;
SET radius=3956;
SET deltaLat=LAT_B-LAT_A;
SET deltaLon=LON_B-LON_A;
SET result=POW(SIN(deltaLat/2), 2) + (COS(LAT_A) * COS(LAT_B) * POW(SIN(deltaLon/2.0), 2));
SET distance=radius * 2 * ATAN2(SQRT(result), SQRT(1 - result));
RETURN distance;
END");

This uses MySQL's mathematical functions. Offloading this processing to the database is fast and efficient (the data doesn't have to travel across the wire, and you're only returned the results you want).

Once you've declared this, you can use it like so:

$query = "SELECT lat, lon FROM zipcodes WHERE Distance(lat, lon, 0, 0) < 20";
mysql_query($query);

However if your database does restart, any functions or procedures declared previously are lost. It's possible to handle MySQL error 1305 (Function functionName does not exist) gracefully at the application level.

In your database error handler:

switch (mysql_errno()):
    case 1305:
        if (false === $database->_declareStoredProcedureFlag) {
             if ($c = preg_match_all("/FUNCTION [a-zA-Z0-9]+\." .
                 "([a-zA-Z0-9_]*) does not exist/is",
                 mysql_error(), $matches)
             ) {
                 $storedFunctionName = $matches[1][0];
                 $database->_declareStoredProcedureFlag = true;
                 if (true === $database->declareStoredFunction($storedFunctionName)) {
                     $result = mysql_query($query);
                 }
             }
         }
         break;
    ...

Upvotes: 13

gview
gview

Reputation: 15361

Mysql has stored routines http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

Some of the functions may be different, for example, the mysql equivalent to php's deg2rad is mysql's radians() function. I think you should be able to create an equivalent function. With this approach, You should be aware that these queries will have to tablescan the entire zipcodes table every time however.

Upvotes: 1

Anon2011
Anon2011

Reputation: 11

Because functions don't work in quotes " or ' you have to split it up with a full stop. (.)

$query = mysql_query("SELECT lat, lon FROM zipcodes WHERE ".Distance($lat, $lon, 0, 0)." < 20");

Hope this helps :)

Upvotes: 0

Daniel Pereira
Daniel Pereira

Reputation: 1785

You certainly can write a MySQL function to do this. I don't have time to write one now, but here's where you get started:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Upvotes: 1

Related Questions