在Django中使用球面余弦定律通过邻近度过滤邮政编码

ldfqzlk8  于 2023-11-20  发布在  Go
关注(0)|答案(8)|浏览(137)

我正在尝试在Django中处理一个基本商店定位器的邻近搜索。而不是在我的应用程序中使用PostGIS，以便我可以使用GeoDjango的距离过滤器，我想在模型查询中使用Cosines距离公式的球面定律。为了提高效率，我希望所有的计算都在数据库中完成。
一个来自互联网的MySQL查询示例实现了球面余弦定律，如下所示：

  1. SELECT id, ( 
  2.     3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
  3.     cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * 
  4.     sin( radians( lat ) ) ) 
  5. ) 
  6. AS distance FROM stores HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

字符串
查询需要为每个商店的纬度/纬度值引用Zipcode ForeignKey。我如何在Django模型查询中实现所有这些功能？

django

8条答案

按热度按时间
yhuiod9q

yhuiod9q1#

有可能执行raw SQL queries in Django。
我的建议是，编写查询来拉取一个ID列表（看起来你现在正在做），然后使用ID来拉取关联的模型（在一个常规的、非原始的SQL Django查询中）。尽量保持SQL与方言无关，这样如果你不得不切换数据库，你就不必再担心一件事了。
为了澄清，这里有一个如何做到这一点的例子：

  1. def get_models_within_25 (self):
  2.     from django.db import connection, transaction
  3.     cursor = connection.cursor()
  5.     cursor.execute("""SELECT id, ( 
  6.         3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * 
  7.         cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * 
  8.         sin( radians( lat ) ) ) )
  9.         AS distance FROM stores HAVING distance < 25
  10.         ORDER BY distance LIMIT 0 , 20;""")
  11.     ids = [row[0] for row in cursor.fetchall()]
  13.     return MyModel.filter(id__in=ids)

字符串
作为免责声明，我不能保证这段代码，因为我已经有几个月没有写任何Django了，但它应该是沿着正确的路线。

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
zte4gxcn

zte4gxcn2#

为了跟进Tom的回答，它在SQLite中默认不工作，因为SQLite默认缺少数学函数。没问题，添加它非常简单：

  1. class LocationManager(models.Manager):
  2.     def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
  3.         if use_miles:
  4.             distance_unit = 3959
  5.         else:
  6.             distance_unit = 6371
  8.         from django.db import connection, transaction
  9.         from mysite import settings
  10.         cursor = connection.cursor()
  11.         if settings.DATABASE_ENGINE == 'sqlite3':
  12.             connection.connection.create_function('acos', 1, math.acos)
  13.             connection.connection.create_function('cos', 1, math.cos)
  14.             connection.connection.create_function('radians', 1, math.radians)
  15.             connection.connection.create_function('sin', 1, math.sin)
  17.         sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
  18.         cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
  19.         AS distance FROM location_location WHERE distance < %d
  20.         ORDER BY distance LIMIT 0 , %d;""" % (distance_unit, latitude, longitude, latitude, int(radius), max_results)
  21.         cursor.execute(sql)
  22.         ids = [row[0] for row in cursor.fetchall()]
  24.         return self.filter(id__in=ids)

字符串

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
xoefb8l8

xoefb8l83#

为了跟进Tom，如果你想有一个在postgresql中也能工作的查询，你不能使用AS，因为你会得到一个错误，说'distance'不存在。
你应该把整个球面定律表达式放在WHERE子句中，像这样（它在mysql中也有效）：

  1. import math
  2. from django.db import connection, transaction
  3. from django.conf import settings
  5. from django .db import models
  7. class LocationManager(models.Manager):
  8.     def nearby_locations(self, latitude, longitude, radius, use_miles=False):
  9.         if use_miles:
  10.             distance_unit = 3959
  11.         else:
  12.             distance_unit = 6371
  14.         cursor = connection.cursor()
  16.         sql = """SELECT id, latitude, longitude FROM locations_location WHERE (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
  17.             cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) ) < %d
  18.             """ % (distance_unit, latitude, longitude, latitude, int(radius))
  19.         cursor.execute(sql)
  20.         ids = [row[0] for row in cursor.fetchall()]
  22.         return self.filter(id__in=ids)

字符串
请注意，您必须选择纬度和经度，否则您不能在WHERE子句中使用它。

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
qybjjes1

qybjjes14#

为了跟进jboxer的回答，下面是作为自定义管理器的一部分的整个过程，其中一些硬编码的东西变成了变量：

  1. class LocationManager(models.Manager):
  2.     def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
  3.         if use_miles:
  4.             distance_unit = 3959
  5.         else:
  6.             distance_unit = 6371
  8.         from django.db import connection, transaction
  9.         cursor = connection.cursor()
  11.         sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
  12.         cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
  13.         AS distance FROM locations_location HAVING distance < %d
  14.         ORDER BY distance LIMIT 0 , %d;""" % (distance_unit, latitude, longitude, latitude, int(radius), max_results)
  15.         cursor.execute(sql)
  16.         ids = [row[0] for row in cursor.fetchall()]
  18.         return self.filter(id__in=ids)

