entityMap|blocks|key|2uqj0|text|我没有使用GeoDjango的经验，但在PostgreSQL/PostGIS上有|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3l9i2|_|g4ii|距离(..)函数。因此，您可以按以下方式对结果进行排序|5nhug|看看最近的行是什么。|brrbt|如果具有普通坐标(无postgis几何图形)，则可以使用geometryFromText函数将坐标转换为点。|dfmq5|这就是你要找的吗？如果不是，试着说得更明确一些。^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|M|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|N|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|O|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|P|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|Q|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|R|9|@]|A|@]|B|$]]]]

I have no experience with GeoDjango, but on PostgreSQL/PostGIS you have the st_distance(..) function. So, you can order your results by <code>st_distance(geom_column, your_coordinates) asc</code> and see what are the nearest rows.

If you have plain coordinates (no postgis geometry), you can convert your coordinates to a point with the geometryFromText function.

Is that what you were looking for? If not, try to be more explicit.

entityMap|blocks|key|5tj74|text|这就是结合使用GeoDjango和PostGIS的答案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|a58u6|点坐标必须是GEOSGeometry对象。要使用它，请执行以下操作|q451|from+django.contrib.gis.geos+import+GEOSGeometry
point+=+GEOSGeometry('POINT(5+23)')|code-block|syntax|javascript|86f8n|然后，让我们假设您有一个"Restaurant“模型和点的坐标。因此，对于最近的餐厅，只需使用：|8mc4d|Restaurants.objects.distance(point).order_by('distance')[0]^0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|N|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|O|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|P|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|Q|9|@]|A|@]|B|$]]|$2|L|4|M|6|G|8|R|9|@]|A|@]|B|$H|I]]]]

This this the answer using GeoDjango with PostGIS

Point coordinates must be a GEOSGeometry object. To make it use

<pre><code>from django.contrib.gis.geos import GEOSGeometry
point = GEOSGeometry('POINT(5 23)')
</code></pre>

Then, let's imagine that you have a "Restaurant" model And coordinates of the point.
So, for the closest restaurant just use:

<pre><code>Restaurants.objects.distance(point).order_by('distance')[0] 
</code></pre>

