I write new locations with:
ST_SetSrid(ST_MakePoint(:longitude, :latitude), 4326)
They get saved under "profile".location:
SRID=4326;POINT(-75.1234 35.1234)
I try to filter profiles within some :radius in meters where :ownLocation is some location string that consists of a bunch of numbers:
ST_DWithin("profile".location, :ownLocation::geometry, :radius)
Then when I get the results back and calculate their distance with:
ST_Distance(
ST_Transform("profile".location::geometry, 3857::int), ST_Transform(:ownLocation::geometry, 3857::int)
) as distance
and convert the distance from meters to miles, my results are a bit off. Let's say I set a max radius of within 10 miles - the distance I get back seems anywhere from 0-23 miles (would expect 0-10 miles).
I am wondering where I am going wrong here, and have a feeling it may have to do with projections, or I am using the functions incorrectly.
Update after solution: store as 4326, display distance as 3857
store as 4326:
ST_SetSrid(ST_MakePoint(:longitude, :latitude), 4326)
filter using geography type so it can accept filter radius as meters that users will pass in:
ST_DWithin("profile".location::geography, :ownLocation::geography, :radiusInMeters)
display distance from 4326 to 3857 meters with correction (https://postgis.net/docs/ST_Distance.html):
ST_Distance(
ST_Transform("profile".location::geometry, 3857),
ST_Transform(:ownLocation::geometry, 3857)
) * cosd(42.3521) as distance
3857 is not suitable for computing distances, as it introduces important distortions as you move away from the equator.
Instead, you can use ST_Distance using the geography datatype:
ST_Distance("profile".location::geography,:ownLocation::geography) as distance
Regarding st_dwithin, it uses the projection unit, which is degrees, not meters. You can also use the geography datatype here.