Thanks for the direction, AT. Below is my final code in case anyone else
encounters a similar problem:
class LocationManager(models.Manager):
'''
'''
def nearby_locations(self, latitude, longitude, proximity, category):
'''
'''
cursor = connection.cursor()
if settings.DATABASES['default']['ENGINE'] ==
'django.db.backends.sqlite3':
# sqlite doesn't natively support math functions, so add them
connection.connection.create_function('acos', 1, math.acos)
connection.connection.create_function('cos', 1, math.cos)
connection.connection.create_function('radians', 1,
math.radians)
connection.connection.create_function('sin', 1, math.sin)
sql = """SELECT *, (3959 * acos(cos(radians(%s)) *
cos(radians(latitude)) * cos(radians(longitude) -
radians(%s)) +
sin(radians(%s)) * sin(radians(latitude))))
AS distance FROM locations_location
WHERE active='1' AND category LIKE %s
GROUP BY id HAVING distance < %s
ORDER BY distance ASC"""
# use params list instead of string formatting to avoid SQL
injection
cursor.execute(sql, [latitude, longitude, latitude, category,
proximity])
data = []
for row in cursor.fetchall():
# model field names and values
record = dict(zip([col[0] for col in cursor.description], row))
# dynamically calculated distance
record['distance'] = row[-1]
data.append(record)
return data
On Wed, Jul 25, 2012 at 2:32 PM, Andre Terra <andrete...@gmail.com> wrote:
> Please read the following bits of documentation:
>
>
> https://docs.djangoproject.com/en/dev/topics/db/sql/#performing-raw-sql-queries
>
> https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra
>
>
>
> Cheers,
> AT
>
>
> On Tue, Jul 24, 2012 at 9:03 PM, jondbaker
> <jonathandavidba...@gmail.com>wrote:
>
>> I've implemented the spherical law of cosines to aid in proximity-based
>> searching. Everything works correctly, but I'm a bit stumped as to how I
>> can return the calculated distance for a record given that it's not a field
>> in the model. In the Model Manager below, row[1] represents the dynamically
>> calculated distance, but I need to find a way to "attach" each calculation
>> to it's corresponding record so that I can print the distance in a template.
>>
>> class LocationManager(models.Manager):
>> '''
>> '''
>> def nearby_locations(self, latitude, longitude, radius):
>> '''
>> '''
>> cursor = connection.cursor()
>> if settings.DATABASES['default']['ENGINE'] ==
>> 'django.db.backends.sqlite3':
>> # sqlite doesn't natively support math functions, so add them
>> connection.connection.create_function('acos', 1, math.acos)
>> connection.connection.create_function('cos', 1, math.cos)
>> connection.connection.create_function('radians', 1,
>> math.radians)
>> connection.connection.create_function('sin', 1, math.sin)
>>
>> sql = """SELECT id, (3959 * acos(cos(radians(%f)) *
>> cos(radians(latitude)) * cos(radians(longitude) -
>> radians(%f)) +
>> sin(radians(%f)) * sin(radians(latitude))))
>> AS distance FROM locations_location
>> GROUP BY id HAVING distance < %d
>> ORDER BY distance ASC""" % (latitude, longitude, latitude,
>> int(radius))
>> cursor.execute(sql)
>> data = [(row[0], row[1]) for row in cursor.fetchall()]
>> ids = [i[0] for i in data]
>> return self.filter(id__in=ids)
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msg/django-users/-/pfZSZB1nLVIJ.
>> To post to this group, send email to django-users@googlegroups.com.
>> To unsubscribe from this group, send email to
>> django-users+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/django-users?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
Jonathan D. Baker
Developer
http://jonathandbaker.com
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
