user-defined function raised exception occurred

Question

I have following error when I'm using below user defined function with raw query:

Django Version: 2.1
Exception Type: OperationalError
Exception Value: user-defined function raised exception

from django.db.backends.signals import connection_created
from django.dispatch import receiver

@receiver(connection_created)
def extend_sqlite(connection=None, **kwargs):
    if connection.vendor == "sqlite":
        # sqlite doesn't natively support math functions, so add them
        cf = connection.connection.create_function
        cf('acos', 1, math.acos)
        cf('cos', 1, math.cos)
        cf('radians', 1, math.radians)
        cf('sin', 1, math.sin)

Here are is my raw query.

query = """SELECT id, (6367*acos(cos(radians(%2f))
                       *cos(radians(lat))*cos(radians(long)-radians(%2f))
                       +sin(radians(%2f))*sin(radians(lat))))
                       AS distance FROM ksApp_outlet
                       group by id having distance < %2f """ % (
                            float(lat),
                            float(lng),
                            float(lat),
                            float(radius)
                        )
            outletdata = Outlet.objects.raw(query)

Here is table schema

fields=[
    ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
    ('outletTitle', models.CharField(max_length=100, unique=True)),
    ('address', models.CharField(blank=True, max_length=100, null=True)),
    ('phoneNumber', models.CharField(blank=True, max_length=100, null=True)),
    ('picturesX', models.FileField(blank=True, null=True, upload_to='')),
    ('lat', models.CharField(blank=True, default='foobar', max_length=50, null=True)),
    ('long', models.CharField(blank=True, default=None, max_length=50, null=True)),
    ('region', models.CharField(blank=True, default=None, max_length=50, null=True)),
    ('cuisine', models.ForeignKey(blank=True, null=True, on_delete='CASCADE', to='ksApp.CuisineType')),
    ('merchantName', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='ksApp.Merchant')),
],

Not sure what I'm doing wrong here. Can any one help me to figure out this error?

Well then how come you are doing a distance calculation? Your query above is the well known distance calculation sql query — e4c5, Sep 05 '18 at 11:21
@e4c5 - I have used custom manager as per the answer here - https://stackoverflow.com/questions/19703975/django-sort-by-distance — bunty007, Sep 05 '18 at 11:27
if you are looking at the answer from rhplo on that it's a very poor one. Amazed that it has so many upvotes. and just FYI, you *are* storing geospatial information. — e4c5, Sep 05 '18 at 11:37
@e4c5 yes, correct I'm using rhplo's answer because of upvotes. but I don't know how to use geospatial and geodjango. if you have any good references to use geodjango with sqllite then plz share here — bunty007, Sep 05 '18 at 11:52
I meant the actual create table statement, not whatever that is. But if I'm reading that right, you're using text columns for your latitude and longitude? The radians function you're using might be expecting numeric values instead and raising an error because of that. — Shawn, Sep 05 '18 at 18:18

user-defined function raised exception occurred

0 Answers0