Question 258562 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Sequelize, postgres and posgis: find activities within n° kilometers

Please help!
I have a project using Sequelize, postgres and postgis (for gps).
The schema (simplified) is:

export default class Activity extends Model {
  static init(sequelize) {
    return super.init({
      sequelize,
      tableName: 'activities',
    })
  }

  static get modelAttributes() {
    return {
      id: {
        type: DataTypes.UUID,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
      },

      activity_name: {
        type: 'citext',
        allowNull: false,
        unique: {
          args: true,
          msg: 'Activity name already in use.',
        },

      point: {
        type: DataTypes.GEOMETRY,
        allowNull: true,
      },
    }
  }
}

I don’t put the migration file, because this part works well: whene I save the data, it’s correctly registered.

await Activity.bulkCreate({
  activity_name: 'whatever,
  point: { type: 'Point', coordinates: [lat, lng] },
})

The problem comes when I try to get activities within a certain distance:

const result = await User.findAll({
      include: [
        {
          model: Activity,
          required: true,
          where: {
            point: Sequelize.fn(
              'ST_DWithin',
              Sequelize.fn(
                'ST_SetSRID',
                Sequelize.fn('ST_MakePoint', 40.119536, 8.495837),
                4326
              ),
              parseFloat(distance) // number 10000
            ),
          },
        },
      ],
    })

This code throws an error: "function st_dwithin(geometry, integer) does not exist".
I struggling with this problem since a while. Any solution?
thanks in advance!!!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks Anatoly for your answer. The code doesn't throw the error anymore, but it still doesn't work. No error, but it retrieves all activities of the table, despite the distance value. Any clue for a solution?

    The code looks like this now:

    const result = await User.findAll({
      include: [
        {
          model: Activity,
          required: true,

          where: {
            [Op.and]: [
              Sequelize.where(
                Sequelize.fn(
                  'ST_DWithin',
                  Sequelize.col('point'),
                  Sequelize.fn(
                    'ST_SetSRID',
                    Sequelize.fn('ST_MakePoint', ownCoordinates[0], ownCoordinates[1]),
                  4326
                  ),
                  parseFloat(distance) * 5000
                ),
                true
              ),
              // other queries here
            ],
          },
        },
      ],
    })

  2. 0

    You should pass point column as a first argument of ST_DWithin while you passed only one geometry to the function:

    where: Sequelize.where(Sequelize.fn(
              'ST_DWithin',
              Sequelize.col('point'),
              Sequelize.fn(
                'ST_SetSRID',
                Sequelize.fn('ST_MakePoint', 40.119536, 8.495837),
                4326
              ),
              parseFloat(distance) // number 10000
            ),
            '=',
            'true'
          )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search