按纬度/经度进行半径搜索

2022-01-14 00:00:00 database geolocation sql-server-2008 sql-server

我使用 mysql 找到了很多关于这个问题的答案，但我无法将任何内容转换为 ms sql 2008 可以使用的查询.我在数据库中的每一行都有一个经度和纬度列.我将有一个用户所在位置的纬度和经度.我希望能够找到距离用户纬度/经度 x 英里范围内的所有行.此外，当尝试使用我在 SO 上找到的其他查询时，我不断收到错误 - 'pow' is not a known built-in function name. 这很奇怪，因为我很确定我有pow 之前在 sql 2008 中使用过.对此的任何帮助将不胜感激.到目前为止，这是最接近的.

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 @Latitude float = <somevalue>, @Longitude float = <somevalue>;
declare @point geography = geography::Point(@Latitude, @Longitude, 4326);
declare @distance int = <distance in meters>;

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

相关文章