PostGIS Query always brings back all results

Question

I'm playing with PostGIS for the first time and I'm getting all the results back regardless of the distance I use on a distance query. My data looks like this:

Id                                      GeoLocation
8eb63480-4d63-11ea-b06a-8c1645ef6ad2    POINT (52.6323202 1.2947649)
a0f2dde6-4d64-11ea-b06a-8c1645ef6ad2    POINT (52.6294342 1.2936336)
a0f2dde7-4d64-11ea-b06a-8c1645ef6ad2    POINT (52.6277909 1.2909079)
a0f2dde8-4d64-11ea-b06a-8c1645ef6ad2    POINT (52.6260535 1.2952051)

And when I run a query for a point that should be over a mile away:

SELECT * FROM "Locations"   WHERE ST_DWithin("GeoLocation", 'POINT(52.6219322 1.2630061)', 1);

I get all of the rows back. My understanding is that the distance parameter should be in metres, so I shouldn't get any results back.

Could it be coordinate format issue? What am I missing?

`dwithin` use the same unit as the geometry CRS, so degrees in your example. All features are within 1 degree of the given coordinate. Cast to geography or use a different projection to use meters. — JGH, Feb 12 '20 at 13:25

score 2 · Answer 1 · answered Feb 12 '20 at 07:44

2

Since these seem to be coordinates in longitude and latitude, you should use the geography data type.

answered Feb 12 '20 at 07:44

Laurenz Albe

209,280
17
206
263

Jim Jones · Accepted Answer · 2020-02-14T08:33:26.540

Using parameters of type geography you get the returned distance in meters, therefore you need to convert it to miles in case you prefer to work with this unit of measurement. If you can cope with degrees, just stick to geometry.

WITH locations (geolocation) AS (
  VALUES ('POINT (52.6323202 1.2947649)'),
         ('POINT (52.6294342 1.2936336)'),
         ('POINT (52.6277909 1.2909079)'),
         ('POINT (52.6260535 1.2952051)')
) 
SELECT *
FROM locations 
WHERE ST_DWithin(
          geoLocation::geography, 
          'POINT(52.6219322 1.2630061)'::geography, 1609*2.2) ;

         geolocation          
------------------------------
 POINT (52.6294342 1.2936336)
 POINT (52.6277909 1.2909079)
(2 Zeilen)

EDIT: @JGH pointed out that ST_Distance does not use a spatial index and my previous suggestion was to use it instead of ST_DWithin. It means I was wrong with my preference for ST_Distance :) Here is anyway how to achieve similar results with ST_Distance for those still willing to use it:

WITH locations (geolocation) AS (
  VALUES ('POINT (52.6323202 1.2947649)'),
         ('POINT (52.6294342 1.2936336)'),
         ('POINT (52.6277909 1.2909079)'),
         ('POINT (52.6260535 1.2952051)')
) 
SELECT *
FROM locations 
WHERE ST_Distance(
          geoLocation::geography, 
          'POINT(52.6219322 1.2630061)'::geography) * 0.000621371 > 2.2 ;

         geolocation          
------------------------------
 POINT (52.6323202 1.2947649)
 POINT (52.6260535 1.2952051)
(2 Zeilen)

Further reading: Getting all Buildings in range of 5 miles from specified coordinates

let's emphasis that `st_distance` does not use a spatial index, while `st_dwithin` does. It is therefore much faster and should be used in `where` clauses. — JGH, Feb 12 '20 at 13:27
@JGH I didn't know that, thanks for pointing out. Just edited my answer accordingly — Jim Jones, Feb 12 '20 at 14:16
Converted to Geography and now SELECT * FROM "Locations" WHERE ST_DWithin("GeoLocation", 'POINT(52.6219322 1.2630061)'::geography, 3200); works as expected - thanks! — Paul Grenyer, Feb 14 '20 at 06:45

PostGIS Query always brings back all results

2 Answers2