按纬度/经度搜索半径 [英] radius search by latitude / longitude

问题描述

我已经找到一堆答案这个问题使用mysql，但我不能将任何转换为​​查询ms sql 2008可以使用。我有一个经度和纬度列的数据库中的每一行。我将有一个纬度和经度的用户在哪里。我想要能够找到距离用户的纬度/经度x英里内的所有行。另外，当试图使用其他查询时，我发现，我一直得到的错误 - 'pow'不是一个公认的内置函数名称这是奇怪的， m相当肯定，我在sql 2008中使用 pow 。任何帮助，这将非常感谢。到目前为止，这是最接近的可能出现。

I have found a bunch of answers for this question using mysql , but I wasn't able to convert anything into a query ms sql 2008 can use. I have a longitude and latitude column for each row in the database. I am going to have a latitude and longitude for where the user is. I want to be able to find all rows that are within x miles from the user's latitude/longitude. Also when trying to use other queries I found on SO I keep getting the error - 'pow' is not a recognized built-in function name. which is weird , because I'm pretty sure that I have used pow before in sql 2008. Any help with this would be greatly appreciated. So far this is the closest could come up with.

select * from tbl_MyTable
WHERE (
POW( ( 69.1 * ( Longitude - @longitude ) * cos( @latitude / 57.3 ) ) , 2 ) + POW( ( 69.1 * ( Latitude - @latitude ) ) , 2 )
) < ( 5 *5 );

推荐答案

由于你使用的是SQL 2008，本地地理空间能力。

Since you're on SQL 2008, consider using the native geospatial capabilities. You can do fancy things like:

• 创建一个表示您的点的地理类型的持久计算列。


• 在计算列上创建空间索引。这将使 yourPoint.STDistance（@otherPoint）< = @distance 有效

• Create a persisted computed column of geography type that represents your point.
• Create a spatial index on the computed column. This will make things like yourPoint.STDistance(@otherPoint) <= @distance efficient

像这样：

alter table [yourTable] add [p] as geography::Point(Latitude, Longitude, 4326) persisted;
create spatial index [yourSpatialIndex] on [yourTable] ([p])

declare @Longitude float = <somevalue>, @Latitude float = <somevalue>;
declare @point = geography::Point(@Latitude, @Longitude, 4326);
declare @dist = <distance in meters>;

select * from [yourTable] where @point.STDistance([p]) <= @distance;

这篇关于按纬度/经度搜索半径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！