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