entityMap|blocks|key|2bibm|text|我不得不同意delawen的回答，但是使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|9a0pp|st|9k97n|_|ooe8|距离|98af8|它本身将是|ugos|非常慢|5866v|..。因此，为了加快速度，您必须|c76ag|利用GIST索引|dadh5|(注：|ev9pa|大多数PostGIS函数包括st|6ugtl|5v4el|距离不使用索引|2217p|请参见：|74h2n|postgis索引建议|2au26|)。|angec|因此，首先在点周围创建一个缓冲区，然后使用"&&“检查它的边界框(这利用了内置的GIST索引-因此它的性能会好得多)，然后通过"st”检查距离|95k5q|3qofv|距离“。|dumnn|例如，要从给定的cood位置(例如X=1，Y=1)获得最近的"restaurant“，您可以这样写：|dufp2|select+*,st_distance(the_geom_col,st_geomfromtext('POINT(1+1)',27700))+as+distance+
from+restaurants+where+st_buffer(st_geomfromtext('POINT(1+1)',27700),100)+
&&+"the_geom_col"|code-block|syntax|javascript|50d1o|与"st“相比，这将是非常快的。|9ibqg|5cd9c|距离“，但结果可能包含离给定位置超过100米的餐馆(特别是当几何图形保持为直线或多边形格式时)。|6p65t|为了获得更准确的结果，恰好在100米范围内的餐厅，你|54i39|追加|6dd6b|下面是上面给出的查询：|bulc8|and+st_distance(the_geom_col,st_geomfromtext('POINTFROMTEXT(1+1)',27700))+<=+100|5bqfn|这仍然比使用|ass8j|5u1rk|akjmh|ipe2|单打独斗。因为数据库将仅运行|9u5jp|5knhs|khdu|cn06f|与满足第一个标准的记录进行比较。|2v83a|因此，作为一个经验法则，每当您必须执行昂贵的空间查找时，请尝试执行以下操作：|8lfdp|通过使用特殊运算符过滤出尽可能多的错误结果(请参阅|e8jve|官方postgis文档|84h58|..。|6utv6|然后编写实际的空间关系检查器函数。|d1iq8|始终将GIST索引放在"geometry“列上。|5qi94|通过使用将边界框添加到您的“几何”|2q6b3|5r6jv|ckhm|addbbox|2o5l5|edpak|例行公事地重新建立索引并对表进行真空/分析。|fmmri|注意:缓冲区的大小或实际距离必须在您使用的投影系统中，例如，如果您使用的是EPSG:4326+(经度/经度)，则必须以度为单位给出这些距离。例如，现实世界中的1米=+0.00000899度。和100米=做数学计算:)^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|2R|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|2S|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|2T|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|2U|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|2V|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|2W|9|@]|A|@]|B|$]]|$2|M|4|N|6|7|8|2X|9|@]|A|@]|B|$]]|$2|O|4|P|6|7|8|2Y|9|@]|A|@]|B|$]]|$2|Q|4|R|6|7|8|2Z|9|@]|A|@]|B|$]]|$2|S|4|T|6|7|8|30|9|@]|A|@]|B|$]]|$2|U|4|F|6|7|8|31|9|@]|A|@]|B|$]]|$2|V|4|W|6|7|8|32|9|@]|A|@]|B|$]]|$2|X|4|Y|6|7|8|33|9|@]|A|@]|B|$]]|$2|Z|4|10|6|7|8|34|9|@]|A|@]|B|$]]|$2|11|4|12|6|7|8|35|9|@]|A|@]|B|$]]|$2|13|4|14|6|7|8|36|9|@]|A|@]|B|$]]|$2|15|4|F|6|7|8|37|9|@]|A|@]|B|$]]|$2|16|4|17|6|7|8|38|9|@]|A|@]|B|$]]|$2|18|4|19|6|7|8|39|9|@]|A|@]|B|$]]|$2|1A|4|1B|6|1C|8|3A|9|@]|A|@]|B|$1D|1E]]|$2|1F|4|1G|6|7|8|3B|9|@]|A|@]|B|$]]|$2|1H|4|F|6|7|8|3C|9|@]|A|@]|B|$]]|$2|1I|4|1J|6|7|8|3D|9|@]|A|@]|B|$]]|$2|1K|4|1L|6|7|8|3E|9|@]|A|@]|B|$]]|$2|1M|4|1N|6|7|8|3F|9|@]|A|@]|B|$]]|$2|1O|4|1P|6|7|8|3G|9|@]|A|@]|B|$]]|$2|1Q|4|1R|6|1C|8|3H|9|@]|A|@]|B|$1D|1E]]|$2|1S|4|1T|6|7|8|3I|9|@]|A|@]|B|$]]|$2|1U|4|D|6|7|8|3J|9|@]|A|@]|B|$]]|$2|1V|4|F|6|7|8|3K|9|@]|A|@]|B|$]]|$2|1W|4|H|6|7|8|3L|9|@]|A|@]|B|$]]|$2|1X|4|1Y|6|7|8|3M|9|@]|A|@]|B|$]]|$2|1Z|4|D|6|7|8|3N|9|@]|A|@]|B|$]]|$2|20|4|F|6|7|8|3O|9|@]|A|@]|B|$]]|$2|21|4|H|6|7|8|3P|9|@]|A|@]|B|$]]|$2|22|4|23|6|7|8|3Q|9|@]|A|@]|B|$]]|$2|24|4|25|6|7|8|3R|9|@]|A|@]|B|$]]|$2|26|4|27|6|7|8|3S|9|@]|A|@]|B|$]]|$2|28|4|29|6|7|8|3T|9|@]|A|@]|B|$]]|$2|2A|4|2B|6|7|8|3U|9|@]|A|@]|B|$]]|$2|2C|4|2D|6|7|8|3V|9|@]|A|@]|B|$]]|$2|2E|4|2F|6|7|8|3W|9|@]|A|@]|B|$]]|$2|2G|4|2H|6|7|8|3X|9|@]|A|@]|B|$]]|$2|2I|4|D|6|7|8|3Y|9|@]|A|@]|B|$]]|$2|2J|4|F|6|7|8|3Z|9|@]|A|@]|B|$]]|$2|2K|4|2L|6|7|8|40|9|@]|A|@]|B|$]]|$2|2M|4|2B|6|7|8|41|9|@]|A|@]|B|$]]|$2|2N|4|2O|6|7|8|42|9|@]|A|@]|B|$]]|$2|2P|4|2Q|6|7|8|43|9|@]|A|@]|B|$]]]]

I have to agree with delawen's answer but using st_distance on its own will be very slow. So to speed up things you will have to make use of GIST indexes (note most PostGIS function incl. st_distance DO NOT make use of indexes see: <a href="http://postgis.refractions.net/documentation/manual-1.5/ch04.html#id2628347" rel="nofollow">postgis indexing recommendation</a>).

