Postgresql - How do I effectively filter locations in my database without having to loop through it manually?

OGBellz
April 15, 2024
213 views
0 votes
2 Answers

In my DRF project, I have a model structured like this

class ServiceLocation(models.Model):
    '''
    Represents a location where an internet service is offered
    '''
    SERVICE_TYPES = [
        ("wifi", 'wifi'),
        ("fibre", "fibre"),
        ("p2p/ptmp", "p2p/ptmp")
    ]

    id = models.UUIDField(primary_key=True, default=uuid.uuid4,
                          editable=False, null=False, blank=False)
    description = models.TextField()


    # Location
    address = models.CharField(max_length=150, null=False, blank=False)
    latitude = models.DecimalField(max_digits=18, decimal_places=15)
    longitude = models.DecimalField(max_digits=18, decimal_places=15)

    # Service
    service = models.CharField(
        max_length=10, choices=SERVICE_TYPES, null=False, blank=False)
    speed = models.IntegerField()
    
    def __str__(self):
        return f"{self.service} by {self.operator}"

and I’m trying to filter this instances of this model with their relative proximity to given coordinate.

My view is structured like this


class CloseServiceLocations(View):
    def get(self, request):
        lat = request.GET.get('lat', 6.748134)
        lng = request.GET.get('lng', 3.633301)
        distance = request.GET.get('distance', 10)  # Default distance to 10 if not provided

        # if lat is None or lng is None:
        #     # return JsonResponse({'error': 'Latitude and Longitude are required parameters.'}, status=400)


        try:
            lat = float(lat)
            lng = float(lng)
            distance = float(distance)
        except ValueError:
            return JsonResponse({'error': 'Invalid latitude, longitude, or distance provided.'}, status=400)

        # Create a Point object representing the provided latitude and longitude
        user_location = Point(lng, lat, srid=4326)

        

        # Calculate the distance in meters (Django's Distance function uses meters)
        distance_in_meters = distance * 1000  
        close_service_locations = ServiceLocation.objects.annotate(
            # Convert longitude and latitude fields to floats
            longitude_float=Cast('longitude', FloatField()),
            latitude_float=Cast('latitude', FloatField())
        ).annotate(
            # Create Point object using converted longitude and latitude
            location=Point(F('longitude_float'), F('latitude_float'), srid=4326)
        ).annotate(
            # Calculate distance
            distance=Distance('location', user_location)
        ).filter(distance__lte=distance_in_meters)
    # Serialize the queryset to JSON
        serialized_data = [{'id': location.id,
                            'description': location.description,
                            'operator': location.operator.name,
                            'address': location.address,
                            'latitude': location.latitude,
                            'longitude': location.longitude,
                            'service': location.service,
                            'speed': location.speed} for location in close_service_locations]

        return JsonResponse(serialized_data, safe=False)

    def post(self, request):
        return JsonResponse({'error': 'Method not allowed'}, status=405)

where i try to annotate a new attribute "location" so i can take advantage of from django.contrib.gis.db.models.functions‘s Distance method to calculate the distance instead of looping through and calculating the Haversine distance manually which was my initial approach.

when i run this, i get Server Error message which I’m almost sure is not from my views.

In an attempt to fix this, I broke my views down with into sections and added print statements to see what part was causing it to break

        print("Annotating QS with lon/lat float... ")
        close_service_locations = ServiceLocation.objects.annotate(
            # Convert longitude and latitude fields to floats
            longitude_float=Cast('longitude', FloatField()),
            latitude_float=Cast('latitude', FloatField())
        )
        print("LON/LAT float annotation complete")
       
        print("Annotating QS with location point... ")
        print("Size: ", len(close_service_locations))
       

        close_service_locations = close_service_locations.annotate(
            # Create Point object using converted longitude and latitude
            location=Point('longitude_float', 'latitude_float', srid=4326)
        )
        print("Location point annotation complete")

        print("Annotating QS with relative distance... ")
        close_service_locations = close_service_locations.annotate(
            # Calculate distance
            distance=Distance('location', user_location)
        )
        print("Distance annotation complete")

