Order by distance

Question

If I have a query returning nearby cafes:

SELECT * FROM cafes c WHERE (
   ST_DWithin(
   ST_GeographyFromText(
     'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
   ),
   ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),
     2000
   )
)

How do I select distance, and order by distance as well?
Is there a more efficient way than this one:

 SELECT id, 
 ST_Distance(ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),
             ST_GeographyFromText(
             'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')')      
             ) as distance 
 FROM cafes c
   WHERE (
   ST_DWithin(
     ST_GeographyFromText(
     'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
   ),
    ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),
   2000
 )
 ) order by distance

Answer 1

First, use

ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography

instead of

ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')')

The manual:

ST_MakePoint while not being OGC compliant is generally faster and more precise than ST_GeomFromText and ST_PointFromText. It is also easier to use if you have raw coordinates rather than WKT.

Next, to make the query shorter and only enter search parameters once (without much effect on performance), use a subquery (or CTE):

SELECT id
     , ST_Distance(t.x
                 , ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography) AS dist
FROM   cafes c
    , (SELECT ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)')) AS t(x)
WHERE  ST_DWithin(t.x
                , ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography, 2000)
ORDER  BY dist;

Finally, you need a GiST index to make this fast for big tables. The manual on ST_DWithin():

This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries.

You could get this to work with a functional index on the expression at the start of the answer. But I would store a geography type column to begin with (let's name it thegeog) and create a plain GiST index like:

CREATE INDEX cafes_thegeog_gist ON cafes USING gist(thegeog);

Arriving at this much simpler and faster query:

SELECT id, ST_Distance(t.x, thegeog) AS distance 
FROM   cafes c
    , (SELECT ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)')) AS t(x)
WHERE  ST_DWithin(t.x, thegeog, 2000)
ORDER  BY distance;

Updated to match geography with geography, as pointed out by @LR1234567 in the comment. As an alternative, you could work with geometry. All functions used here work for both (except for ST_MakePoint, hence the appended cast). What's the difference? See:

• Why is my spatial query slower in Postgres 13 than in Postgres 11?

If you want to get the n nearest cafes instead all within a radius, consider a "nearest neighbour" search. Often more convenient.