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 |
--------------------------------------
(whereWhere location comes from location tabeltable and bus_stop from busstop table, while distance is calculated between mulipolygonmultipolygon 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 lestleast I was able to write a SQL query that returns the locations with the bus stopstops 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