字符串

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
2exbekwf

2exbekwf5#

JBoxer的回应

  1. def find_cars_within_miles_from_postcode(request, miles, postcode=0):
  3.     # create cursor for RAW query
  4.     cursor = connection.cursor()
  6.     # Get lat and lon from google
  7.     lat, lon = getLonLatFromPostcode(postcode)
  9.     # Gen query
  10.     query = "SELECT id, ((ACOS(SIN("+lat+" * PI() / 180) * SIN(lat * PI() / 180) + COS("+lat+" * PI() / 180) * COS(lat * PI() / 180) * COS(("+lon+" - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car HAVING distance<='"+miles+"' ORDER BY distance ASC"
  12.     # execute the query
  13.     cursor.execute(query)
  15.     # grab all the IDS form the sql result
  16.     ids = [row[0] for row in cursor.fetchall()]
  18.     # find cars from ids
  19.     cars = Car.objects.filter(id__in=ids)
  21.     # return the Cars with these IDS
  22.     return HttpResponse( cars )

字符串
这返回了我的汽车从x数量的英里，这工作得很好。然而，原始查询返回多远，他们从某个位置，我认为fieldname是'距离'。
我怎样才能返回这个字段'距离'与我的汽车对象？

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
guykilcj

guykilcj6#

使用上面提出的一些答案，我得到了不一致的结果，所以我决定再次使用[这个链接] http://www.movable-type.co.uk/scripts/latlong.html作为参考来检查方程，方程是d = acos(sin(lat1)*sin(lat2) + cos(lat1)*cos(lat2)*cos(lon2-lon1) ) * 6371，其中d是要计算的距离，
lat1,lon1是基点的坐标，lat2,lon2是其他点的坐标，在我们的例子中，这些点是数据库中的点。
从上面的答案来看，LocationManager类如下所示

  1. class LocationManager(models.Manager):
  2. def nearby_locations(self, latitude, longitude, radius, max_results=100, use_miles=True):
  3.     if use_miles:
  4.         distance_unit = 3959
  5.     else:
  6.         distance_unit = 6371
  8.     from django.db import connection, transaction
  9.     from mysite import settings
  10.     cursor = connection.cursor()
  11.     if settings.DATABASE_ENGINE == 'sqlite3':
  12.         connection.connection.create_function('acos', 1, math.acos)
  13.         connection.connection.create_function('cos', 1, math.cos)
  14.         connection.connection.create_function('radians', 1, math.radians)
  15.         connection.connection.create_function('sin', 1, math.sin)
  17.     sql = """SELECT id, (acos(sin(radians(%f)) * sin(radians(latitude)) + cos(radians(%f))
  18.           * cos(radians(latitude)) * cos(radians(%f-longitude))) * %d)
  19.     AS distance FROM skills_coveragearea WHERE distance < %f
  20.     ORDER BY distance LIMIT 0 , %d;""" % (latitude, latitude, longitude,distance_unit, radius, max_results)
  21.     cursor.execute(sql)
  22.     ids = [row[0] for row in cursor.fetchall()]
  24.     return self.filter(id__in=ids)

字符串
使用网站[链接] http://www.movable-type.co.uk/scripts/latlong.html作为检查，我的结果一致。

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
cbeh67ev

cbeh67ev7#

使用Django的数据库函数也可以做到这一点，这意味着你可以使用.annotate()调用添加一个distance_miles列，然后按它排序。下面是一个例子：

  1. from django.db.models import F
  2. from django.db.models.functions import ACos, Cos, Radians, Sin
  4. locations = Location.objects.annotate(
  5.     distance_miles = ACos(
  6.         Cos(
  7.             Radians(input_latitude)
  8.         ) * Cos(
  9.             Radians(F('latitude'))
  10.         ) * Cos(
  11.             Radians(F('longitude')) - Radians(input_longitude)
  12.         ) + Sin(
  13.             Radians(input_latitude)
  14.         ) * Sin(Radians(F('latitude')))
  15.     ) * 3959
  16. ).order_by('distance_miles')[:10]

字符串

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
z0qdvdin

z0qdvdin8#

@classmethod def nearby_locations（cls，latitude，longitude，radius，max_results=1000，use_miles=False）：if use_miles：distance_unit = 3959 else：distance_unit = 6371000

  1. from django.db import connection, transaction
  2. from django.conf import settings
  3. cursor = connection.cursor()
  5. sql = """SELECT id, (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
  6. cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) )
  7. AS distance FROM yourapp_yourmodel 
  8. GROUP BY id, latitude, longitude
  9. HAVING (%f * acos( cos( radians(%f) ) * cos( radians( latitude ) ) *
  10. cos( radians( longitude ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( latitude ) ) ) ) < %d
  11. ORDER BY distance OFFSET 0 LIMIT %d;""" % (distance_unit, latitude, longitude, latitude, distance_unit, latitude, longitude, latitude, int(radius), max_results)
  13. cursor.execute(sql)
  14. ids = [row[0] for row in cursor.fetchall()]
  16. return cls.objects.filter(id__in=ids)

字符串

展开查看全部
赞(0）分享  回复(0）举报  2023-11-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com