[Back]

/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Locations]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[ADefWebserver_Locations](
    [LocationId] [int] IDENTITY(1,1) NOT NULL,
    [ModuleID] [int] NOT NULL,
    [LocationName] [nvarchar](50) NOT NULL,
    [Address] [nvarchar](50) NOT NULL,
    [City] [nvarchar](50) NOT NULL,
    [State] [nvarchar](2) NOT NULL,
    [Zip] [nvarchar](9) NOT NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
 CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED 
(
    [LocationId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE FUNCTION [dbo].[XAxis] ( @lat float, @lon float ) RETURNS float AS BEGIN RETURN COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lat) * COS(4 * (4 * atn2(1, 5) - atn2(1, 239)) / 180 * @lon) END
GO

CREATE FUNCTION [dbo].[YAxis] ( @lat float, @lon float ) RETURNS float AS BEGIN RETURN COS(4 * (4 * atn2(1,5) - atn2(1,239)) / 180 * @lat) * SIN(4 * (4 * atn2(1,5) - atn2(1,239)) / 180 * @lon) END
GO

CREATE FUNCTION [dbo].[ZAxis] ( @lat float ) RETURNS float AS BEGIN RETURN SIN(4 * (4 * atn2(1,5) - atn2(1,239)) / 180 * @lat) END
GO

CREATE PROCEDURE [dbo].[GetNearbyLocations] @CenterLatitude FLOAT, @CenterLongitude FLOAT, @SearchDistance FLOAT, @EarthRadius FLOAT, @ModuleId int  
AS DECLARE @CntXAxis FLOAT DECLARE @CntYAxis FLOAT DECLARE @CntZAxis FLOAT SET @CntXAxis = COS(RADIANS(@CenterLatitude)) * COS(RADIANS(@CenterLongitude)) SET @CntYAxis = COS(RADIANS(@CenterLatitude)) * SIN(RADIANS(@CenterLongitude)) SET @CntZAxis = SIN(RADIANS(@CenterLatitude)) SELECT TOP 100 *, ProxDistance = @EarthRadius * ACOS( dbo.XAxis(latitude, longitude)*@CntXAxis + dbo.YAxis(latitude, longitude)*@CntYAxis + dbo.ZAxis(latitude)*@CntZAxis) FROM ADefWebserver_Locations WHERE ModuleID = @ModuleId AND (@EarthRadius * ACOS( dbo.XAxis(latitude, longitude)*@CntXAxis + dbo.YAxis(latitude, longitude)*@CntYAxis + dbo.ZAxis(latitude)*@CntZAxis) <= @SearchDistance) AND latitude IS NOT NULL ORDER BY ProxDistance ASC

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/

Buy DotNetNuke Modules from Snowcovered

 DotNetNuke™ is a registered trademark of DotNetNuke Corporation.