Bounding Box Search in MySQL

Question

I am trying to figure out how retrieve the information from the MySQL table below using the "top-left latitude and longitude" and "bottom-right latitude and longitude" for a geographical bounding box.

CREATE TABLE `markers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
  `name` VARCHAR( 60 ) NOT NULL ,
  `address` VARCHAR( 80 ) NOT NULL ,
  `lat` decimal(10,7) NOT NULL ,
  `long` decimal(10,7) NOT NULL,
  `geopoint` point NOT NULL,
  PRIMARY KEY  (id),
  SPATIAL geopoint (geopoint)
) ENGINE = MYISAM ;

So far I have the SQL query below and I am not sure if it's the most effective and efficient way of retrieving the information?

$sql = "SELECT * FROM `markers` WHERE (long BETWEEN '$west_long' AND '$east_long') AND (lat BETWEEN '$north_lat' AND '$south_lat')";

What's different from retrieving information from any other table? — Royal Bg, Apr 18 '13 at 17:03
I need to retrieve the information based on the bounding box of a map as described above! — Chris, Apr 18 '13 at 17:21
The question stays the same. But you can also give more info about your bounding box, such as... code? — Royal Bg, Apr 18 '13 at 17:49

score 0 · Answer 1 · answered Apr 18 '13 at 19:17

There are quite impressive formulas to convert two latitudes into a distance (meters, miles), the same for longitudes.

As long as your requests are in a certain range of Earth, you should assist MySQL with a second much simplified widened where-condition to reduce to the right rectangle.

It's your choice if you take those formulas in php or in sql - the box in meters can be converted into a box in lat/long.

A simple rectangle in lat-long-form would never be a real rectangle, like you can see at every orange.

Marco

Bounding Box Search in MySQL

1 Answers1