- April 18, 2007
- distance gis postgis geography geometry nearby nearest
- 2 (after 2 ratings)
This code assumes a) that you are using PostgreSQL with PostGIS and b) that the geometry column in your model's table is populated with points, no other type of geometry.
It also returns a list instead of a QuerySet, because it was simpler to sort by distance from the given point and return that distance as part of the payload than to muck around with QuerySet. So bear in mind that any additional filtering, excluding, &c., is outside the scope of this code.
'Distance' is in the units of whatever spatial reference system you define, however if you do not intervene degrees decimal is used by default (SRID 4326, to be exact).
To get distance in units a little easier to work with, use a spatial ref close to your domain set: in my case, SRID 26971 defines a projection centered around Illinois, in meters. YMMV.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
# in your models.py or managers.py class PlaceManager(models.Manager): def nearest_to(self, point, number=5, geom_column='geom', from_srid=4326, to_srid=4326): """finds the model objects nearest to a given point `point` is a tuple of (x, y) in the spatial ref sys given by `from_srid` returns a list of tuples, sorted by increasing distance from the given `point`. each tuple being (model_object, dist), where distance is in the units of the spatial ref sys given by `to_srid`""" if not isinstance(point, tuple): raise TypeError from string import Template cursor = connection.cursor() x, y = point table = self.model._meta.db_table sql = Template(""" SELECT id, Length(Transform(SetSRID(MakeLine($geom_column, GeomFromText('POINT(' || $x || ' ' || $y || ')', $from_srid)), $from_srid), $to_srid)) FROM $table WHERE $geom_column IS NOT NULL ORDER BY Distance(GeomFromText('POINT($x $y)', $from_srid), $geom_column) ASC LIMIT $number; """) cursor.execute(sql.substitute(locals())) nearbys = cursor.fetchall() # get a list of primary keys of the nearby model objects ids = [p for p in nearbys] # get a list of distances from the model objects dists = [p for p in nearbys] places = self.filter(id__in=ids) # the QuerySet comes back in an undefined order; let's # order it by distance from the given point def order_by(objects, listing, name): """a convenience method that takes a list of objects, and orders them by comparing an attribute given by `name` to a sorted listing of values of the same length.""" sorted =  for i in listing: for obj in objects: if getattr(obj, name) == i: sorted.append(obj) return sorted return zip(order_by(places, ids, 'id'), dists) # ex. REPL >>> Places.objects.nearest_to((-87.96, 42.06), number=5, to_srid=26971) [(<Place: 600 S See Gwun Ave, Mount Prospect, IL 60056, USA>, 1908.34541121444), (<Place: 48 Raupp Blvd, Buffalo Grove, IL 60089, USA>, 9209.1395558741897), (<Place: 1000 S Milwaukee Ave, IL 60090, USA>, 7996.2420822250497), (<Place: 1175 Howard Ave, Des Plaines, IL 60018, USA>, 8095.9994070327703), (<Place: 701 Thorndale Ave, Wood Dale, IL 60191, USA>, 9613.4684521103609)]