0

I am querying the PostGres database using POSTGIS extension as I want to get users in a radius.

I am following this post, where the following code is working fine:

Alert.findAll({
  where: Sequelize.where(
    Sequelize.fn('ST_DWithin',
      Sequelize.col('position'),
      Sequelize.fn('ST_SetSRID',
        Sequelize.fn('ST_MakePoint',
          req.query.long, req.query.lat),
        4326),
      0.032),
    true)
})
.then(alerts => res.send(alerts))
.catch(next)

But I am encountering a strange problem, the radius is defined in degrees not miles, e.g. they said

Sample query to find all within 0.032 deg which is approximately 2 miles.

Then to customize the radius & validate my solution against above statement, I searched a lot and I wasn't able to find any formula to convert my miles into radius say I want to check in the radius of 10 miles. Then I checked this stack question, and run some calculations based on the formulas given in the accepted answer, using miles as 2 instead of 100, just to ensure if the approximation is correct but the answer was 0.42 not 0.32. e.g.

const latTraveledMiles = 2;
const latTraveledKM = 2 * 0.621371;
const latTraveledDeg = (1 / 110.54) * latTraveledKM;

const currentLat = 74.0064;
const longTraveledMiles = 2;
const longTraveledKM = 2 * 0.621371;
const longTraveledDeg = (1 / (111.320 * Math.cos(currentLat))) * longTraveledKM;

let degrees = Math.sqrt(Math.pow(latTraveledDeg, 2), Math.pow(longTraveledDeg, 2)); //should be 0.32
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zain Ul Abidin
  • 2,467
  • 1
  • 17
  • 29

1 Answers1

1

To make things easier, I would suggest you cast the geometry column position and ST_MakePoint to geography which will allow you to use metric input. You can read more about it here https://www.crunchydata.com/blog/postgis-and-the-geography-type

To add a geography column to your table you can use the following statement

-- first you add the column of type geography
ALTER TABLE your_table ADD COLUMN geog geography(point, 4326);
-- afterwards you populate it from your geometry column which is position
UPDATE your_table SET geog = position::geography;

You can now use ST_DWithin(geography gg1, geography gg2, double precision distance_meters); in your javascript logic, hence just substitute your radius value with 2*1.609344*1000 which translates to 2 miles times the factor to kilometers times 1000 to yield that value in meters.

Timothy Dalton
  • 1,290
  • 2
  • 17
  • 24