Login

PostGIS "nearest" including range limiting

Author:
lkcl
Posted:
July 24, 2008
Language:
Python
Version:
.96
Score:
1 (after 1 ratings)

this is an enhancement to paulsmith's code snippet 190, to provide an extra "search by distance" parameter rather than "limit number of results returned".

i need this for a social networking site where users can search for people within a certain geographical area.

 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
    def nearest_to(self, point, number=5, geom_column='latlon',
                   from_srid=2163, to_srid=2163,
                   within_range=None):
        """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
        distance_clause = ''
        if within_range:
            distance_clause = "AND (Distance(GeomFromText('POINT($x $y)', $from_srid), $geom_column) <= %f)" % within_range
        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 %s
            ORDER BY Distance(GeomFromText('POINT($x $y)', $from_srid), $geom_column) ASC
            LIMIT $number;
        """ % distance_clause)
        cursor.execute(sql.substitute(locals()))
        nearbys = cursor.fetchall()
        # get a list of primary keys of the nearby model objects
        ids = [p[0] for p in nearbys]
        # get a list of distances from the model objects
        dists = [p[1] 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)

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 2 months, 2 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 2 months, 3 weeks ago
  3. Serializer factory with Django Rest Framework by julio 9 months, 2 weeks ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 10 months, 1 week ago
  5. Help text hyperlinks by sa2812 11 months ago

Comments

Please login first before commenting.