Searching by ZIP code using a shared procedure

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