Abhishek Acharya Abhishek Acharya -4 years ago 83
MySQL Question

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

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..!!

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download