1

so I am writing migration which will assign latitude and longitude values to PointField in every record of database.

I have one solution which works but it is too slow for a 10 000 000 records of database. So I can update every record in for loop by simply assigning lat and long to a point and then save a record. But I wanted to use Django's .update() method to do this much faster.

class Listing(AirbnbModel):
 latitude = models.DecimalField()
 longitude = models.DecimalField()
 point = PointField(srid=4326, geography=True, null=True) # new field

This works but takes too much time and it is almost impossible to do this for 10 000 000 records

for listing in Listing.objects.all():
        listing.point = Point(float(listing.latitude), float(listing.longitude))
        listing.save()

Here is what I tried to do but I got an error.


    Listing.objects.all().update(point=ExpressionWrapper(F('latitude'), F('longitude') , output_field=PointField()))

I tried this as well

      Listing.objects.all().update(point=ExpressionWrapper(Point(Value('latitude'), Value('longitude')), output_field=PointField()))

But this does not work at all.

Jakobowsky
  • 11
  • 2
  • Considering that all of this needs to happen in the database, you may want to research what SQL is used to save a point in the database. It would be easier (for you and us) to find a solution then. – Endre Both Apr 11 '19 at 08:58
  • Could you provide me with some links ? I tried already few solutuin with saving Point in database via terminal. But got many errors as well. I tried solution like this `update listings set point = POINT(latitude, longitude) where id =8;` It does not work as well – Jakobowsky Apr 11 '19 at 09:11
  • If you can use the Django shell, you can find information on how to inspect the SQL used by Django [here](https://stackoverflow.com/questions/51362648/print-sql-queries-in-jupyter-notebook-with-django-extensions-plugin#answer-54632855). – Endre Both Apr 11 '19 at 09:52

1 Answers1

0

I got my answer. Here is the solution. I used SQL for that

with connections[self.db_name].cursor() as cursor:
            cursor.execute(
                f"UPDATE listings SET point = ST_MakePoint(latitude, longitude);"
            )

This solution is fast.

Jakobowsky
  • 11
  • 2