I noticed the print("Annotating QS with lon/lat float... ") block runs successfully in no time but it breaks in the print("Annotating QS with location point... ") block where i try to annotate the QS with location attribute

At some point, I got an error that says "Invalid parameters given for Point initialization." which made me add the print("Annotating QS with lon/lat float... ") block to force all Decimalfiled objects into floats.

I also tried looping through the close_service_locations manually to see if I have a service location with an invalid latitude or longitude with this

for i in range(len(close_service_locations)):
            print(i)
            location=Point(close_service_locations[i].longitude_float, close_service_locations[i].latitude_float, srid=4326)

Surprisingly, this ran successfully.

But I still dont know how to get my view to work successfully past that point.

This is the error i keep getting

Annotating QS with lon/lat float...
LON/LAT float annotation complete
Annotating QS with location point...

[13/Apr/2024 04:49:30] "GET /directory/close-service-locations/ HTTP/1.1" 500 145

No Django Error page on the browser detailing the cause, just a big Server Error

I also tried looping through the service-locations manually and adding the location attribute and printing the index maybe I could get an hint of what is breaking my code or if there is service location whose longitude and altitude pair couldn’t be converted to a Point object but I still got the same error

Answers

Chosen as BEST ANSWER

I devised a different approach based on how precise the decimal digits on the coordinates of google maps are and it worked.

    def get(self, request):
        lat = request.GET.get('lat', 8.464134)
        lng = request.GET.get('lng', 4.454301)
        distance = request.GET.get('distance', 10)  # Default distance to 10 km if not provided

        try:
            lat = float(lat)
            lng = float(lng)
            distance = float(distance)
        except ValueError:
            return JsonResponse({'error': 'Invalid latitude, longitude, or distance provided.'}, status=400)

        # Define the approximate distance difference in degrees (assuming 1 degree is approximately 111 km)
        diff = distance / 111    
        
        print("Getting SLs .....")
        close_locations = ServiceLocation.objects.filter(latitude__gte=(lat-diff), latitude__lte=(lat+diff), longitude__gte=(lng-diff), longitude__lte=(lng+diff), )

        print("Close locations length ", len(close_locations))
        serialized = ServiceLocationSerializer(close_locations, many=True)

        print(serialized.data)
        return Response(serialized.data, status=status.HTTP_200_OK)

(Edit)

In your following code you are providing strings(longitude_floatlatitude_float and latitude_float) to Point class.

close_service_locations = close_service_locations.annotate(
    # Create Point object using converted longitude and latitude
     location=Point('longitude_float', 'latitude_float', srid=4326)
   )

you might be thinking that you are creating those value above but that’s in line longitude_float=Cast('longitude', FloatField()), but in following annotation it is considering as just string.

you can trying following to make it work.

from django.contrib.gis.db.models import F
close_service_locations = ServiceLocation.objects.annotate(
            # Convert longitude and latitude fields to floats
            longitude_float=Cast('longitude', FloatField()),
            latitude_float=Cast('latitude', FloatField()),
            location=Point(F('longitude_float'), F('latitude_float'), srid=4326)
            # in the same code.
        )

EDIT:
An Another way to do this is to use Python instead of Django.

close_service_locations = ServiceLocation.objects.annotate(
    # Convert longitude and latitude fields to floats
    longitude_float=Cast('longitude', FloatField()),
    latitude_float=Cast('latitude', FloatField())
)
service_location_mapping = {}
for service in close_service_locations:
    location = Point(service.longitude_float, service.latitude_float, srid=4326)
    distance = Distance(location, user_location)
    service_location_mapping[service] = {
        'distance': distance,
        'service': service
    }

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – How do I effectively filter locations in my database without having to loop through it manually?

Answers