Finding poi using st_dwithin in postgis

Question

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

Answer 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