我有一个带有位置表的 Microsoft SQL Server 数据库。每个位置都有其地址以及纬度和经度坐标。
在我的应用程序中，用户可以输入邮政编码，我们会返回附近位置的列表。
这是我的方法。
a）使用邮政编码数据库，我在经纬度上搜索邮政编码（这是中心点）。
b）我运行这样的搜索
SELECT Position_ID, distance(pos_lon,pos_lat,zip_lon,zip_lat) dist
FROM Positions
ORDER BY dist
“距离”是一个计算两点之间距离的函数。
问题是，随着我的位置数据库的增加，运行这些搜索的时间开始增加。
有更好的方法吗？
I have a Microsoft SQL Server database with a table of Locations. Each location has its address and latitude and longitude coordinates.
In my application, the user can input a zipcode and we return a list of close by locations.
This is my approach.
a) Using a zipcode DB I search the lat,lon for the zipcode (this is the center point).
b) I run a search like this
SELECT Position_ID, distance(pos_lon,pos_lat,zip_lon,zip_lat) dist
FROM Positions
ORDER BY dist
"distance" is a function that calculates the distance between two points.
The problem is that as my location DB increases the time to run these searches is starting to grow.
Is there a better approach?
发布评论
评论（3）
如果您使用的是 SQL Server 2008，您可能需要查看
geography列类型、STDistance函数和空间索引。If you're using SQL Server 2008, you probably want to look into the
geographycolumn type, theSTDistancefunction and spatial indexes.我会计算指定距离处邮政编码周围的框，以获得四个角的纬度/经度值。然后对您的位置值进行简单比较，以选择那些落在正方形内的值。这样，您就不必为每次搜索计算从邮政编码到数据库中每个点的距离。
I would do a calculation of the box surrounding your zip code at the specified distance to get the lat/lon values for the four corners. Then do a simple comparison of your position values to select those which fall inside the square. This way, you don't have to calculate the distance from your zip code to every point in your db for every search.
SQL Server 是哪个版本？如果2008年看看空间类型。
http://www.microsoft.com/sqlserver/2008/ en/us/spatial-data.aspx
编辑：还用 where 限制该查询可能会有所帮助，因为您可能不想在任何特定方向上走得太远。
Which version of SQL server? If 2008 have a look at the spatial type.
http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx
EDIT: Also limiting that query with a where would probably help as you likely don't want to go to far in any particular direction.