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

Question

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.

Answer 1

PostGIS 2.0 and later can use KNN nearest-neighbour searching to get the nearest centroids. For example:

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;

Answer 2

Using PostGIS and GeoDjango to find your nearest neighbour

Consider this the model:

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)

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

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')

Show the results in a template:

<ul>
    {% for point in points %}
    <li>{{ point.name }} is {{ point.distance.mi|floatformat }} miles away</li>
    {% endfor %}
</ul>

Answer 3

PostgreSQL/PostGIS users should use "<->" operator in ORDER BY clause to get "K nearest neighbors" (KNN), as said Mike T in this answer.

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

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]

Answer 4

This this the answer using GeoDjango with PostGIS

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

from django.contrib.gis.geos import GEOSGeometry
point = GEOSGeometry('POINT(5 23)')

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

Restaurants.objects.distance(point).order_by('distance')[0] 

Answer 5

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: postgis indexing recommendation).

So you would first create a buffer around the point and then check its bounding box using "&&" (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:

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"

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:

and st_distance(the_geom_col,st_geomfromtext('POINTFROMTEXT(1 1)',27700)) <= 100

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:

• Filter out as many false results as possible by making use of special operators (see special operations in official postgis docs.
• Then write actual spatial relationship checker function.
• Always have GIST index on your "geometry" column.
• Add bounding boxes to your "geometries" by using st_addbbox.
• Routinely reindex and vacuum/analyze your tables.

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 :)

Answer 6

I have no experience with GeoDjango, but on PostgreSQL/PostGIS you have the st_distance(..) function. So, you can order your results by st_distance(geom_column, your_coordinates) asc 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.