Query PostGIS radius giving me odd results

Question

I'm new to PostGIS and I'm trying to create a radius query. I have a table with a geometry field (position) and also have the latitude and longitude values on separate fields.

I'm trying to find points on a 10 km radius from lat: 40.753777, lon: -73.981568.

with:

SELECT postcode, lat, lon, st_asgeojson(position) geojson, ST_Distance(ST_MakePoint(lat, lon), ST_MakePoint(40.753777, -73.981568)) distance FROM addresses WHERE ST_DWithin(ST_MakePoint(lat, lon), ST_MakePoint(40.753777, -73.981568), 10000) order by id limit 10;

The results give me very far a way points. The same query with earth distance using the lat and lon directly give me much closer results.

SELECT postcode, lat, lon, st_asgeojson(position) geojson FROM addresses WHERE earth_box(ll_to_earth(40.753777, -73.981568), 10000) @> ll_to_earth(addresses.lat, addresses.lon) order by id limit 10;

But I really don't know if this is right either, what's wrong with the PostGIS query?

Longitude and latitude have nothing to do with meters, so I don't think that is surprising. If the data points are too distributed to be reasonably transformed to an appropriate projection, you could use geography. — Laurenz Albe
On a side note, points must be created using long first, then lat — JGH
Hi @LaurenzAlbe thanks for your reply, I thought for distances could be calculated on geometry fields, to get distances from a point Geography must be used? Do you know which SRID do I use to find by meters? — josesuero
@JGH yes, my geo json created out of lat 42.035149 and lon -78.8768906 resulted in {"type":"Point","coordinates":[-78.8768906,42.035149]} — josesuero

antimirov antimirov · Accepted Answer · 2019-06-28T22:46:20

A few notes:

I think you swapped the lat and lon when you made a point, your line says ST_MakePoint(40.753777, -73.981568), but the definition is:

geometry ST_MakePoint(double precision x, double precision y);
Note: x is longitude and y is latitude

So it should have been ST_MakePoint(-73.981568, 40.753777) instead.

As a simple solution you can use ST_Distance_Spheroid function ( http://www.postgis.org/docs/ST_Distance_Spheroid.html ):

SELECT
    postcode, lat, lon, st_asgeojson(position) AS geojson,
    ST_Distance_Spheroid(
        position,
        ST_GeomFromText('POINT(-73.981568 40.753777)',
        4326), 'SPHEROID["WGS 84",6378137,298.257223563]'
    ) as distance
FROM addresses 
WHERE distance < 10000 LIMIT 10;

For a more precise distances, add a new column of type geography from you existing column position of type geometry:

ALTER TABLE addresses ADD COLUMN position_geog geography(Point,4326);
UPDATE addresses SET position_geog = position::geography;
CREATE INDEX ON addresses USING gist(position_geog);
-- and now you can use ST_DWITHIN with meters...

Query PostGIS radius giving me odd results

1 Answers