I need to convert distance from meters to units of EPSG 3857 in PostGIS. This is important for me to calculate a radius for ST_DWITHIN as I'm dealing with geometries. How can I do this?
As an approximation suitable for medium-small radii, use the
cos()of the latitude to scale up the true distance to a "distance" that makes sense for your latitude in web mercator, as described here.
SELECT * FROM mytable WHERE ST_DWithin( ST_Transform(ST_SetSRID(ST_MakePoint(%lon, %lat), 4326), 3857), the_geom_webmercator, %radius / cos(%lat * pi()/180) );
You only want to do this for small/medium radii because the scaling only makes sense at one latitude. The further you get from your reference latitude, the more wrong your radius gets. If you want a "perfect" result, you could pair this approach with a test on
ST_DistanceSpheroid()to pare the results down to items exactly within the radius.