基于纬度、经度和 UTC 偏移量的日出日落计算 - SQL Server 2005
5.00/5 (2投票s)
使用 SQL Server 2005 确定特定纬度和经度的日出日落值
引言
此技巧的主要目标是为 SQL Server 2005 数据库提供一组功能，根据提供的参数中的地理位置信息确定日出和日落时间，并返回一个 DateTime 值。
这组函数的主要目标是从数据库直接进行与日光相关的计算，用作天文计算器。
背景
这是美国海军天文台日出/日落算法在 SQL Server 中的实现，可能（但未经测试）与市场上大多数基于 SQL 的引擎兼容。
Using the Code
使用方法非常简单。包含两个主要的存储过程：DetermineSunrise 和 DetermineSunset。它们都将期望日期和地理位置参数。
DECLARE @OffSet int
SET @OffSet = dbo.udf_getUTCOffSet()
-- Buenos Aires, Argentina, 34, 35 S, 58, 22 W, 11th May 2012
--
-- Parameters:
-- -------------
-- Today's date
-- latitude
-- longitude
-- UTC offset
Exec DetermineSunrise
'05/11/2012',
-34.58,
-58.3,
@OffSet
Exec DetermineSunset
'05/11/2012',
-34.58,
-58.3,
@OffSet 输出
2012-05-11 07:37:19.200
2012-05-11 18:01:19.200 以下是相关的函数
CREATE FUNCTION [dbo].[TimeFromDecimal]
(
@dTime decimal(28,4),
@dateTime datetime
)
RETURNS
datetime
AS
BEGIN
-- Add the T-SQL statements to compute the return value here
DECLARE @iHour int, @iMin int, @iSec int, @imSec int
set @iHour = @dTime
--concatenate hours
set @iMin = ((@dTime - @iHour)*60.)
--subtract hours and convert to mins
--select mins and secs in dec form subtract mins and convert remainder to seconds:
set @iSec = (((@dTime-@iHour)*60-@iMin)*60)
set @imSec = ((((@dTime-@iHour)*60-@iMin)*60) - @iSec)* 1000
if @iHour > 0
begin
set @dateTime = dateadd(hh, @ihour, @dateTime)
end
else --will subtract into the day before
begin
set @dateTime = dateadd(d, 1, @datetime)
set @dateTime = dateadd(hh, @ihour, @dateTime)
end
set @dateTime = dateadd(mi, @imin, @dateTime)
set @dateTime = dateadd(s, @isec, @dateTime)
set @dateTime = dateadd(ms, @imsec, @dateTime)
-- Return the result of the function
RETURN
@dateTime
END CREATE FUNCTION [dbo].[udf_calcDayofYear]
(
@dDate datetime
)
RETURNS int
AS
BEGIN
DECLARE @N1 int, @N2 int, @N3 int, @N int
set @N1 = floor(275 * (select month(@dDate)) / 9)
set @N2 = floor(((select month(@dDate)) + 9) / 12)
set @N3 = (1 + floor(((select year(@dDate)) -
4 * floor((select year(@dDate)) / 4) + 2) / 3))
set @N = @N1 - (@N2 * @N3) + (select day(@dDate)) - 30
RETURN @N
END CREATE FUNCTION [dbo].[udf_getUTCOffSet]()
RETURNS int
AS
BEGIN
RETURN DATEDIFF(hh, GETUTCDATE(), GETDATE())
END 实现中描述的存储过程
CREATE PROCEDURE [dbo].[DetermineSunrise]
@localDate datetime,
@latitude decimal(18,3),
@longitude decimal(18,3),
@GMT int
AS
DECLARE @ZENITH decimal(18,3)
SET @ZENITH = 90.83
/*
ZENITH
offical = 90 degrees 50'
civil = 96 degrees
nautical = 102 degrees
astronomical = 108 degrees
*/
--SUNSET
--Calculate the day of the year
DECLARE @DayOfYear int
SET @DayOfYear = dbo.udf_calcDayofYear(@localDate)
-- convert the longitude to hour value and calculate an approximate time
DECLARE @lngHour decimal(18,3)
DECLARE @t decimal(18,3)
SET @lngHour = @longitude / 15
SET @t = @DayOfYear + ((6 - @lngHour) / 24)
-- calculate the Sun's mean anomaly
DECLARE @Mean decimal(18,3)
SET @Mean = (0.9856 * @t) - 3.289
-- calculate the Sun's true longitude
DECLARE @SunLon decimal(18,3)
SET @SunLon = @Mean + (1.916 * sin(RADIANS(@Mean))) +
(0.020 * sin(2 * RADIANS(@Mean))) + 282.634 - 360
---- calculate the Sun's right ascension
DECLARE @SunRightAsention decimal(18,3)
SET @SunRightAsention = DEGREES(atan(0.91764 * tan(RADIANS(@SunLon))))
---- right ascension value needs to be in the same quadrant as L
DECLARE @Lquadrant decimal(18,3)
DECLARE @Rquadrant decimal(18,3)
SET @Lquadrant = (floor( @SunLon/90)) * 90
SET @Rquadrant = (floor(@SunRightAsention/90)) * 90
SET @SunRightAsention = @SunRightAsention + (@Lquadrant - @Rquadrant)
----right ascension value needs to be converted into hours
SET @SunRightAsention = @SunRightAsention / 15
---- calculate the Sun's declination
DECLARE @sinDec decimal(18,3)
DECLARE @cosDec decimal(18,3)
SET @sinDec = 0.39782 * sin(RADIANS(@SunLon))
SET @cosDec = cos(asin(@sinDec))
---- calculate the Sun's local hour angle
DECLARE @cosH decimal(18,3)
SET @cosH = (cos(radians(@ZENITH)) - (@sinDec *
sin(radians(@latitude)))) / (@cosDec * cos(radians(@latitude)))
----if (cosH > 1)
---- the sun never rises on this location (on the specified date)
----if (cosH < -1)
---- the sun never sets on this location (on the specified date)
DECLARE @H decimal(18,3)
SET @H = 360 - degrees(ACOS(@cosH))
SET @H = @H / 15
---- calculate local mean time of rising/setting
DECLARE @MeanTime decimal(18,3)
SET @MeanTime = @H + @SunRightAsention - (0.06571 * @t) - 6.622
DECLARE @UT decimal(18,3)
SET @UT = @MeanTime - @lngHour
SELECT dbo.TimeFromDecimal(@UT + @GMT, @localDate) CRATE PROCEDURE [dbo].[DetermineSunset]
@localDate datetime,
@latitude decimal(18,3),
@longitude decimal(18,3),
@GMT int
AS
DECLARE @ZENITH decimal(18,3)
SET @ZENITH = 90.83
/*
ZENITH
offical = 90 degrees 50'
civil = 96 degrees
nautical = 102 degrees
astronomical = 108 degrees
*/
--SUNSET
--Calculate the day of the year
DECLARE @DayOfYear int
SET @DayOfYear = dbo.udf_calcDayofYear(@localDate)
-- convert the longitude to hour value and calculate an approximate time
DECLARE @lngHour decimal(18,3)
DECLARE @t decimal(18,3)
SET @lngHour = @longitude / 15
SET @t = @DayOfYear + ((18 - @lngHour) / 24)
-- calculate the Sun's mean anomaly
DECLARE @Mean decimal(18,3)
SET @Mean = (0.9856 * @t) - 3.289
-- calculate the Sun's true longitude
DECLARE @SunLon decimal(18,3)
SET @SunLon = @Mean + (1.916 * sin(RADIANS(@Mean))) +
(0.020 * sin(2 * RADIANS(@Mean))) + 282.634 - 360
---- calculate the Sun's right ascension
DECLARE @SunRightAsention decimal(18,3)
SET @SunRightAsention = DEGREES(atan(0.91764 * tan(RADIANS(@SunLon))))
---- right ascension value needs to be in the same quadrant as L
DECLARE @Lquadrant decimal(18,3)
DECLARE @Rquadrant decimal(18,3)
SET @Lquadrant = (floor( @SunLon/90)) * 90
SET @Rquadrant = (floor(@SunRightAsention/90)) * 90
SET @SunRightAsention = @SunRightAsention + (@Lquadrant - @Rquadrant)
----right ascension value needs to be converted into hours
SET @SunRightAsention = @SunRightAsention / 15
---- calculate the Sun's declination
DECLARE @sinDec decimal(18,3)
DECLARE @cosDec decimal(18,3)
SET @sinDec = 0.39782 * sin(RADIANS(@SunLon))
SET @cosDec = cos(asin(@sinDec))
---- calculate the Sun's local hour angle
DECLARE @cosH decimal(18,3)
SET @cosH = (cos(radians(@ZENITH)) - (@sinDec *
sin(radians(@latitude)))) / (@cosDec * cos(radians(@latitude)))
----if (cosH > 1)
---- the sun never rises on this location (on the specified date)
----if (cosH < -1)
---- the sun never sets on this location (on the specified date)
DECLARE @H decimal(18,3)
SET @H = degrees(ACOS(@cosH))
SET @H = @H / 15
---- calculate local mean time of rising/setting
DECLARE @MeanTime decimal(18,3)
SET @MeanTime = @H + @SunRightAsention - (0.06571 * @t) - 6.622
DECLARE @UT decimal(18,3)
SET @UT = @MeanTime - @lngHour
SELECT dbo.TimeFromDecimal(@UT + @GMT, @localDate)
希望这些信息对您有所帮助。祝您编码愉快！