Select all geospatial points inside a bounding box
Presumably the x and y items in your POINT data in your geometry
column is in degrees of latitude and longitude.
To do this lookup efficiently in MySQL, you'll need a few things.
- A MyISAM table (or MySQL Version 5.7 and beyond and either InnoDB or MyISAM)
- A NOT NULL qualification on your geometry column
- A spatial index
ALTER TABLE flags ADD SPATIAL INDEX (coordinates)
- Code to create a textual representation of the rectangle you want to search
- Use of the GeomFromText and MBRContains / MBRWithin functions in your SELECT statement.
Suppose your lat/long box is a rectangle one degree in extent centered about Winchester Cathedral (51.0606, -1.3131). You need a bounding box around that point. This MySQL query will generate a LINESTRING (text) for a line going diagonally across that bounding box.
SELECT
CONCAT('LINESTRING(',
latitude-0.5,' ',longitude-0.5,
',',
latitude+0.5 ,' ',longitude +0.5,
')') AS box
FROM (
SELECT 51.0606 AS latitude, -1.3131 AS longitude
) AS coord
The query gets you this:
LINESTRING(50.5606 -1.8131,51.5606 -0.8131)
You can also use string processing in a host language to come up with a similar sort of text string. The format you need is this.
LINESTRING(lat1 long1, lat2 long2)
Then you can use it to search your spatial table as follows:
SELECT whatever, whatever
FROM flags
WHERE MBRContains(
GeomFromText( 'LINESTRING(50.5606 -1.8131,51.5606 -0.8131)' ),
flags.coordinates)
This will exploit the spatial index and find every row of flags
whose coordinates lie within the bounding box of that diagonal line.
Here's some documentation.
If your flags
table contains fewer than a few hundred thousand rows, you may find that an ordinary table (not a spatial table) with latitude and longitude columns (FLOAT data types, indexed) performs as well and is easier to develop and debug.
I have written a tutorial on that technique. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/