Sunday, October 1, 2017

How to query places sorted by distance with lat, lng coordinates in MySQL with bookshelf.js or knex?

By Hường Hana 7:30 AM , , , , Leave a Comment

Is it possible to search nearest places in mysql by using Bookshelf.js, or Knex? And, if yes, how to do it correct?

UPD: I'm not strong with raw mysql queries, and can't find how to search with lat, lng in Bookshelf and knex.

For example, i have places table with:

  • title
  • address
  • lat
  • lng.

What query i should use to get all places in 10 miles radius from Point(lat, lng)?

thanks.

3 Answers

Answers 1

Demo

http://rextester.com/NDOR4479

SQL

SELECT *,        ACOS(SIN(RADIANS(:lat)) * SIN(RADIANS(lat)) + COS(RADIANS(:lat)) * COS(RADIANS(lat))        * COS(RADIANS(lng - :lng))) * 3959 AS distance FROM places WHERE  ACOS(SIN(RADIANS(:lat)) * SIN(RADIANS(lat)) + COS(RADIANS(:lat)) * COS(RADIANS(lat))        * COS(RADIANS(lng - :lng))) * 3959 <= 10 ORDER BY distance;

Notes

The above uses the Spherical Law of Cosines where :lat and :lng are the latitude and longitude values from Point and 3959 is the Earth radius in miles.

Answers 2

After some research i found working solution:

const {lat, lng} = req.params;  const X1 = +lat - 10,       Y1 = +lng - 10,       X2 = +lat + 10,       Y2 = +lng + 10;   const distanceInMilesSql = `( 3959 * acos( cos( radians(${+lat}) )            * cos( radians( place.lat ) )            * cos( radians( place.lng ) - radians(${+lng}) )            + sin( radians(${+lat}) )            * sin( radians( place.lat ) ) ) ) AS distance          `;  const places = await new Place().query(qb => {     qb.whereBetween('lat', [X1, X2]);     qb.whereBetween('lng', [Y1, Y2]);     qb.column(['*', Bookshelf.knex.raw(distanceInMilesSql)]);     qb.having('distance', '<', 10); }).fetchAll({withRelated: [     'place_specific_fields',     'state',     'media' ]});

Answers 3

You don't make it clear in your question, but I believe that the Bookshelf.js library works as a layer above PostgreSQL, which means that you have access to geometric data types. These can make use of these special operations. The search could be entirely SQL based, so without further context I can simply offer the following:

Combine the latitude and longitude into a single data piece of type point (using the decimal form of the latitude and longitude). Here I will refer to that as coord.

With the proper data in order, you can do a query of:

SELECT p.*, (point'(@origin_long,$origin_lat)' <-> `coord`) distance FROM `places` p WHERE circle '((@origin_long, @origin_lat),@radius)' @> `coord` ORDER BY distance;

If the data structures are set right, this should return a list, ordered by distance, of all pairs of coordinates within a given radius. You can set the coordinates at the top of this function, or maybe even wrap it in a stored procedure (if you don't mind the overhead).

For further reading, I know that PostgreSQL has an "Earth Distance" function as well, so if you are looking to include the calculation of true distance traveled between points, it might be useful!

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment