CODEWITHSUNDEEP
postgresqlpostgis
Paul Grenyer
Paul Grenyer

Reputation: 1853

PostGIS Query always brings back all results

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?

Upvotes: 1

Views: 129

Answers (2)

Jim Jones
Jim Jones

Reputation: 19643

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

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 247225

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

Upvotes: 2

Related Questions