# Order by distance

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

• Can you use the distance in your WHERE clause? Like comparing it with a threshold? – dezso Mar 11 '14 at 7:54
• can you please elaborate on that I'm not sure that I understand what you mean? thanks – Gandalf StormCrow Mar 11 '14 at 14:47
• I was thinking that a point being inside a geometry or nor farther than a given distance may be more or less equivalent in some cases. – dezso Mar 11 '14 at 14:56

First, use

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


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

Per documentation:

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. Per documentation 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?

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

• You shouldn't mix geometry with geography. You should do: ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography - note that ST_MakePoint returns a geometry. – LR1234567 Mar 12 '14 at 20:22
• @LR1234567: Thanks for pointing that out. I updated the answer accordingly. – Erwin Brandstetter Mar 12 '14 at 20:49
• +1 + acc, pretty awesome answer I hope that will help many people as well – Gandalf StormCrow Mar 12 '14 at 21:47
• Hi Erwin I did everything like you wrote, I created a geography column (thegeog), created index and I dumped all the latlongs into it UPDATE cafes SET thegeog = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326); Do I still need to use that part where I select ST_GeographyFromText .... AS t(x) now that I have this column populated? Can I take advantage of that column and ask nearby that directly instead of specifying lat/long? – Gandalf StormCrow Apr 24 '14 at 19:37
• @GandalfStormCrow: Yes, as demonstrated in my last query. – Erwin Brandstetter Apr 24 '14 at 21:58