Using MySQL’s geospatial extension for a location finder

It’s possible to use the geospatial extension in MySQL for an efficient location finder.  For this to be worth the trouble, the following conditions must hold.

• You must use a MyISAM table for your geospatial data, or use version 5.7.5 or later of MySQL.
• A NOT NULL qualification on your geometry column is required
• A spatial index is needed:  ALTER TABLE flags ADD SPATIAL INDEX (coordinates)
• Use of the GeomFromText and MBRContains / MBRWithin functions in your SELECT statement.

For example, a geospatial table might have the following definition.

CREATE TABLE geozip (
  zip VARCHAR(5) CHARACTER SET utf8 NOT NULL,
  geo GEOMETRY NOT NULL,
  PRIMARY KEY zip (zip),
  SPATIAL KEY geo (geo) )
ENGINE=MYISAM

Once you have created your geozip table, you can populate it with this kind of query.

INSERT INTO geozip 
SELECT DISTINCT 
       zip, 
       GEOMFROMTEXT( CONCAT('POINT(', latitude, ' ', longitude, ')') ) AS geo 
  FROM zip

Looking up rows in this table might be done by this query.

SELECT zip, X(geo), Y(geo), distance 
  FROM ( 
         SELECT zip, geo,r, 
                units * DEGREES( ACOS( COS(RADIANS(latpoint)) * 
                                       COS(RADIANS(X(geo))) * 
                                       COS(RADIANS(longpoint) - RADIANS(Y(geo))) + 
                                       SIN(RADIANS(latpoint)) * 
                                       SIN(RADIANS(X(geo))))) AS distance 
           FROM geozip
           JOIN ( 
                  SELECT 42.81 AS latpoint, -70.81 AS longpoint, 
                         10.0 AS r, 69.0 AS units 
                ) AS p ON (1=1) 
          WHERE MbrContains(GeomFromText( 
                        CONCAT('LINESTRING(', latpoint-(r/units),' ',
                                              longpoint-(r /(units* COS(RADIANS(latpoint)))), 
                                              ',', 
                                              latpoint+(r/units) ,' ', 
                                              longpoint+(r /(units * COS(RADIANS(latpoint)))), ')')),
                        geo) 
       ) AS d 
 WHERE distance <= r 
 ORDER BY distance

The secret is using the MbrContains(box,geo) function (“Minimum bounding rectangle Contains”) for your WHERE search. That function can exploit your spatial index. The first parameter, the box parameter, specifies a bounding box, and the second specifies a point.  When you use a constant value for the first parameter, the function uses the index.

In this example we specify the first parameter with this sort of expression:

GeomFromText('LINESTRING(LatSW LonSW, LatNE LonNE)')

The LINESTRING is a diagonal line across the bounding box from its southwest to northeast corner. That LINESTRING’s value is generated based on latpoint/longpoint by the long CONCAT() expression in the query. For the example lat/long value of 42.81/-70.81, the LINESTRING looks like this:

GeomFromText('LINESTRING(42.6650 -71.0076,42.9549 -70.6124)')

Finally, if you are dealing with locations that are close together, you may find that computing your distances
using the Vincenty formula is more accurate. You would do that with this query.

SELECT zip, X(geo), Y(geo), distance FROM (
    SELECT zip, geo,r,
           units * DEGREES(
              ATAN2(
                SQRT(
                   POW(COS(RADIANS(latpoint))*SIN(RADIANS(longpoint-Y(geo))),2) +
                     POW(COS(RADIANS(X(geo)))*SIN(RADIANS(latpoint)) -
                     (SIN(RADIANS(X(geo)))*COS(RADIANS(latpoint)) *
                      COS(RADIANS(longpoint-Y(geo)))) ,2)),
                   SIN(RADIANS(X(geo)))*SIN(RADIANS(latpoint)) +
                     COS(RADIANS(X(geo)))*
                     COS(RADIANS(latpoint))*
                     COS(RADIANS(longpoint-Y(geo)
                   )
                  )) AS distance
      FROM geozip
      JOIN (
             SELECT 42.81  AS latpoint,  -70.81 AS longpoint,
             50.0 AS r, 69.0 AS units
           ) AS p ON (1=1)
     WHERE MBRCONTAINS(GEOMFROMTEXT(
             CONCAT('LINESTRING(',
                       latpoint-(r/units),' ',
                       longpoint-(r /(units* COS(RADIANS(latpoint)))),
                       ',',
                       latpoint+(r/units) ,' ',
                       longpoint+(r /(units * COS(RADIANS(latpoint)))),
                    ')')),  geo)
       ) AS d
 WHERE distance <= r
 ORDER BY distance

Percona’s Alex Rubin has prepared some helpful details on the geo extension.