CODEWITHSUNDEEP
javascriptexpresspostgissequelize.js
transGLUKator
transGLUKator

Reputation: 652

How to correctly find objects by proximity using sequelize.js and PostGIS?

I'm trying to perform a simple ST_Dwithin search using sequelize.js and PostGIS. In my database I have 3 tables of interest: Users, Neighborhoods and Addresses. All geo data is stored inside addresses table, which has references to users and neighborhoods.

return Neighborhood.findById(id).then(neighborhood => {
  return neighborhood.getAddress().then(address => {
    return Address.findAll({
      where: sequelize.where(
        sequelize.fn(
          'ST_DWithin',
          sequelize.fn(
            'ST_Transform',
            address.position,
            26986),
          sequelize.fn('ST_Transform',
            sequelize.col('position'),
            26986),
          distance),
        true
      )
    })
  })
}).catch(err => new Error(err));

First I get the address of a neighborhood and then use sequelize.fn to query with PostGIS ST_DWithin function. However this throws an error TypeError: val.replace is not a function. I believe it is something with line address.position. The column position in the table Addresses stores geometry points with type GEOMETRY and srid 4326.

The function works correctly if instead of address.position I hard code something like sequelize.fn('ST_GeomFromText', 'POINT(39.807222 -76.984722)', 4326)

Upvotes: 1

Views: 2790

Answers (1)

Edudjr
Edudjr

Reputation: 1835

In my case, the geometry(point) attribute is in the User entity. This is what I got working:

var lat = parseFloat(json.lat);
var lng = parseFloat(json.lng);
var attributes = Object.keys(User.attributes);
var distanceAttribute = 
  sequelize.fn('ST_Distance_Sphere', 
                sequelize.literal('geolocation'), 
                sequelize.literal('ST_MakePoint('+lat+','+lng+')'));
var distanceAlias = [distanceAttribute, 'distance'];
attributes.push(distanceAlias);

var query = {
  attributes: attributes,
  where: sequelize.where(distanceAttribute, {$lte: 100000}),
  logging: console.log
}

User.findAll(query)
  .then(function(instance){
    console.log(instance);
  });

Which produces a SQL like this:

SELECT "user_id", "user_name" ... ST_Distance_Sphere(geolocation, 
ST_MakePoint(-22.4149023,-47.56513940000002)) AS "distance" 
FROM "user" AS "User" 
WHERE ST_Distance_Sphere(geolocation, 
ST_MakePoint(-22.4149023,-47.56513940000002)) <= 100000;

I think this should work for you too by changing User to Address

Upvotes: 2

Related Questions