Django - how to make a complex math annotation (k Nearest Neighbors)

Question

I have this model:

class Image(models.Model):
    title = models.CharField(max_length=200)
    image = models.ImageField(upload_to='img/')
    signature = models.TextField(null = True)

The signature is a numpy monodimensional vector encoded in json. In order to make my query, I have to decode each object signature into a nparray, and make a dot product between each object's signature and a given vector, then annotate as a float (named "score") field beside each raw. Lastly I have to order from max to min.

I tried this in view.py

def image_sorted(request):
    query_signature = extract_feat(settings.MEDIA_ROOT + "/cache" + "/003_ant_image_0003.jpg") # a NParray object

    image_list = Image.objects.annotate(score=np.dot(
        JSONVectConverter.json_to_vect(F('signature')), query_signature.T
    ).astype(float)).order_by('score') #JSONVectConverter is a class of mine
    return render(request, 'images/sorted.html', {'image_sorted': image_list})

of course it doesn't work. I think "F()" operator is out of scope...

If you're wondering, I'm writing an image retrieval webapp for my university thesis.

Thank you.

EDIT: I found this that is quite the same problem (He use postgres instead of MySQL)

EDIT2: I just remember now what is the last solution I've adopted! First I pull out every vector from the DB and mantain it in RAM, then I make some simple computes to find the K-Nearest Neighbors. Then, I retrieve from the DB the respective image using its index (primary key). So I decouple this task from Django ORM. Here's the code (from the Rest API)

def query_over_db(query_signature, page):

    query_signature = np.array(query_signature)

    t0 = time.time()

    descriptor_matrix = cache.get('descriptor_matrix')
    id_vector = cache.get('id_vector')

    if not descriptor_matrix:
        id_vector = []
        descriptor_matrix = []
        images_dict = Image.objects.all().values('id', 'signature')
        for image in images_dict:
            s = image['signature']
            descriptor = np.array(s)
            descriptor_matrix.append(descriptor)
            id_vector.append(image['id'])

        cache.set('id_vector', id_vector)
        cache.set('descriptor_matrix', descriptor_matrix)

    t1 = time.time()
    print("time to pull out the descriptors : " + str(t1 - t0))
    t1 = time.time()
    #result = np.abs(np.dot(descriptor_matrix, query_signature.T))

    #result = np.sum((descriptor_matrix - query_signature)**2, axis=1)

    result = ne.evaluate('sum((descriptor_matrix - query_signature)**2, axis=1)')

    t2 = time.time()
    print("time to calculate similarity: " + str(t2 - t1))

    perm = np.argsort(result)[(page - 1) * 30:page * 30]
    print(perm.shape)
    print(len(id_vector))

    perm_id = np.array(id_vector)[perm]
    print(len(perm_id))

    print("printing sort")
    print(np.sort(result)[0])

    t4 = time.time()

    print("time to order the result: " + str(t4 - t2))

    qs = Image.objects.defer('signature').filter(id__in=perm_id.tolist())

    qs_new = []
    for i in range(len(perm_id)):
        qs_new.append(qs.get(id=perm_id[i]))

    t3 = time.time()
    print("time to get the results from the DB : " + str(t3 - t2))
    print("total time : " + str(t3 - t0))
    print(result[perm])
    return qs_new

Welcome to Stack Overflow! You can learn [How to Ask a good question](http://stackoverflow.com/help/how-to-ask) and create a [Minimal, Complete, and Verifiable](http://stackoverflow.com/help/mcve) example. That makes it easier for us to help you. — Stephen Rauch, Jan 21 '18 at 20:22

Artisan · Accepted Answer · 2018-01-22T12:50:52.240

0

I didn't come close to trying something this complex, however I've solved a similar issue here: Combining Django F, Value and a dict to annotate a queryset

I haven't tried this but you could give it a go:

    from django.db.models import Case, When, FloatField
    query_signature = extract_feat(settings.MEDIA_ROOT + "/cache" + "/003_ant_image_0003.jpg") # a NParray object

    value_dict = {}
    for image in Image.objects.all():
        value_dict[image.signature] = np.dot(
            JSONVectConverter.json_to_vect(image.signature),
                query_signature.T
            ).astype(float) 
    whens = [
        When(signature=k, then=v) for k, v in value_dict.items()
    ]
    qs = Image.objects.all().annotate(
        score=Case(
            *whens,
            default=0,
            output_field=FloatField()
        )
    ).order_by('score')

Hope it helps

edited Jan 22 '18 at 12:50

answered Jan 22 '18 at 06:40

Artisan

1,974
1
18
23

Hi unfortunately it doesn't work: it gives me the same error as before: "name 'F' is not defined" – Federico Vaccaro Jan 22 '18 at 11:13
I tried to change something (to perform the dot product at python level) and the interpreter can go forward, but it stuck at the middle saying me "name 'When' is not defined". I have to admit that i don't know that syntax. – Federico Vaccaro Jan 22 '18 at 11:50
@FedericoVaccaro I've made some fixes and added the required Import for When and Case - you can also read about them here https://docs.djangoproject.com/en/2.0/ref/models/conditional-expressions/ – Artisan Jan 22 '18 at 12:51

score 0 · Answer 2 · answered Jan 22 '18 at 12:49

So that's the final working code:

def image_sorted(request):
    query_signature = extract_feat(settings.MEDIA_ROOT + "/cache" + "/001_accordion_image_0001.jpg")  # a NParray object
    #query_signature = extract_feat(settings.MEDIA_ROOT + "/cache" + "/003_ant_image_0003.jpg")  # a NParray object


    value_dict = {}
    for image in Image.objects.all():
        S = image.signature
        value_dict[image.signature] = np.dot(
            JSONVectConverter.json_to_vect(S),
            query_signature.T
        ).astype(float)
    whens = [
        When(signature=k, then=v) for k, v in value_dict.items()
    ]
    qs = Image.objects.all().annotate(
        score=Case(
            *whens,
            default=0,
            output_field=FloatField()
        )
    ).order_by('-score')

    for image in qs:
        print(image.score)

    return render(request, 'images/sorted.html', {'image_sorted': qs})

Thanks to Omar for helping me! Of course I'm still here if there are finer solutions.

Django - how to make a complex math annotation (k Nearest Neighbors)

2 Answers2