# 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