1 Make a Stored Function...
ALTER DEFINER=`XXXX`@`XXXXt` FUNCTION `GetDistance`(
lat1 numeric (9,6),
lon1 numeric (9,6),
lat2 numeric (9,6),
lon2 numeric (9,6)
) RETURNS decimal(10,5)
BEGIN
DECLARE x decimal (20,10);
DECLARE pi decimal (21,20);
SET pi = 3.14159265358979323846;
SET x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180 ) + cos(
lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos( abs ( (lon2 * pi/180) -
(lon1 *pi/180) ) );
SET x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x );
RETURN ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END
2. Make a stored procedure
PROCEDURE `GetNearbyZipCodes`(zipbase varchar (6), ziprange numeric (15))
BEGIN
DECLARE lat1 decimal (5,2);
DECLARE long1 decimal (5,2);
DECLARE rangeFactor decimal (7,6);
SET rangeFactor = 0.014457;
SELECT latitude,longitude into lat1,long1 FROM zips WHERE ZIPcode = zipbase;
SELECT B.ZIPcode
FROM zips AS B
WHERE
B.latitude BETWEEN lat1-(ziprange*rangeFactor) AND lat1+(ziprange*rangeFactor)
AND B.longitude BETWEEN long1-(ziprange*rangeFactor) AND long1+(ziprange*rangeFactor)
AND GetDistance(lat1,long1,B.latitude,B.longitude) <= ziprange;
END
3. Access it in your code
Dim sqlcon As New MySqlConnection(ConnString)
Dim reader As MySqlDataReader
Dim cmd As MySqlCommand = New MySqlCommand
cmd.Parameters.AddWithValue("@zipbase", "85250")
cmd.Parameters.AddWithValue("@ziprange", "30")
cmd.CommandText = "GetNearbyZipCodes"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = sqlcon
sqlcon.Open()
reader = cmd.ExecuteReader()
GridView1.DataSource = reader
GridView1.DataBind()
sqlcon.Close()
This will display all the results of ZIP codes in a 30 mile radius around 85250