Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

If I have a query returning nearby cafes:

     'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
   ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),

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)'),
             'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')')      
             ) as distance 
 FROM cafes c
   WHERE (
     'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
    ST_GeographyFromText('SRID=4326;POINT(-76.000000 39.000000)'),
 ) order by distance
share|improve this question
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
up vote 7 down vote accepted

First, use

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

instead of

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):

     , 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.

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.