如何使用Sql搜索(预定义)矩形内的位置(纬度/经度)? [英] How to search (predefined) locations (Latitude/Longitude) within a Rectangular, using Sql?

问题描述

我有一个矩形位置的北，东，南和西值，如何只搜索该矩形位置范围内的那些位置(纬度/经度)?

I have the North, East, South and West values of a Rectangular location, how can I search only those locations (Latitude/Longitude) that are within the bounds of that Rectangular location?

请注意，我需要在数据库中搜索位置(纬度/经度).我需要在数据库中找到给定矩形范围内的位置，然后在地图上为其绘制标记.

Please note that I need to search the Locations (Latitude/Longitude) within a database. I need to find the locations in the database which are in the bounds of a given rectangular and I will draw markers for them on the map.

我不是在谈论 Google搜索，搜索API已经支持传递矩形边界以过滤搜索.

I am not talking about Google search, the APIs for search already support passing the rectangular bound to filter the search.

googlemaps API可以为我做到这一点，例如:

The googlemaps APIs can do this for me like:

var request = {
    bounds: myMapObject.getBounds(), // Search only within the Bound of the Map
    query: someTextToFind
};

OR

var isWithInMapsBounds = myMapObject.getBounds().contains(myLocation);

但是我需要在从数据库获取位置的同时这样做，我的意思是在服务器端.

有人可以指导我正确的方向，如何实现此功能吗?

Can someone guide me in the right direction, how to achieve this functionality?

我想在数据库中搜索；所以我需要一个SQL解决方案.

I want to search within database; so I need an SQL solution.

推荐答案

DECLARE @Bounds AS Geography = GEOMETRY::STGeomFromText('polygon-wkt-here',0)

SELECT columns-list
FROM   table
WHERE  1 = @Bounds.STIntersects(table.point-column)

这里黑暗的人的简短说明:为什么不能将点位置与边界框的北/南/东/西坐标相比较:

Quick note for those that are in the dark here about why you can't just compare your point location to the north/south/east/west coordinates of your bounding box:

边界框可能不是经纬度对齐的(例如，其北/南/东/西线可能不遵循经纬度/长轴). 如果您在纬度/经度地图投影上绘制平面矩形，就会发生这种情况.该点越靠近边界框的边缘，并且距赤道越远，失真将越明显.

The bounding box may not be lat/long-aligned (eg. its north/south/east/west lines may not follow lat/long axis). This will happen if you draw a planar rectangle on a Lat/Long map projection. The distortion will be more apparent the closer to the edge of the bounding box the point is, and the farther from the Equator.

使用Geography数据类型的第二个原因是它提供了一种统一的方法来对 ANY 多边形(不仅仅是矩形)执行此类操作.因此，现在您可以比较框中是否有单个点，或者查找邮政编码中的所有地址，并且所有地址都使用相同的代码.

The second reason to use the Geography data types is that it provides a uniform method of performing these kinds of operations for ANY polygon, not just rectangles. So now you can compare if a single point is within your box, or find all the addresses in a zipcode, all with the same piece of code.

这篇关于如何使用Sql搜索(预定义)矩形内的位置(纬度/经度)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！