PostGIS ST_PointInsideCircle radius units config

Question

I have a table with a lat and a long float columns and I'm trying to use the ST_PointInsideCircle PostGIS method which is working but the radius I enter is not in the correct units. I'm trying to config the radius to be in meters, all of the configuration queries I saw with setting a different SRID was for tables with geom point var columns . I tried running something like this query but it does not work for me the units are still probably in feet or degrees for example

SELECT * 
FROM box nbe 
WHERE ST_PointInsideCircle(ST_Transform(st_point(nbe.longitude,nbe.latitude),3857), -91.973998, 29.978479, 1000)

those are example coordinates they are random

Answer 1

Use st_dwithin instead. Geometry in a geographic coordinate system is using degrees as the units, which is why your 1000 is not working. Since you want to use meters, cast the geometry to geography, like this:

SELECT * 
FROM box nbe 
WHERE ST_DWithin(ST_Transform(st_point(nbe.longitude,nbe.latitude,3857),4326)::geography, st_setsrid(st_makepoint(-91.973998, 29.978479),4326)::geography, 1000)

If nbe.longitude / nbe.latitude are already srid 4326 (WGS 84) then you would do this instead:

SELECT * 
FROM box nbe 
WHERE ST_DWithin(st_point(nbe.longitude,nbe.latitude,4326)::geography, st_setsrid(st_makepoint(-91.973998, 29.978479),4326)::geography, 1000)

Regarding casting, according to the docs

The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage.

Cast to geography using cast syntax:

st_dwithin(cast(st_point(nbe.longitude,nbe.latitude,4326) as geography),cast(st_point(-91.973998, 29.978479, 4326) as geography),1000)