django - PostGIS Nearest Neighbor Search Results Out of Order? -

i have django/postgresql application shows users nearest particular user. uses postgis 2.0 knn (k nearest neighbors) <-> operator in order clause list users, nearest first. i've found initial dataset 2 of search results out of order (all distances measured los angeles, ca):

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 

the member name username column django's contrib.auth.models user class. useraccount class contains geometry information defined follows:

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)  # or metric      # user's home (default) , current longitude , 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() 

here's query in django view:

def members(request, template):     """view members of website."""     uid = request.session['uid']   # pk user table      # current user's lon/lat , 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 e:         return httpresponseredirect(reverse('unable-to-display-members'))      # define proximity query.     if measurement_sys == 'us':         multiplier = 0.000621371  # convert miles     else:         multiplier = 0.001  # convert 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) + " distance \             \                 user_account ua \                 inner join \                 auth_user au \                 on (ua.user_id = au.id) \             ua.user_id != %s \             order \                 ua.current_point::geometry \                 <-> \                 'srid=4326;point(" + str(lon) + " " + str(lat) + ")'::geometry \             limit 250;"      # run proximity query     raw_queryset = useraccount.objects.raw(query, [uid])      # paginate results     user_list = [user 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}) 

am doing wrong in query? can knn operator "hiccup" , return results out of order? ask because when tried taking 2 out-of-order records out of table adding additional records users addresses in farther away (i.e. in il, la, mi, nc, pa, ny , me), results in correct order.

by way, inputs located here.

thanks!

updated answer:

postgis has 2 approximate solutions knn neighbors functionality, since september 2011:

• using <-> operator, nearest neighbour using centers of bounding boxes calculate inter-object distances.
• using <#> operator, nearest neighbour using bounding boxes calculate inter-object distances.

your problem is, both approximate, not perfect. so, if want best 250 results, can use of them retrieve example best 1000 results , order same results st_distance , limit 250 best 250 results out of approximate 1000.

example:

select *      (select *,st_distance(current_point::geography, 'srid=4326;point(" + str(lon) + " " + str(lat) + ")'::geography ) st_dist     ua     order current_point::geometry <-> 'srid=4326;point(" + str(lon) + " " + str(lat) + ")'::geometry      limit 1000) s     order st_dist limit 250;