0

For a given user, I'm trying to find out the other users who drive closest to them. For carpooling.
Here are my abbriviated models:

class Office(models.Model):
    location = PointField(srid=4326, geography=True)

class User(models.Model):
    office = fk(Office)
    home_location = PointField(srid=4326, geography=True)

In my head, I need to do a few things:

  • Annotate on a LineStringField of F('home_location')→F('office__location') to represent an as-the-crow-flies route to work.
  • Annotate on the shortest distance for each route from the given user's home_location.
  • Sort by that distance, desc.

It looks simple on paper but I'm stumbling at the first hurdle. How do I annotate on a LineStringField?

This seems like it would be a very common problem for anybody using GIS in Django.

Oli
  • 235,628
  • 64
  • 220
  • 299
  • (Not answering the actual question:) I think a better approach would be extracting some nodes (points) between "office" and "home" `(p1, p2... pn)`, extracting nodes for people seeking rides `(a1 a2...an; b1, b2... bn....)` and working with these sets. The node extraction should be smart (snap to grid/roads/junctions). – Udi Dec 07 '18 at 07:51
  • related, in shapely: https://stackoverflow.com/questions/24415806/coordinate-of-the-closest-point-on-a-line – Udi Dec 07 '18 at 07:56
  • Thanks @Udi. Shapely's `.project()` seems like it would let me do this in Python and then manually sort the points. However I'd much rather push this back to PostGIS. Your idea is good. I don't need to be too clever about roads (honestly this is napkin maths) but I do still need (want) to do this in-DB. – Oli Dec 07 '18 at 13:50
  • https://postgis.net/docs/ST_ClosestPoint.html may be the answer. It's amazing what you can Google when you know the words. – Oli Dec 07 '18 at 13:55
  • Crap, I still need to annotate a linestring before I can call that :( – Oli Dec 07 '18 at 13:57

0 Answers0