在地理半径内是否存在一个点 - SQL Server 2008

Question

在地理半径内是否存在一个点 - SQL Server 2008

sql-serversql-server-2008sql-server-2008-r2proximitysqlgeography

6

给定以下数据，是否有可能，并且如果有的话，最有效的方法是确定第一张表中的位置“Shurdington”是否包含在第二张表中任何位置的给定半径内。

GeoData列属于“地理学”类型，因此可以使用SQL Server的空间功能以及使用纬度和经度。

Location      GeoData       Latitude    Longitude
===========================================================
Shurdington   XXXXXXXXXX    51.8677979  -2.113189

ID  Location            GeoData     Latitude    Longitude   Radius
==============================================================================
1000    Gloucester      XXXXXXXXXX  51.8907127  -2.274598   10
1001    Leafield        XXXXXXXXXX  51.8360519  -1.537438   10
1002    Wotherton       XXXXXXXXXX  52.5975151  -3.061798   5
1004    Nether Langwith XXXXXXXXXX  53.2275276  -1.212108   20
1005    Bromley         XXXXXXXXXX  51.4152069  0.0292294   10

任何帮助都将不胜感激。

- Nathan

3个回答

1

你需要计算两点之间的距离，然后将此距离与给定半径进行比较。

对于计算短距离，您可以使用维基百科-地理距离-球形地球投影到平面中提供的公式，该公式声称“非常快速，并为小距离生成相当准确的结果”。

根据该公式，您需要经纬度差和平均纬度。

with geo as (select g1.id, g1.latitude as lat1, g1.longitude as long1, g1.radius,
                    g2.latitude as lat2, g2.longitude as long2
             from geography g1
             join geography g2 on g2.location = 'shurdington'
                               and g1.location <> 'shurdington')
     base as (select id,
                     (radians(lat1) - radians(lat2)) as dlat,
                     (radians(long1) - radians(long2)) as dlong,
                     (radians(lat1) + radians(lat2)) / 2 as mlat, radius
              from geo)
     dist as (select id,
                     6371.009 * sqrt(square(dlat) + square(cos(mlat) * dlong)) as distance,
                     radius
              from base)
select id, distance
from dist
where distance <= radius

我使用 with select 作为中间步骤来保持计算的“可读性”。

- Olaf Dietsche

为什么要自己动手，既然“使用SQL Server的空间特性是一个选项”？ - AakashM

@AakashM 因为它是一个有趣的练习。 - Olaf Dietsche

1

如果您想自己进行计算，可以使用基于毕达哥拉斯的等距近似方法。公式如下：

var x =（lon2-lon1）* Math.cos（（lat1 + lat2）/ 2）; var y =（lat2-lat1）; var d = Math.sqrt（x * x + y * y）* R;

就SQL而言，这应该给出包含第1个表中条目的半径范围内的第2个表中的这些位置：

SELECT *
FROM Table2 t2
WHERE EXISTS (
 SELECT 1 FROM Table1 t1
 WHERE 
  ABS (
  SQRT (
    (SQUARE((RADIANS(t2.longitude) - RADIANS(t1.longitude)) * COS((RADIANS(t2.Latitude) + RADIANS(t1.Latitude))/2))) +
    (SQUARE(RADIANS(t1.Latitude) - RADIANS(t2.Latitude)))
    ) * 6371 --Earth radius in km, use 3959 for miles
    )
    <= t2.Radius
)

请注意，这并不是最准确的方法，但很可能足够好。如果您正在查看跨越地球的距离，您可能希望搜索“haversine”公式。

值得一提的是，可以将此与Paddy的解决方案进行比较，以查看它们的一致性和哪个表现更佳。

- NigelK

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Morphed · Accepted Answer

创建数据

CREATE TABLE #Data (
    Id int,
    Location nvarchar(50),
    Latitude decimal(10,5),
    Longitude decimal(10,5),
    Radius int
)

INSERT #Data (Id,Location,Latitude,Longitude,Radius) VALUES 
(1000,'Gloucester', 51.8907127 ,-2.274598  , 20), -- Increased to 20
(1001,'Leafield', 51.8360519 , -1.537438  , 10),
(1002,'Wotherton', 52.5975151,  -3.061798  , 5),
(1004,'Nether Langwith', 53.2275276 , -1.212108  , 20),
(1005,'Bromley', 51.4152069 , 0.0292294  , 10)

测试

将您感兴趣的点声明为POINT

DECLARE @p GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-2.113189 51.8677979)', 4326);

查找一个点是否在另一个点的半径范围内：

-- First create a Point.
DECLARE @point GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT(-2.27460 51.89071)', 4326);
-- Buffer the point (meters) and check if the 1st point intersects
SELECT @point.STBuffer(50000).STIntersects(@p)

将所有内容合并为一个查询：

select  *,
        GEOGRAPHY::STGeomFromText('POINT('+ 
            convert(nvarchar(20), Longitude)+' '+
            convert( nvarchar(20), Latitude)+')', 4326)
        .STBuffer(Radius * 1000).STIntersects(@p) as [Intersects]
from    #Data

提供：

Id      Location        Latitude    Longitude   Radius  Intersects
1000    Gloucester      51.89071    -2.27460    20      1
1001    Leafield        51.83605    -1.53744    10      0
1002    Wotherton       52.59752    -3.06180    5       0
1004    Nether Langwith 53.22753    -1.21211    20      0
1005    Bromley         51.41521    0.02923     10      0

关于效率问题：在正确建立索引的情况下，SQL的空间索引能够十分迅速。