Order by distance

15 votes

1 answer

9273 views

postgresql postgresql-9.2 postgis gist-index

                          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
                        

Asked by Gandalf StormCrow (615 rep)

Mar 11, 2014, 02:12 AM
Last activity: Nov 2, 2021, 11:44 PM

Order by distance

Related Questions