On Tue, Apr 15, 2008 at 9:48 AM, Aaron <[EMAIL PROTECTED]> wrote:
>
> I have a model (Sale) that has latitude and longitude fields. I'm
> trying to take one Sale object and find others within a certain radius
> (using the great circle formula). When I do this using the extra()
> QuerySet modifier, it returns a queryset with all the Sale objects in
> the database. But when I manually execute the raw SQL query that's
> being run (according to connection.queries), it returns the result
> that I'm expecting.
>
> I'm using version 0.97-pre-SVN-7121 with MySQL.
>
What version of MySQL and which Django mysql backend? Trying to recreate
with MySQL 5.0.45 the count() on the query set below raises a warning.
With the current mysql backend that gets turned into an exception thrown, so
I get no result. When I tried backing up to using the mysql_old backend,
the warning doesn't (apparently) get turned into an exception (though I
still see it printed on the console), and the count returns the (incorrect)
result of all the rows in the table. So I think you must be using the old
mysql backend? But I don't understand why you do not see the warning I do.
My results are indicated below...
> Am I doing something wrong?
>
> From my shell session:
>
> >>> from data.models import Sale
> >>> from django.db import connection
> >>>
> >>> this_sale = Sale.objects.order_by('?')[0]
> >>> distance_query = "acos( SIN( PI( ) * %s /180 ) * SIN( PI( ) *
> `data_sale`.`latitude` /180 ) + COS( PI( ) * %s /180 ) * COS( PI( ) *
> `data_sale`.`latitude` /180 ) * COS( PI( ) * `data_sale`.`longitude` /180 -
> PI( ) * %s /180 ) ) * 3963.191" % (this_sale.latitude, this_sale.latitude,
> this_sale.longitude)
> >>>
> >>> nearby_sales = Sale.objects.extra(where=['%s < %s'],
> params=[distance_query, .5])
> >>> nearby_sales.count()
Here I get:
/homedir/django/trunk/django/db/backends/mysql_old/base.py:48: Warning:
Truncated incorrect DOUBLE value: 'acos( SIN( PI( ) * 99.0 /180 ) * SIN( PI(
) * `data_sale`.`latitude` /180 ) + COS( PI( ) * 99.0 /180 ) * COS( PI( ) *
`data_sale'
return self.cursor.execute(sql, params)
3L
(For the data, I mocked up, there are 3 rows in the table but the correct
answer should be 1.)
Note the quote before acos( in the warning. I believe the problem is that
strings you specify in params for the extra are automatically quoted, which
you do not want to be done to your distance_query parameter. To avoid
having the distance_query parameter quoted do something like this:
>>> where='%s < %%s' % distance_query
>>> nearby_sales = Sale.objects.extra(where=[where], params=[.5])
>>> nearby_sales.count()
1L
>
> 52448L
> >>>
> >>> query = connection.queries[-1]['sql']
> >>> query
> u'SELECT COUNT(*) FROM `data_sale` WHERE acos( SIN( PI( ) * 37.41915 /
> 180 ) * SIN( PI( ) * `data_sale`.`latitude` /180 ) + COS( PI( ) *
> 37.41915 /180 ) * COS( PI( ) * `data_sale`.`latitude` /180 ) *
> COS( PI( ) * `data_sale`.`longitude` /180 - PI( ) * -76.514234 /
> 180 ) ) * 3963.191 < 0.5'
> >>> cursor = connection.cursor()
> >>> cursor.execute(query)
> 1L
> >>> cursor.fetchone()[0]
> 34L
connection.queries doesn't exactly mimic the backend quoting behavior,
apparently.
Karen
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