So you would first create a buffer around the point and then check its bounding box using "&amp;&amp;" (this makes use of built in GIST index - so it would perform much much better ) and then you would check distance by "st_distance".

For example to get nearest "restaurant" from a given cood location (e.g. X=1,Y=1) you would write:

<pre><code>select *,st_distance(the_geom_col,st_geomfromtext('POINT(1 1)',27700)) as distance 
from restaurants where st_buffer(st_geomfromtext('POINT(1 1)',27700),100) 
&amp;&amp; "the_geom_col"
</code></pre>

This would be very fast compared to "st_distance" but results may contain restaurants that are more than 100 meters from the given location (esp. when geometries are kept in line or polygon formats).

To get more accurate results, restaurants that are exactly with in 100 meters range, you append following to the query given above:

<pre><code>and st_distance(the_geom_col,st_geomfromtext('POINTFROMTEXT(1 1)',27700)) &lt;= 100
</code></pre>

This will still be more efficient and faster than using st_distance on its own. Because database will only run st_distance against records that fulfil the first criteria.

So as a rule of thumb, whenever you have to perform expensive spatial lookups try to:

<ul>
<li>Filter out as many false results as possible by making use of special operators (see special operations in <a href="http://postgis.refractions.net/documentation/manual-1.5/reference.html" rel="nofollow">official postgis docs</a>.</li>
<li>Then write actual spatial relationship checker function.</li>
<li>Always have GIST index on your "geometry" column.</li>
<li>Add bounding boxes to your "geometries" by using st_addbbox.</li>
<li>Routinely reindex and vacuum/analyze your tables. </li>
</ul>

Note: Size of buffer or actual distance has to be in projection system you are using i.e. if you are using EPSG:4326 (Lat/Long) then you have give these distances in degrees. For example 
1 meter in real world = 0.00000899 degrees .. and 100 meters = do the maths :)

entityMap|blocks|key|arubg|text|PostGIS+2.0和更高版本可以使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|8hsh4|KNN近邻搜索|e6657|以获得最近的质心。例如：|clhpp|SELECT+ST_Distance(geom,+'SRID=26910;POINT(34.5+-23.2)'::geometry)+AS+d
FROM+mypoints
ORDER+BY+geom+<->+'SRID=26910;POINT(34.5+-23.2)'::geometry
LIMIT+1;|code-block|syntax|javascript^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|L|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|M|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|N|9|@]|A|@]|B|$]]|$2|G|4|H|6|I|8|O|9|@]|A|@]|B|$J|K]]]]

PostGIS 2.0 and later can use <a href="https://postgis.net/workshops/postgis-intro/knn.html" rel="nofollow noreferrer">KNN nearest-neighbour searching</a> to get the nearest centroids. For example:
<pre><code>SELECT ST_Distance(geom, 'SRID=26910;POINT(34.5 -23.2)'::geometry) AS d
FROM mypoints
ORDER BY geom &lt;-&gt; 'SRID=26910;POINT(34.5 -23.2)'::geometry
LIMIT 1;
</code></pre>

entityMap|blocks|key|ef9n1|text|PostgreSQL/PostGIS用户应在ORDER+BY子句中使用"<->“运算符来获取"K近邻”(KNN)，如上所述|type|unstyled|depth|inlineStyleRanges|entityRanges|data|9g7vb|Mike+T|dvh3d|在|b510f|这个答案|enccu|..。|1qhvu|要从GeoDjango中的KNN-GiST性能改进中获益，您可以编写以下代码：|20g7a|from+django.contrib.gis.db.models.functions+import+GeomValue
from+django.contrib.gis.geos+import+Point
from+django.db.models.expressions+import+CombinedExpression,+F

knn+=+10
longitude+=+-96.876369
latitude+=+29.905320
pnt+=+Point(longitude,+latitude,+srid=4326)
order_by_expression+=+CombinedExpression(F('geom'),+'<->',+GeomValue(pnt))
nearest_neighbors+=+Neighbor.objects.order_by(order_by_expression)[:knn]|code-block|syntax|javascript^0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|R|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|S|9|@]|A|@]|B|$]]|$2|E|4|F|6|7|8|T|9|@]|A|@]|B|$]]|$2|G|4|H|6|7|8|U|9|@]|A|@]|B|$]]|$2|I|4|J|6|7|8|V|9|@]|A|@]|B|$]]|$2|K|4|L|6|7|8|W|9|@]|A|@]|B|$]]|$2|M|4|N|6|O|8|X|9|@]|A|@]|B|$P|Q]]]]

