Fetch all points from database inside a bounding box of north east and south west coordinates in MySQL

Question

I am currently building an application to show all geo-tagged trees in a particular city. The main columns I am using to fetch data from the table are as follows,

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| tree_geotags_id | int(11)     | NO   | PRI | None    |       |
| lattitude_dl    | double(9,7) | YES  |     | NULL    |       |
| longitude_dl    | double(9,7) | YES  |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

The table has over 158000 rows.

Currently I am using the following query to get my output,

SELECT gt.tree_geotags_id, gt.latitude_dl, gt.longitude_dl,
    SQRT(
        POW( 69.1 * ( gt.latitude_dl - [ center_lat ] ), 2) +
        POW( 69.1 * ( [ center_lon ] - gt.longitude_dl ) * COS( gt.latitude_dl / 57.3 ), 2 )
    ) AS distance
    FROM tree_geotags_t gt
    HAVING distance < 0.4 ORDER BY distance

What this does is, it fetches all records at a radius of 0.4. I use an ajax call to fetch the data, every time the center coordinate of the map changes( on map pans or zooms ), convert the fetched data into geojson format and then load it on the map as a layer. The issue I am having with this is, in locations where there is a very high density of trees, it takes a long time for the map to place all the points and since it fetches it on a radius it loads points that are even outside the viewport.

I need a query to only load data in coordinates inside the viewport, using the northeast and southwest coordinates as boundaries. I searched for quite a while here but couldn't find anything suited to my requirements. Please help me out. Thanks in advance..!!

Add a WHERE clause that excludes entries with lat/long lower/greater than your target values …? — CBroe, Feb 24 '17 at 12:02

score 4 · Accepted Answer · edited May 23 '17 at 11:53

4

If anyone's still looking, I got my answer from this post.

Get all records from MySQL database that are within Google Maps .getBounds?

Thanks for the help anyway.

edited May 23 '17 at 11:53

Community

1
1

answered Mar 01 '17 at 08:34

Abhishek Acharya

339
4
12

O. Jones · Answer 2 · 2017-02-24T12:52:48.060

You are very close. Your (otherwise grossly incorrect) distance formula contains the seed of your bounding box check.

Try this

SET @distance_unit := 69.0;  /* for miles, use 111.045 for km. */
SET @radius := 1.0;          /* for radius */
SET @center_lat := target_latitude_in_degrees;
SET @center_lon := target_longitude_in_degrees;

SELECT gt.tree_geotags_id, gt.latitude_dl, gt.longitude_dl
  FROM tree_geotags_t gt
 WHERE gt.latitude_dl
   BETWEEN @center_lat  - (@radius/@distance_unit) /*east boundary*/
       AND @center_lat  + (@radius/@distance_unit) /*west*/
   AND gt.longitude_dl
   BETWEEN @center_lon - (@radius / (@distance_unit * COS(RADIANS(@center_lat)))) /*south*/
       AND @center_lon + (@radius / (@distance_unit * COS(RADIANS(@center_lat)))) /*north*/

Suppose you know the east, west, north, and south boundaries of your bounding box instead of its center. That's an easy adaptation of the above code.

SELECT gt.tree_geotags_id, gt.latitude_dl, gt.longitude_dl
  FROM tree_geotags_t gt
 WHERE gt.latitude_dl  BETWEEN @east   AND @west
   AND gt.longitude_dl BETWEEN @south  AND @north

The question of how to derive the sides of your bounding box from its corners is trivial as long as the bounding box coordinates are in degrees. If they're given in some projection units (like transverse UTM coordinates) there's no way the answer will fit in a Stack Overflow post.

This query can be made fast by a compound index on (latitude_dl, longitude_dl, tree_geotags_id) The latitude search will use an index range scan, and the longitude and id can then be retrieved directly from the index.

What's wrong with your distance formula? It's cartesian, but you need the spherical cosine law formula because you're dealing with spherical coordinates.

This will not work close to either the north or south pole (because cos(latitude) tends towards zero there) but that's OK; you're dealing with trees, and they don't grow there, yet.

Here's a comprehensive writeup on the topic. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Thanks for the corrections, but I am actually looking for a query that will help me get all points inside the viewport. I am getting the coordinates for all 4 corners, northeast, northwest, southeast and southwest. However, I'm not being able to build a query using these values, to get all trees in between these coordinates. — Abhishek Acharya, Feb 24 '17 at 12:34
This is because, when I zoom in, it still loads points outside the viewport. If these can be avoided, the performance may become much faster. — Abhishek Acharya, Feb 24 '17 at 12:39
I have a particular section in the city, which is basically a jungle. There are around 87000 trees in that particular area. Zooming in would reduce the amount of points being plotted.. — Abhishek Acharya, Feb 24 '17 at 13:11
Well, yes. That's why you need to give the query the east / west / north / south boundaries of the area you want. See my edit. — O. Jones, Feb 24 '17 at 13:28
I tried the east, west query you provided. Didn't work out. I am however marking your answer as correct, since m using the radius query anyway.. Thanks for the help. — Abhishek Acharya, Feb 27 '17 at 05:16

Fetch all points from database inside a bounding box of north east and south west coordinates in MySQL

2 Answers2