How do I query a table with a geospatial "point" field in MySQL

Question

I have a "places" table with fields for lat,lng, and point. I've been using haversine formula to calculate the distance in terms of lat,lng but I read that using a point field is more efficient? I just can't find any docs on how to do this. This is my query as it stands now to query all places and order them by distance

 SELECT `places`.*, `places`.`id` AS `pid`,   
        ((6371*acos(cos(radians(37.776708))*cos(radians(lat))*
          cos(radians(lng)-radians(-122.398050))+sin(radians(37.776708))*
          sin(radians(lat))))*1000) AS `distance` 
   FROM `places` 
  WHERE `page_id` IS NOT null 
  ORDER BY `distance` ASC 
  LIMIT 40 OFFSET 0

How can I change this to use the "point" field in the table instead of lat/lng

Answer 1

There are a few things you need to do to use the MySQL geospatial extension for this kind of thing.

1. You must use a MyISAM table for your geospatial data.
2. You need to have a GEOMETRY column in your table, and it must be NOT NULL.
3. You can't use the spherical-cosine-law distance calculation mentioned in your question as your primary search criterion. You need to use a bounding box instead (that is, you need to search for points within a particular distance of your search point.

Your current query simply finds the closest points to (37.77671, -122.3980) by examining all the points in your places table and sorting them in proximity order. When you use geospatial, you're doing something a bit smarter than that. You need to establish a proximity limit. Let's call it a radius of 10.0 statute miles. Notice that there are 69.0 statute miles per degree of latitude.

So how do you do all this?

First make yourself a table with a geometry column, perhaps like this:

CREATE TABLE geoplace (
  id INT NOT NULL,
  geo GEOMETRY NOT NULL,
  PRIMARY KEY id (id),
  SPATIAL KEY geo (geo)
) ENGINE=MYISAM

Then, populate it something like this, putting a geometric Point item into each geo value.

INSERT INTO geoplace
SELECT DISTINCT id,
                GEOMFROMTEXT(
                  CONCAT('POINT(',
                     lat,
                     ' ',
                     lng,
                ')')  ) AS geo
 FROM places

Obviously, you can put the GEOMETRY column into any table you want. But you can't create a functioning spatial index in an InnoDB table; that access method doesn't support spatial indexes.

Then, you need a query like this to retrieve your information.

SELECT id, X(geo), Y(geo), distance FROM (
SELECT id, 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 geoplace
 JOIN (
        SELECT 37.776708  AS latpoint,  -122.398050 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

Notice that this great hairball of a query uses the parameters you provide in this clause:

        SELECT 37.776708  AS latpoint,  -122.398050 AS longpoint,
               10.0 AS r, 69.0 AS units

It will do a spatial rectangle search using MbrContains, then compute the distances of the points it finds using the spherical cosine law formula, then order them correctly. This thing runs quite fast compared to the query you started with, because it exploits the spatial index.

This is written up in more detail here.

http://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/

with background here.

http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/