1

in my database on PostgreSQL/PostGIS I've two tables: a "location" table which main geo field is a MultiPolygon and a "busstop" table which main geo field is a Point.

Given a base point, I would like both to filter all the location within a certain distance from that point (I already know how to do that) and find the closest bus stop with its distance to that point.

Basically I would like to have back a recordset like this:

--------------------------------------
| location_1 | bus_stop_234 | 1.1 Km |
--------------------------------------
| location_2 | bus_stop_106 | 1.2 Km |
--------------------------------------
| location_3 | bus_stop_827 | 1.6 Km |
--------------------------------------
| location_4 | bus_stop_365 | 1.8 Km |
--------------------------------------

(Where location comes from location table and bus_stop from busstop table, while distance is calculated between multipolygon field of location and point field of bus stop.)

Even better if I can order the results by distance field, like in the example.

I need to do this using a GeoDjango query, but even a simple SQL query could be good to let me understand how to perform this kind of query.

Please note that there aren't any FK relations between the two tables.

Update: I still don't know how to do this with Django ORM and it's still not the exact query I want, but at least I was able to write a SQL query that returns the locations with the bus stops within 200 meters. I would want just the first (with closest bus stop) location/busstop for each location:

SELECT "api_location"."id", "api_location"."name", "api_location"."point", "api_location"."geom", "api_busstop"."amic_code",
ST_Distance("api_location"."geom", "api_busstop"."point") as distance
FROM "api_location", "api_busstop" 
WHERE ST_DWithin("api_location"."geom", ST_GeomFromEWKB('\001\001\000\000 \346\020\000\000\210\215\325\247\202\367\001\300q\216\350\015\272\301J@'::bytea), 2000.0)
    AND ST_DWithin("api_location"."geom", "api_busstop"."point", 200)
ORDER BY id, distance
  • not sure about geodjango but in your SQL query now you can use the LIMIT 1 clause. And order only by distance or write the id as second – ylka Nov 30 '16 at 11:58
1

To sort your geo-lookup by distance, you can do

queries = yourmodel.objects.filter(\
                    location__distance_lte=(base_point, D(m=certain_distance))\
          ).distance(base_point).order_by('distance')

Where location is the fieldname of the first column in your table, base_point is an instance of django.contrib.gis.geos.Point

Since it is sorted, to get the closest busstop you then just have to get the first element of queries as list. I mean

closest_busstop = list(queries)[0]
distance_to_closest_busstop = base_point.distance(closest_busstop)

UPDATE

Perhaps an expert will contradict me (and i would be glad), but it will be necessary to "target" the closest busstop from the returned queryset in one way or another. Given that you have a list of base points, something like this should do the job

django.VERSION < 1.10

get_cbusstop = lambda bpoint : list(yourmodel.objects.filter(\
                                        location__distance_lte=(bpoint, D(m=certain_distance))\
                                    ).distance(bpoint).order_by('distance')\
                                )[0]

django.VERSION > 1.9

from django.contrib.gis.db.models.functions import Distance
get_cbusstops= lambda bpoint : yourmodel.objects.filter(\
                                        location__distance_lte=(bpoint, D(m=certain_distance))\
                                    )\
                                   .annotate(distance=Distance('<busstop_shape_fieldname>',\
                                                               bpoint)\
                                             )
get_cbusstop = lambda bpoint : sorted(get_cbusstops(bpoint),\
                                      key=lambda obj_:obj_.distance\
                                     )[0]

For both version

Then, you can declare a list of base points, say base_points_list, formed over location_1,...,location_n, and do

closest_busstops = map(get_cbusstop,base_points_list)
  • Hi learner and thanks for your reply, but this is not what I was asking. If you look at the example table of data you will see that I need a query that can return the closest bus stop, not a list of queries where I have to get the closest bus stop individually. – Andrea Grandi Nov 30 '16 at 9:29
  • @AndreaGrandi. If you look at the answer, you will see that queries contains busstops sorted from the nearest to the most distant. Also, note that the example table of data is not a meaningfull example of what you really want since a "table of data" is not django-native object, and that you will have to map any returned queryset in one way or another to build it as such with your base_point-specific distances. – keepAlive Nov 30 '16 at 9:59
  • in your first part of the code example, you are ordering the locations by distance from the base point, this is not what I want. I've updated my question with a SQL query I wrote and that is returning the locations and the bus stop within 200 meters from each location. This is close to what I need to do, but it has to be only the closest bus stop for each location and it has to be expressed with Django ORM. Also: .distance(....) method doesn't exist on the queryset and 'distance' field neither (so you can't use it in an order_by. – Andrea Grandi Nov 30 '16 at 10:21
  • @AndreaGrandi. distance(...) method DOES exist if you put the following method to your model definition : objects = models.GeoManager(). Note my forthcoming update. – keepAlive Nov 30 '16 at 10:40
  • I'm afraid, but GeoManager has been deprecated since Django 1.9 docs.djangoproject.com/en/1.10/ref/contrib/gis/model-api/… (maybe I should have specified that I'm using Django 1.10) – Andrea Grandi Nov 30 '16 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.