I need help with a architectural problem that im working with. The user enters a position and a radius (e.g. distance). The software searches in a (giant = couple of 100k posts) database table for posts where the users location and the "posts" distance to each other is less than the entered distance.
我需要帮助处理与之合作的架构问题。用户输入位置和半径(例如距离)。该软件在(巨型=几个100k帖子)数据库表中搜索用户位置和彼此的“帖子”距离小于输入距离的帖子。
It's kind of hard for me to explain, but imagine a table with two posts, point a and point c, point U is the user location. The user has entered a position and a radius, and the position and radius for a and c is predefined (stored in a database).
这对我来说很难解释,但想象一下有两个帖子的表,点a和点c,点U是用户位置。用户已输入位置和半径,并且a和c的位置和半径是预定义的(存储在数据库中)。
In this case i would only be interested in the point A, because the two areas intersect with each other. How should i transform this into doing in a database with a couple of hundred thousand posts in an effective way? In the database i shall store longitude,latitude and radius.
在这种情况下,我只对A点感兴趣,因为这两个区域相互交叉。我应该如何将其转换为在数据库中以有效的方式进行数十万个帖子?在数据库中,我将存储经度,纬度和半径。
1 个解决方案
#1
2
Depends on which database server you're using, but look into the GIS capabilities that might be included. For example, MS SQL Server 2008 has a built-in geometry type, and PostgreSQL has PostGIS. Oracle has something like this too. Anyhow - these native GIS formats come with spacial querying functions that do the sort of thing you're talking about - searching for matches within given distances, etc... It is pretty simple to accomplish once to switch to the proper datatype.
取决于您正在使用的数据库服务器,但请查看可能包含的GIS功能。例如,MS SQL Server 2008具有内置几何类型,PostgreSQL具有PostGIS。 Oracle也有类似的东西。无论如何 - 这些本地GIS格式带有空间查询功能,可以完成你所讨论的事情 - 在给定距离内搜索匹配等等......一旦切换到正确的数据类型,完成一次非常简单。
edit
Since you're using SQL 2008, and your data is lat/long, I suggest the "geography" rather than the "geometry" datatype. Take a look here: http://msdn.microsoft.com/en-us/library/cc280766.aspx
由于您使用的是SQL 2008,并且您的数据是纬度/经度,因此我建议使用“地理”而不是“几何”数据类型。看看这里:http://msdn.microsoft.com/en-us/library/cc280766.aspx
#1
2
Depends on which database server you're using, but look into the GIS capabilities that might be included. For example, MS SQL Server 2008 has a built-in geometry type, and PostgreSQL has PostGIS. Oracle has something like this too. Anyhow - these native GIS formats come with spacial querying functions that do the sort of thing you're talking about - searching for matches within given distances, etc... It is pretty simple to accomplish once to switch to the proper datatype.
取决于您正在使用的数据库服务器,但请查看可能包含的GIS功能。例如,MS SQL Server 2008具有内置几何类型,PostgreSQL具有PostGIS。 Oracle也有类似的东西。无论如何 - 这些本地GIS格式带有空间查询功能,可以完成你所讨论的事情 - 在给定距离内搜索匹配等等......一旦切换到正确的数据类型,完成一次非常简单。
edit
Since you're using SQL 2008, and your data is lat/long, I suggest the "geography" rather than the "geometry" datatype. Take a look here: http://msdn.microsoft.com/en-us/library/cc280766.aspx
由于您使用的是SQL 2008,并且您的数据是纬度/经度,因此我建议使用“地理”而不是“几何”数据类型。看看这里:http://msdn.microsoft.com/en-us/library/cc280766.aspx