How to get the nearest location entries from a database?

Question

I store coordinates as latitude longitude pairs in my database. Now, I need to retrieve all the entries that are within a given radius from a given lat-long coordinate. For example, if given coordinates 48.796777, 2.371140 and distance of 20 kilometers, it would retrieve all the records within 20 kilometers from that point.

If it provides any simplification or results in less CPU time, it's accurate enough to calculate the "straight" distance between the points, i.e. there's no need to factor in the curvature of the earth.

How can this be achieved with Django? More specifically, how should the view (and possibly the model) be constructed for such a query?

University

Answer 1

You should use GeoDjango. It allows you to perform distance queries on your geographic database entries.

from django.contrib.gis.measure import D
from django.contrib.gis.geos import *
from myapp.models import MyResult

pnt = fromstr('POINT(48.796777 2.371140 )', srid=4326)
qs = MyResult.objects.filter(point__distance_lte=(pnt, D(km=20)))

Pastoralize

@Timmy I have stored coordinates in 2 columns(lat and lng), How to add above query to this? I'm not using GIS. – Dumps

can i use Mysql as Well ? – Strader

Answer 2

You can use geopy

from geopy import distance  

_, ne = g.geocode('Newport, RI')  
_, cl = g.geocode('Cleveland, OH')  
distance.distance(ne, cl).miles  
# 538.37173614757057

To optimize a bit you can filter user objects to get a rough estimate of nearby users first. This way you don't have to loop over all the users in the db. This rough estimate is optional. To meet all your project requirements you maybe have to write some extra logic:

#The location of your user.
lat, lng = 41.512107999999998, -81.607044999999999 

min_lat = lat - 1 # You have to calculate this offsets based on the user location.
max_lat = lat + 1 # Because the distance of one degree varies over the planet.
min_lng = lng - 1
max_lng = lng + 1    

users = User.objects.filter(lat__gt=min_lat, lat__lt=max__lat, lat__gt=min_lat, lat__lt=max__lat)

# If not 20 fall back to all users.
if users.count() <= 20:
    users = User.objects.all()

Kokaras

I had similar requirements and ended up using geopy also. I found this gist to be useful: gist.github.com/renyi/3385043. It includes a rough distance calculation to make the query more efficient. – Luminiferous

Answer 3

try this code. i am using python, sqlalchemy

this sqlalchemy query return nearest objects in given point.its work correct.but this query return all models.but its work fine.

my model class

#

class City(Base):
   __tablename__ = "tbl_City"
  city_id = Column(Integer, primary_key=True, unique=True) 
  geo_coordinates = Column(Geometry('POINT', srid=4326))

my query

#

point = city.geo_coordinates
near_citylist = City.query.order_by(City.geo_coordinates.distance_box(point))

Corkage

Answer 4

Without GeoDjango

You could do,

objects = YourModel.objects.raw('SELECT *,  ( 6371 * acos( cos( radians('+my_lat+') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('+my_long+') ) + sin( radians('+my_lat+') ) * sin( radians( latitude ) ) ) ) AS distance FROM "YourModel" WHERE (6371 * acos( cos( radians('+my_lat+') ) * cos( radians(latitude) ) * cos( radians(longitude) - radians('+my_long+') ) + sin( radians('+my_lat+') ) * sin( radians(latitude) ) ) ) <= 100')

where my_lat is the input latitude, my_long is the input longitude, latitude is the Model field for Latitude, longitude is the Model field for Longitude and 100 is the distance.

I used FloatField for latitudes and longitudes in my Model.

Now as this returns a RawQueryset, if you need to work with additional filters on the queryset, you could get the usual Django Queryset by doing,

objects = YourModel.objects.filter(pk__in=[i.pk for i in objects])

Jevons

Answer 5

I think the best way is to go with GeoDjango like Timmy suggests, but then you have to have a Geo-enabled Backend (like PosGis). However, I don't think it's possible to use if you have a model containing custom Latitude and Longitude Field like

class CustomGeoModel(models.Model):
  lat = models.CharField(max_length=30)
  lon = models.CharField(max_length=30)

Miru