Return SQL calculation within queryset

[jondbaker]

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)

[Andre Terra]

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

--
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...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Jonathan Baker]

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

--
Jonathan D. Baker
Developer
http://jonathandbaker.com

[Melvyn Sopacua]

On 25-7-2012 2:03, jondbaker wrote:
> I've implemented the spherical law of cosines to aid in proximity-based
> searching.

Is there a specific reason you're not using GeoDjango?
Specifically:
<https://docs.djangoproject.com/en/1.4/ref/contrib/gis/geoquerysets/#distance-lookups>
--
Melvyn Sopacua

[Jonathan Baker]

No, nott particularly. I'm working on my second Django project, so I suppose I'm still taking the hard road in a few places until I feel more comfortable. I'll keep Geo on my shortlist of apps/frameworks/etc. to check out soon.

--
You received this message because you are subscribed to the Google Groups "Django users" group.

To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.