7

I am not familiar with gis or writing sql, so my understanding in this field is lacking. I have a hundreds of lat/lng geographic coordinates stored in a single table called Places like this:

ID | Coordinate
-----------------------------
1  |  POINT(192.938 -28.348)
2  |  POINT(57.349 -88.482)

They are in 4326, and I'm using geography type. I want to find all nearby points that are within 10 miles of a given point.

I want to use st_dwithin(), but I don't understand how to use it. I came up with this, but it doesn't seem quite right:

SELECT * FROM places WHERE ST_DWithin(places.Coordinate, ST_GeographyFromText('POINT(195.888 -28.348)'), 16093.4);

16093.4 metres is 10 miles.

How do I get all points within 10 miles of a given point (in this example, from POINT(195.888 -28.348))?

  • 1
    Your SQL looks right. Your geography representations do not though. The ST_GeographyFromText call you have should error out, since a ',' is not used to separate the longitude from the latitude. – Paul Ramsey Aug 26 '13 at 4:53
  • @PaulRamsey sorry, I had mistyped the comma. I edited the post. If ST_DWithin() takes two geographies, then why does using places.Coordinate seem okay? Also, I found other threads using a mix of ST_Distance() and ST_DWithin(). I don't understand why ST_Distance was used? Is it applicable for my problem? – user21398 Aug 26 '13 at 6:16
  • 2
    ST_DWithin is a true/false function. And since all you want to know is "what is within this radius" a true/false test is all you need. If you also wanted to know "and how far are they" then you'd pull out ST_Distance too. For efficiency, use the simplest function that fulfills your need. ST_DWithin has lots of efficiency smarts under the covers. – Paul Ramsey Aug 27 '13 at 17:32
  • 1
    It appears that this question was answered by Paul's comments and abandoned. – kttii Oct 17 '16 at 15:49
  • I have no idea what you're asking, why doesn't it "seem right." – Evan Carroll Apr 23 '17 at 1:38
1

long/lat > 180

I'm not sure where you're getting these numbers, but..

  • latitude is [-90 90]
  • longitude is [-180 180]
  • Anything out of those ranges gets wrapped.

Here is what they get wrapped to.

SELECT ST_AsText(str::geography)
FROM ( VALUES
  ('POINT(192.938 -28.348)'),
  ('POINT(195.888 -28.348)')
)  AS t(str);
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY

        st_astext        
-------------------------
 POINT(-167.062 -28.348)
 POINT(-164.112 -28.348)

PostGIS coerces these into the proper range, 181 gets wrapped to -179.

Works fine.

SELECT
  id,
  coordinate,
  v2.geogwkt,
  ST_DWithin(
    coordinate::geography,
    geogwkt::geography,
    16093.4
  ),
  ST_Distance( coordinate::geography, geogwkt::geography )
FROM ( VALUES
  ( 1, 'POINT(192.938 -28.348)' ),
  ( 2, 'POINT(57.349 -88.482)' )
) AS v1(id, coordinate)
CROSS JOIN ( VALUES ('POINT(195.888 -28.348)') )
  AS v2(geogwkt);

 id |       coordinate       |        geogwkt         | st_dwithin |   st_distance    
----+------------------------+------------------------+------------+------------------
  1 | POINT(192.938 -28.348) | POINT(195.888 -28.348) | f          |  289222.77513405
  2 | POINT(57.349 -88.482)  | POINT(195.888 -28.348) | f          | 6992543.20903735
(2 rows)

ST_Distance is giving the result back in meters. One of your points, is 289KM away. The other point is 6,992KM away. Both of them are further than a single mile.

;tldr Nothing wrong

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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