Select all geospatial points inside a bounding box

Question

I have a table called 'flags' in a MySQL database with 400,000 rows. This table is made up of geospatial points that represent different positions around the UK.

The application I'm creating uses Google Maps. On the map is a button that should toggle the flags visibility on the map. It's my job now to create an API that when passed a bounding box all flags inside the bounding box are returned (so that they can then be displayed on the map).

The passed parameters are the North East latitude/longitude and the South West latitude/longitude of the viewport's current position.

I now need to perform an SQL query that will return all geospatial points that are inside this sets of coordinates (the viewport).

Ideally the solution needs to be optimized as there are many rows to search. The application does force you to zoom into a certain level before the user can reveal the flags, however.

Flags table:

• id
• coordinates
• name

Example row:

1 | [GEOMETRY - 25B] | Tenacy AB

The coordinates field can also be converted to a literal point by using AsText(coordinates). The X and Y functions do this for you however.

The coordinates column is data type: POINT

I know that to obtain the latitude/longitude of a point you can use the X and Y functions. For example, the latitude of a point can be retrieved like this: X(coordinates)

DBMS: MySQL
DBMS version: 5.6.14

Answer 1

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/