点上圆（经度/纬度）的.NET / SQL [英] Point on Circle (Longitude/Latitude) in .NET/SQL

问题描述

我有以下问题： 我有一个经度/纬度值的表，并与项目-id和经度/纬度/半径（圈）另一个表。

I've got the following problem: I have a table with long/lat values and another table with a project-id and long/lat/radius (circle).

现在我需要找出哪些项目从第一个表匹配的经度/纬度值。

Now I need to find out which project matches the long/lat values from the first table.

有没有一种简单的方法来做到这一点？

Is there an easy way to do that?

推荐答案

有多种算法来计算球的距离，但是我们用下面的：

There are multiple algorithms to calculate distance on sphere, but we use following:

create function GetDistance(
    @latitudeFrom decimal(30,10), 
    @longitudeFrom decimal(30,10),
    @latitudeTo decimal(30,10), 
    @longitudeTo decimal(30,10)
)
RETURNS float
AS
BEGIN

DECLARE @distance float

    SET @distance = ROUND(6378.137 * ACOS(
            convert(decimal(30,10), 
            (SIN(RADIANS(@latitudeFrom)) * SIN(RADIANS(@latitudeTo))) +
            (COS(RADIANS(@latitudeFrom)) * COS(RADIANS(@latitudeTo)) *
             COS(RADIANS(@longitudeTo) - RADIANS(@longitudeFrom))))), 15)

 RETURN @distance
 END
 go

（其中6378.137 - 为地球半径）

(where 6378.137 - is Earth's radius)

所以现在当你可以计算出地球上2点之间的距离，你可以建立查询

So now when you can calculate distance between 2 points on Earth you can build query

 select * 
     from Table1, Project 
     where dbo.GetDistance(
           Table1.lat, Table1.lon, 
           Project.lat, Project.lon) < @YouRadius

在这里@YouRadius - 你的圈子的参数半径

where @YouRadius - parameterized radius of your circle

这篇关于点上圆（经度/纬度）的.NET / SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！