How to order by distance?

[nlassaux]

I'm using distance operators in the ORDER BY, because those use geographic indexes better than their function equivalents:

SELECT
    id
FROM
    house h
ORDER BY
    ST_SetSRID(ST_MakePoint(%s, %s), 4326) <-> h.point

Unfortunately, I don't seem to find a correct way to define that custom distance. It looks like defining a custom function assumes a prefix expression notation function_name(param1, param2), when I'd like a postfix version.

Has anyone covered a similar case? Thanks!

[williambdean]

What is the function here that doesn't meet the syntax function_name(param1, param2)?

CustomFunction seems to cover the two I see in your example

[nlassaux]

In that case it's the operator A <-> B that'd need to be computed. The functions before are all right, sorry :)

[williambdean]

For that, I think you can get away with a custom ArithmeticExpression from pypika.terms. It'd be similar to how the primary operations are defined

left = ... # custom functions
right = ... # point column
order_by = ArithmeticExpression("<->", left, right)

# define query

EDIT:

ArithmeticExpression is used with enum which has a value attribute. A simple wrapper class can be used to have same attribute

from pypika import CustomFunction, Field, Query
from pypika.terms import ArithmeticExpression

class NewOperation: 
    value: str = "<->"

ST_SetSRID  = CustomFunction("ST_SetSRID", ["col", "value"])
ST_MakePoint = CustomFunction("ST_MakePoint", ["left", "right"])

left = ST_SetSRID(ST_MakePoint(Field("left"), Field("right")), 4326)
right = Field("point")
orderby = ArithmeticExpression(NewOperation, left, right)

query = Query.from_("house").select("id").orderby(orderby)

[nlassaux]

That's exactly what's needed, thanks! It would be great if it was in the doc, so I opened a PR to add that: #779

[nlassaux]

Issue solved!