我有一个Django/PostgreSQL应用程序,可以显示哪些用户最接近特定用户。它使用PostGIS 2.0 KNN(K最近邻)<->操作符在ORDER BY子句中列出用户,最接近的用户排在前面。根据我的初始数据集,我发现两个搜索结果顺序不正确(所有距离都是从加利福尼亚州洛杉矶测量的)。
会员名称只是Django的contrib.auth.models User类中的用户名列。包含几何信息的UserAccount类定义如下:
Member, City, State, Distance (miles)
user1, North Las Vegas, NV, 239
user2, Phoenix, AZ, 365
user3, Provo, UT, 568
user4, Twin Falls, ID, 630
user5, Albuquerque, NM, 673
user6, Portland, OR, 828
user7, Bozeman, MT, 896
user8, Seattle, WA, 962
user9, Boulder, CO, 834 <- Out of order!
user10, Laramie, WY, 862 <- Out of order!
user11, Naperville, IL, 1756
会员名称只是Django的contrib.auth.models User类中的用户名列。包含几何信息的UserAccount类定义如下:
class UserAccount(models.Model):
user = models.OneToOneField(User, primary_key=True, unique=True)
address_line_1 = models.CharField(max_length=30)
address_line_2 = models.CharField(max_length=30, blank=True)
city = models.CharField(max_length=30)
region = models.CharField(max_length=30, blank=True)
postal_code = models.CharField(max_length=10, blank=True)
country = models.ForeignKey('Country')
measurement_sys = models.CharField(max_length=5) # US or Metric
# User's home (default) and current longitude and latitude
home_lon = models.FloatField(default=0.0)
home_lat = models.FloatField(default=0.0)
current_lon = models.FloatField(default=0.0)
current_lat = models.FloatField(default=0.0)
# GeoDjango-specific fields
home_point = models.PointField(srid=4326)
current_point = models.PointField(srid=4326)
objects = models.GeoManager()
这是我Django视图中的查询:
def members(request, template):
"""View all members of the website."""
uid = request.session['uid'] # PK from User table
# Get the current user's lon/lat and measurement system
try:
ua = UserAccount.objects.get(user_id=uid)
lon = ua.current_lon
lat = ua.current_lat
measurement_sys = ua.measurement_sys
except UserAccount.DoesNotExist as e:
return HttpResponseRedirect(reverse('unable-to-display-members'))
# Define the proximity query.
if measurement_sys == 'US':
multiplier = 0.000621371 # Convert to miles
else:
multiplier = 0.001 # Convert to kilometers
query = "SELECT \
ua.user_id, \
au.username, \
ua.city, \
ua.region, \
ST_Distance( \
ua.current_point::geography, \
ST_GeographyFromText( \
'SRID=4326;POINT(" \
+ str(lon) \
+ " " \
+ str(lat) + \
")' \
) \
)*" + str(multiplier) + " AS distance \
FROM \
user_account ua \
INNER JOIN \
auth_user au \
ON (ua.user_id = au.id) \
WHERE ua.user_id != %s \
ORDER BY \
ua.current_point::geometry \
<-> \
'SRID=4326;POINT(" + str(lon) + " " + str(lat) + ")'::geometry \
LIMIT 250;"
# Run the proximity query
raw_queryset = UserAccount.objects.raw(query, [uid])
# Paginate results
user_list = [user for user in raw_queryset]
list_size = len(list(user_list))
paginator = Paginator(user_list, 10, 4)
paginator._count = list_size
page = request.GET.get('page')
try:
users = paginator.page(page)
except PageNotAnInteger:
users = paginator.page(1)
except EmptyPage:
users = paginator.page(paginator.num_pages)
return render(request, template, {'users': users})
在我的查询中是否有任何错误?KNN运算符是否有时会“失误”并返回一些错序的结果?我问这个是因为当我尝试从我的表中取出两个错序的记录,然后添加额外的记录以使用户的地址更远(即在IL、LA、MI、NC、PA、NY和ME),所有的结果都是按正确顺序排列的。
顺便说一下,我的输入位于这里。
谢谢!