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

One Response to “Order by distance”


Leave a Reply

Question and Answer is proudly powered by WordPress.
Theme "The Fundamentals of Graphic Design" by Arjuna
Icons by FamFamFam