[GIS] PostGIS/GeoDjango: given a list of locations, how to find closest point of interest in a different table

geodjangopostgis

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

Best Answer

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 = [get_cbusstop(bp) for bp in base_points_list]
Related Question