PostgreSQL/PostGIS users should use "&lt;->" operator in ORDER BY clause to get "K nearest neighbors" (KNN), as said <a href="https://stackoverflow.com/users/327026/mike-t">Mike T</a> in <a href="https://stackoverflow.com/a/7947019/3018084">this answer</a>.

To get benefit from KNN-GiST performance improvement in GeoDjango you may write as following:

<pre><code>from django.contrib.gis.db.models.functions import GeomValue
from django.contrib.gis.geos import Point
from django.db.models.expressions import CombinedExpression, F

knn = 10
longitude = -96.876369
latitude = 29.905320
pnt = Point(longitude, latitude, srid=4326)
order_by_expression = CombinedExpression(F('geom'), '&lt;-&gt;', GeomValue(pnt))
nearest_neighbors = Neighbor.objects.order_by(order_by_expression)[:knn]
</code></pre>

entityMap|blocks|key|3f4u0|text|使用PostGIS和GeoDjango查找最近的邻居|type|unstyled|depth|inlineStyleRanges|entityRanges|data|c0hau|考虑这个模型：|3sb1g|from+django.contrib.gis.geos+import+Point
from+django.contrib.gis.db+import+models

++++class+Store(models.Model):
++++++++name+=+models.CharField(max_length=100)
++++++++location+=+models.PointField(geography=True,+srid=4326)
++++++++longitude+=+models.FloatField()
++++++++latitude+=+models.FloatField()
++++++++objects+=+models.GeoManager()
++++def+save(self,+**kwargs):
++++++++self.location+=+Point(self.longitude,+self.latitude)
++++++++super(Store,+self).save(**kwargs)|code-block|syntax|javascript|25jk3|在视图中获取指定经度/纬度100英里半径内的所有兴趣点：|bbbr3|from+django.contrib.gis.geos+import+Point
from+django.contrib.gis.measure+import+D

point+=+Point(lng,+lat)
points+=+Store.objects.filter(
++++location__distance_lte=(point,+D(mi=100))
).distance(point).order_by('distance')|bin0s|在模板中显示结果：|b7vkp|{%25+for+point+in+points+%25}
++++{{+point.name+}}+is+{{+point.distance.mi%7Cfloatformat+}}+miles+away
++++{%25+endfor+%25}^0|0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|R|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|S|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|T|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|U|9|@]|A|@]|B|$]]|$2|L|4|M|6|G|8|V|9|@]|A|@]|B|$H|I]]|$2|N|4|O|6|7|8|W|9|@]|A|@]|B|$]]|$2|P|4|Q|6|G|8|X|9|@]|A|@]|B|$H|I]]]]

Using PostGIS and GeoDjango to find your nearest neighbour

Consider this the model:

<pre><code>from django.contrib.gis.geos import Point
from django.contrib.gis.db import models

 class Store(models.Model):
 name = models.CharField(max_length=100)
 location = models.PointField(geography=True, srid=4326)
 longitude = models.FloatField()
 latitude = models.FloatField()
 objects = models.GeoManager()
 def save(self, **kwargs):
 self.location = Point(self.longitude, self.latitude)
 super(Store, self).save(**kwargs)
</code></pre>

In a view get all the points of interest within a 100 mile radius of a specified longitude / latitude:

<pre><code>from django.contrib.gis.geos import Point
from django.contrib.gis.measure import D

point = Point(lng, lat)
points = Store.objects.filter(
 location__distance_lte=(point, D(mi=100))
).distance(point).order_by('distance')
</code></pre>

Show the results in a template:

<pre><code>&lt;ul&gt;
 {% for point in points %}
 &lt;li&gt;{{ point.name }} is {{ point.distance.mi|floatformat }} miles away&lt;/li&gt;
 {% endfor %}
&lt;/ul&gt;
</code></pre>

I am using GeoDjango with PostGIS. Then I am into trouble on how to get the nearest record from the given coordinates from my postgres db table.

How can I query the nearest record in a given coordinates(latitude and longitude of string type)?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 我在PostGIS中使用GeoDjango。然后，我陷入了如何从postgres数据库表中获取与给定坐标最近的记录的麻烦。

问如何查询给定坐标(字符串类型的纬度和经度)中最近的记录？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何查询给定坐标(字符串类型的纬度和经度)中最近的记录？EN