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

Question

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)

unless you have an address in Antarctica, it should be POINT(-76.984722 39.807222) — Mike T
This is just an example geolocation. It could be any other coordinates — transGLUKator

Edudjr Edudjr · Accepted Answer · 2017-09-03T20:59:38

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

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

1 Answers