使用纬度和经度返回 SQL Server 2008 中两个位置之间的距离

发布于 2024-12-05 20:10:06 字数 820 浏览 2 评论 0原文

我们有一张表，其中包含地点及其纬度和经度。

我们正在尝试在 SQL Server 2008 中创建一个函数，以使用特定的纬度和经度作为中心点列出接下来 25 公里内的地点。

我想知道这是否是启动和测试我们的功能并获取中心点（当前位置）和目标位置（@latitude/@longitude）之间的当前距离的好方法：

ALTER FUNCTION [dbo].[GetDistanceFromLocation]
(   
    @myCurrentLatitude float,
    @myCurrentLongitude float,
    @latitude float,
    @longitude float
)
RETURNS int
AS
BEGIN
    DECLARE @radiusOfTheEarth int 
    SET @radiusOfTheEarth = 6371--km

    DECLARE @distance int
    SELECT @distance = ( @radiusOfTheEarth 
        * acos( cos( radians(@myCurrentLatitude) ) 
        * cos( radians( @latitude ) ) 
        * cos( radians( @longitude ) - radians(@myCurrentLongitude) ) + sin( radians(@myCurrentLatitude) ) 
        * sin( radians( @latitude ) ) ) )

    RETURN @distance

END

它是正确的还是我们遗漏了一些东西？

原文

We have a table with places and their latitudes and longitudes.

We are trying to create a function in SQL Server 2008 to list places within next 25 kilometers using a specific latitude and longitude as centre point.

I was wandering if this is a good way to start and test our function and getting current distance between a centre point (current location) and a target location (@latitude/@longitude):

ALTER FUNCTION [dbo].[GetDistanceFromLocation]
(   
    @myCurrentLatitude float,
    @myCurrentLongitude float,
    @latitude float,
    @longitude float
)
RETURNS int
AS
BEGIN
    DECLARE @radiusOfTheEarth int 
    SET @radiusOfTheEarth = 6371--km

    DECLARE @distance int
    SELECT @distance = ( @radiusOfTheEarth 
        * acos( cos( radians(@myCurrentLatitude) ) 
        * cos( radians( @latitude ) ) 
        * cos( radians( @longitude ) - radians(@myCurrentLongitude) ) + sin( radians(@myCurrentLatitude) ) 
        * sin( radians( @latitude ) ) ) )

    RETURN @distance

END

Is it correct or we are missing something?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

素罗衫 2024-12-12 20:10:06

看来您正在使用大圆距离公式，该公式可能足够准确你，尽管你必须对此做出判断。

如果您想检查公式的结果，可以使用地理位置数据类型：

declare @geo1 geography = geography::Point(@lat1, @long1, 4326),
        @geo2 geography = geography::Point(@lat2, @long2, 4326)

select @geo1.STDistance(@geo2)

并且由于您正在执行邻近搜索，您可能需要进一步研究geography 数据类型。

It looks like you are using the great-circle distance formula, which is probably accurate enough for you, although you'll have to be the judge of that.

If you want to check the results of your formula, you can use the geography data type:

declare @geo1 geography = geography::Point(@lat1, @long1, 4326),
        @geo2 geography = geography::Point(@lat2, @long2, 4326)

select @geo1.STDistance(@geo2)

and since you are doing a proximity search, you may want to investigate the geography data type further.

回复收藏 0 原文

花辞树 2024-12-12 20:10:06

这有效吗？

CREATE FUNCTION [dbo].[GetDistanceFromLocation]
(   
    @CurrentLatitude float,
    @CurrentLongitude float,
    @latitude float,
    @longitude float
)
RETURNS int
AS
BEGIN
    DECLARE @geo1 geography = geography::Point(@lat1, @long1, 4268), 
            @geo2 geography = geography::Point(@lat2, @long2, 4268)

    DECLARE @distance int
    SELECT @distance = @geo1.STDistance(@geo2) 

    RETURN @distance

END

谢谢！

Would this be valid?

CREATE FUNCTION [dbo].[GetDistanceFromLocation]
(   
    @CurrentLatitude float,
    @CurrentLongitude float,
    @latitude float,
    @longitude float
)
RETURNS int
AS
BEGIN
    DECLARE @geo1 geography = geography::Point(@lat1, @long1, 4268), 
            @geo2 geography = geography::Point(@lat2, @long2, 4268)

    DECLARE @distance int
    SELECT @distance = @geo1.STDistance(@geo2) 

    RETURN @distance

END

Thanks!

回复收藏 0 原文

~没有更多了~