Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
share|improve this question
    
Can you use the distance in your WHERE clause? Like comparing it with a threshold? –  dezso Mar 11 at 7:54
    
can you please elaborate on that I'm not sure that I understand what you mean? thanks –  Gandalf StormCrow Mar 11 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 at 14:56

1 Answer 1

up vote 6 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):

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 neighbor" search. Often more convenient.

share|improve this answer
2  
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 at 20:22
    
@LR1234567: Thanks for pointing that out. I updated the answer accordingly. –  Erwin Brandstetter Mar 12 at 20:49
    
+1 + acc, pretty awesome answer I hope that will help many people as well –  Gandalf StormCrow Mar 12 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 at 19:37
    
@GandalfStormCrow: Yes, as demonstrated in my last query. –  Erwin Brandstetter Apr 24 at 21:58

Your Answer

 
discard

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.