[GIS] How to handle distance in postgis using ST_DWithin

postgispostgresql

I'm super new with this subject and was assigned the task of creating a query to find objects around a given point using some type of radius search using Postgresql with PostGis. After some research I came across the ST_DWithin method which is perfect for this.

I'm currently having some issues with the query. Here is how it looks like:

SELECT * FROM waypoints WHERE ST_DWithin(lonlat, '002000000100000f11c05e9335158b82804047dcae85b9e8c4', 10)

This is a table that stores lonlat using SRID=3857 and I'm creating the second geometry using FACTORY.point(circle.fetch('lon'), circle.fetch('lat')) from Ruby RGEO

Now the documentation states that if I'm using geometries then the third parameter distance_of_srid should be in the units of the geometries.

The problem is that both lonlat and the point are using SRID 3857 which from what I understood correctly, uses units in meters. Logically I would expect that distance_of_srid would accept distances in meters. But when I send the query I get waypoints that are kilometers away not 10 meter away. But if I break it down to say .01 then it brings waypoints that are within 10 meters or at least that's what it seems.

I feel like I'm lacking some knowledge to overcome this but not sure what it is or where is a good place to get more acquainted with this subject. If someone could help me with this issue or point me to a better direction that would be awesome!

Update:

Based on a comment below I tried doing the following:

ST_DWithin(ST_Transform(archived_listings.lonlat, 4326)::geography, ST_GeogFromText('SRID=4326;POINT(-122.300115 47.724076)'), 10000.0)

But unfortunately I'm getting nothing with 10 km as before I was getting a house that was closer than that.

Best Answer

After some further debugging and taking into account the comments in the question. I realized that the data stored in the lanlot column was actually SRID=4326 even do the column states that it's a SRID=3857 geometry.

I came to this conclusion because when I plugged the coordinates as a 3857 in a map I was placed very close to the null island. But when I put the same coordinates as 4326 I was place in the correct location.

Since we already had over 1 million entries on the production database there was no time to change the column so the query ended up been something like:

SELECT * FROM waypoints 
WHERE ST_DWithin(ST_SetSRID(lonlat, 4326)::geography, 
                 ST_GeomFromText('POINT(-122.300045 47.723779)', 4326)::geography, 
                 10)

And it works!!

Related Question