Get all points from the database in the bounding box of the north-east and south-west coordinates in MySQL

Question

I am currently creating an application to display all the trees with geo-tags in a specific city. The main columns that I use to extract 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 158,000 rows.

I am currently 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 is it, it retrieves all records in a radius of 0.4. I use the ajax call to retrieve the data, each time the central coordinate of the map changes (in the pan or zoom panel), converts the extracted data to geojson format, and then loads it onto the map as a layer. The problem I am facing is that in places with a very high tree density, it requires the map to place all the points, and since it extracts it in radius, it loads the points that are outside the viewport.

I need a query only to load the data in coordinates inside the viewport, using the coordinates to the north-east and south-west as the borders. I searched for quite a while here, but could not find anything suitable for my requirements, Please help me. Thanks in advance..!!

Answer 1

If someone is still looking, I received a response from this message.

Get all the records from the MySQL database that are on Google Maps. GetBounds?

Thanks for the help anyway.

Answer 2

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

try it

 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 eastern, western, northern, and southern borders of your bounding box instead of your center. This is 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 draw the sides of your bounding box from its corners is trivial, as long as the coordinates of the bounding box are in degrees. If they are given in some projection units (for example, the transverse coordinate of the UTM ), then there is no answer that the answer will be placed in the message.

This query can be quickly executed using a composite index on (latitude_dl, longitude_dl, tree_geotags_id) . A latitude search will use the index range scan, and longitude and identifier can be obtained directly from the index.

What happened to the distance formula? This is Cartesian, but you need the formula of the spherical cosine law, because you are dealing with spherical coordinates.

This will not work close to the north or south pole (because cos (latitude) tends to zero there), but this is normal; you are dealing with trees, and they are not growing there yet.

Here you will find an exhaustive entry on this topic. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/