Select Zipcode, ZipDistance('49415') as Distance
From Zipcodes
Where Latitude = 'somenumber' And Longitude = 'somenumber'
Order By Distance

'returns zip codes within proximity range
Public Shared Function GetNearByZips(zipcode as String,zipprox as Integer) as String
	Dim sSQL1,sSQL2 as String
	Dim iStartLong,iStartLat,iLongVary,iLatVary as Decimal
	Dim dblMiles as Double
	'look up lat and long on passed zip
	sSQL1 = "Select * From ZipSource Where ZipCode = '" & zipcode & "';"
	Dim oConn as New SQLConnection(Client.sAltConnStr)
	Try	
		oConn.Open()		
		Dim oComm as New SQLCommand(sSQL1, oConn)
		Dim dr as SQLDataReader = oComm.ExecuteReader(CommandBehavior.CloseConnection)
		If dr.Read() Then
			iStartLat = dr("Latitude")
			iStartLong = dr("Longitude")
		Else
			'return error if no zip code was found
			Return "Sorry, no matching zip code was found."
		End If
		oConn.Close()
		dblMiles = Convert.ToDouble(zipprox)
		'determine longitude/latitude variance
		iLatVary = dblMiles/((6076/5280) * 60)
		iLongVary = dblMiles/(((Math.Cos(Convert.ToDouble(iStartLat * Math.PI/180)) * 6076)/5280) * 60)
		'query zip codes that fall with variance
		sSQL2 += "Select ZipCode From ZipSource "
		sSQL2 += "Where Longitude Between " & (iStartLong - iLongVary) & " And " & (iStartLong + iLongVary) & " "
		sSQL2 += "And Latitude Between " & (iStartLat - iLatVary) & " And " & (iStartLat + iLatVary) & ";"
		Dim oConn2 as New SQLConnection(Client.sAltConnStr)
		oConn2.Open()		
		Dim oComm2 as New SQLCommand(sSQL2, oConn2)
		Dim dr2 as SQLDataReader = oComm2.ExecuteReader(CommandBehavior.CloseConnection)
                'concat results as single string
		Dim sZipcodeList as String
		Do While dr2.Read()
			sZipcodeList += "'" & dr2("ZipCode") & "',"
		Loop
		'remove last comma
		Return Util.CutLeft(sZipcodeList,sZipcodeList.LastIndexOf(","))
		Catch ex as Exception
			Return ex.Message
	End Try
End Function

CREATE PROC up_FindZipCodesWithinRadius

	@ZipCode char(5) ,
	@GivenMileRadius int
AS
SET NOCOUNT ON

DECLARE @lat1 float, 
	@long1 float

SELECT  @lat1= latitude,
        @long1 = longitude 
FROM ZipSource
WHERE zipcode = @ZipCode

SELECT ZipCode ,DistanceInMiles
FROM
(
	SELECT  ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + 
				(Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * Cos((longitude/57.2958) - (@Long1/57.2958)))), 2)) / 
				((Sin(@Lat1/57.2958) * Sin(latitude/57.2958)) + (Cos(@Lat1/57.2958) * Cos(latitude/57.2958) * 
				Cos((longitude/57.2958) - (@Long1/57.2958)))))) DistanceInMiles
	FROM ZipSource
) a
WHERE a.DistanceInMiles <= @GivenMileRadius
--AND ZipCode <> @ZipCode
ORDER BY DistanceInMiles

GO

EXEC up_FindZipCodesWithinRadius '35085',20
GO

DROP PROC up_FindZipCodesWithinRadius

quote:

---

Quick question - should the last line of the inner select read '(@Long1/57.2958)))))) AS DistanceInMiles'

---