16

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

jskidd3
  • 4,609
  • 15
  • 63
  • 127
  • showing the definition of the `flags` table will be very helpful. You might also show a half-dozen sample rows of that table. Also, because UK straddles the prime meridian, the sign (+ -) of the longitude is going to matter. – O. Jones Jan 18 '14 at 19:28
  • @OllieJones Thanks Ollie, I have edit the question with the flags table structure – jskidd3 Jan 18 '14 at 19:36

2 Answers2

29

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/

Allan Veloso
  • 5,823
  • 1
  • 38
  • 36
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 1
    I cannot thank you enough. Fantastic answer, thank you! I might need your help with one other thing in a few days time since you seem to be very knowledgable on the subject of MySQL geospatials. I saw your email on your Google Plus, would you mind if I sent you an email with the link to another Stack question in a few days time? Your help was very much appreciated (in case you haven't guessed)! – jskidd3 Jan 19 '14 at 10:10
  • @OllieJones looks like MySQL uses X/Y as Long/Lat, not Lat/Long as is commonly used across SO. Point(x y) is actually Point(y x). – kouton Dec 05 '14 at 13:22
  • @kouton, perhaps you're thinking of PostgreSQL. MySQL doesn't, as far as I know, assign any geographic semantics to the the x and y in `POINT(x y)`. If it does, perhaps you could give a reference. – O. Jones Dec 05 '14 at 17:16
  • 2
    @OllieJones MySQL stores in WKT which apparently should conform to lng, lat. Even PHPMyAdmin assumes this and plots polygons / points incorrectly on it's maps. http://spatialreference.org/ref/epsg/4326/ – kouton Jun 11 '15 at 12:13
  • Thank you as well. Finding points inside a bounding box like this is surprisingly difficult. – joe Sep 01 '16 at 03:33
  • @O.Jones I think you mean this other article of yours, which is just as enjoyable: https://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/ – Pato Jan 23 '18 at 14:47
0

You can use a quadkey. It reduces the dimensions and make a spatial search easier. I have wrote a php class hilbert-curve @ phpclasses.org. You can also read about quadkey from Microsoft Bing maps tiling. Basically a quadkey helps to find the tile at x,y,z coordinate.Source:http://msdn.microsoft.com/en-us/library/bb259689.aspx.

Micromega
  • 12,486
  • 7
  • 35
  • 72