CODEWITHSUNDEEP
node.jsgissequelize.js
Andre M
Andre M

Reputation: 7554

Sequelize, searching for point within bounding box

I have a need to search for any records where a point exists within a bounding box, but I am not sure how I would formulate this with Sequelize, for use with MariaDB. Currently Sequelize doesn't seem to have native support for geo searches, so I am hoping I can do it with an SQL function directly.

Currently my code looks as follows:

// latlon1 and latlon2 are of form [lat,lon]
// representing bottom-left and top-right corners of the box
var a = latlon1[0] + ' ' + latlon1[1];
var b = latlon2[0] + ' ' + latlon1[1];
var c = latlon2[0] + ' ' + latlon2[1];
var d = latlon1[0] + ' ' + latlon2[1];
var e = latlon1[0] + ' ' + latlon1[1];

var boundingBox = `${a},${b},${c},${d},${e}`;
var fn = `ST_CONTAINS(ST_MakePolygon(ST_GeomFromText('LINESTRING(${boundingBox})'), latlon)`;

Address.findAll( { ... } ).then(function (results) {
  // results will contain all records where the value of the 
  // latlon column is within the bounding box
  // latlon column is of type 'point'
});

With the above I have an SQL GIS function (which I believe is correct) and the findAll. The problem is I don't know how to merge the two, to provide me the results I am looking for? Can anyone help?

Using Sequelize 3.24.3, node.js 6.7.0 and MariaDB 10.0.

Upvotes: 2

Views: 1812

Answers (1)

Andre M
Andre M

Reputation: 7554

The solution I came up with is below. The sequelize.fn() is the way to specify a database based function and then just specify the column you are interested in using the sequelize.col() function.

// latlon1 and latlon2 are of form [lat,lon]
// representing bottom-left and top-right corners of the box
var a = latlon1[0] + ' ' + latlon1[1];
var b = latlon2[0] + ' ' + latlon1[1];
var c = latlon2[0] + ' ' + latlon2[1];
var d = latlon1[0] + ' ' + latlon2[1];
var e = latlon1[0] + ' ' + latlon1[1];

var boundingBox = `${a},${b},${c},${d},${e}`;

// convert the textual representation to a geometry
var geom = sequelize.fn('ST_GEOMFROMTEXT', `LINESTRING(${a},${b},${c},${d},${e})`);
// convert the geometry to a polygon and the do the contains
var contains = sequelize.fn('ST_CONTAINS',
    sequelize.fn('POLYGON', geom),
    sequelize.col('latlon')
);

Address.findAll( { 
  where: contains
  }).then(function (results) {
  // results will contain all records where the value of the 
  // latlon column is within the bounding box
  // latlon column is of type 'point'
});

Note, the SQL query could also be as follows, saving the need to do the conversion from geometry to polygon:

WHERE ST_CONTAINS(ST_POLYGONFROMTEXT('POLYGON((45.51195 -73.56973,45.51335 -73.56973,45.51335 -73.56584,45.51195 -73.56584,45.51195 -73.56973))'), `latlon`)

So as Sequelize code:

var contains = sequelize.fn('ST_CONTAINS',
    sequelize.fn('ST_POLYFROMTEXT', `POLYGON((${a},${b},${c},${d},${e}))`),
    sequelize.col('latlon')
);

Note, I haven't performance compared the two, so I can't say whether one is 'better' than the other.

*Note the code expects ES6, due to use of template strings.

Upvotes: 3

Related Questions