C.4. Step 3: Create the FindNearby Stored ProcedureCreate a SQL stored procedure called FindNearby: CREATE PROCEDURE FindNearby @CenterLat float, @CenterLon float, @SearchDistance float, @Units int AS declare @CntXAxis float declare @CntYAxis float declare @CntZAxis float declare @EarthRadius float -- Miles = 0 if(@Units = 0) set @EarthRadius = 3963.0 else set @EarthRadius = 6378.5 set @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon)) set @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon)) set @CntZAxis = sin(radians(@CenterLat)) select BusinessEntity.ID, BusinessEntity.Name, ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + ZAxis*@CntZAxis) from [BusinessEntity] where @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + ZAxis*@CntZAxis) <= @SearchDistance order by ProxDistance ASC Now you can execute FindNearby queries using your own SQL implementation. |