问题描述
我已经找到一堆答案这个问题使用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;
这篇关于按纬度/经度搜索半径的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!