If you're on a hosting package, I don't know if you'll have access to do this.
Basically, a friend of mine put together (he copied some code and pasted it with some other code) to make a MySQL SQL function. The code is in C++ and requires it to be compiled in a way MySQL prefers. He's had problems compiling it for Red Hat, but I think I've worked around them (I've got it up on my web host and they're running Red Hat). Let me describe what it does and then you can determine if this is something that might be helpful:
The function is called geodist([latitude1], [longitude1], [latitude2], [longitude2]). It returns a numeric distance value to two decimal places in miles (I'm sure with some minor tweaking, the miles can be converted to kilometers from within the C++ code). You can then do things like this:
$query = 'SELECT Zip, geodist(' . $lat . ', ' . $long . ', Latitude, Longitude) AS distance FROM tbl_zip WHERE geodist(' . $lat . ', ' . $long . ', Latitude, Longitude) < ' . $radius . ' ORDER BY geodist(' . $lat . ', ' . $long . ', Latitude, Longitude)';
Where $lat and $long are the coordinates of the center location. Then $radius is the number of miles you want to keep the search under. On top of that, the query then sorts the results by distance, showing the closest location first. You can see it in action here:
http://www.axtime.com/zip/geodist.php (USA zips only). I was fiddling with this and it brings the results back surprisingly fast (you end up waiting for the data to download versus the server to process the request).
I don't know if this will fit your application or not. I'm not an expert so if this doesn't work or you have problems installing, you will be on your own along with any docs at MySQL (they have sample code on how to create and install C++ user functions). Note the commented install instructions.
geodist.cpp:
/*
Created by Geoff Parris
[email]geoff@eclectic.com[/email]
November 2003
Follows GPL licensing
INSTALL:
gcc -shared -o geodist.so -I /usr/local/mysql/include geodist.cpp
Place in a mysql accessible library such as usr/lib
Run from MySQL:
create function geodist returns real soname "geodist.so";
*/
#ifdef STANDARD
#include <stdio.h>
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong; /* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>
#endif
#include <mysql.h>
#include <m_ctype.h>
#include <m_string.h> // To get strmov()
#ifdef HAVE_DLOPEN
/* These must be right or mysqld will not find the symbol! */
extern "C" {
my_bool geodist_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void geodist_deinit(UDF_INIT *initid);
double geodist(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
char *error);
}
double pi = 3.141592653589792907377;
double rad(double gr) {return pi * gr / 180.0;}
/*
Simple example of how to get a sequences starting from the first argument
or 1 if no arguments have been given
*/
my_bool geodist_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if (args->arg_count != 4)
{
strmov(message,"geodis takes 4 args");
return 1;
}
if( args->arg_type[0] != REAL_RESULT || args->arg_type[1] != REAL_RESULT ||
args->arg_type[2] != REAL_RESULT || args->arg_type[3] != REAL_RESULT )
{
strcpy(message, "geodist() requires all 4 parameters to be type REAL");
return 1;
}
// set max decimals to 2 because we are returning miles
initid->decimals = 2;
return 0;
}
void geodist_deinit(UDF_INIT *initid)
{
}
double geodist(UDF_INIT *initid, UDF_ARGS *args, char *is_null,
char *error)
{
double lat1 = 0.0;
double lon1 = 0.0;
double lat2 = 0.0;
double lon2 = 0.0;
lat1 = *((double*) args->args[0]);
lon1 = *((double*)args->args[1]);
lat2 = *((double*)args->args[2]);
lon2 = *((double*)args->args[3]);
return 0.621 * 6371.2 * 2 *
atan2( sqrt(fabs(0 + pow(sin(rad(lat2)/2 - rad(lat1)/2),2) +
cos(rad(lat1)) * cos(rad(lat2)) * pow(sin(rad(lon2)/2 -
rad(lon1)/2),2))),sqrt(fabs(1 - pow(sin(rad(lat2)/2 -
rad(lat1)/2),2) + cos(rad(lat1)) * cos(rad(lat2)) *
pow(sin(rad(lon2)/2 - rad(lon1)/2),2))));
}
#endif /* HAVE_DLOPEN */
