CODEWITHSUNDEEP
mysqlgeolocationgeospatial
Kevin
Kevin

Reputation: 2688

Zipcode Radius Search with POINT Column

I have a MySQL Routine that is getting records within a 50 mile radius when passed Latitude and Longitude via utilizing the Haversin equation.

While this works great, and is pretty speedy (considering it's searching through 82k records), I am thinking that I can get better performance by creating a similar procedure utilizing a POINT column.

So, in my table I created an extra column called Location, gave it a datatype of POINT, updated my data to pass lat & lon to the Location column. Data is valid, and is fine., and added a Spatial Index

The question is, how can I convert the following query to use the Location column, instead of lat and lon columns.

SET @LAT := '37.953';
SET @LON := '-105.688';

SELECT DISTINCT
BPZ.`store_id`,         
3956 * 2 * ASIN(SQRT(POWER(SIN((@LAT - abs(Z.`lat`)) * pi()/180 / 2),2) + COS(@LAT * pi()/180 ) * COS(abs(Z.`lat`) *  pi()/180) * POWER(SIN((@LON - Z.`lon`) *  pi()/180 / 2), 2))) as distance,
c.`name`,c.`address`,c.`city`,c.`state`,c.`phone`,c.`zip`,c.`premise_type`
FROM
`zip_codes` as Z, 
`brand_product_zip` as BPZ
LEFT JOIN `customers` c ON c.`store_id` = BPZ.`store_id`
WHERE
BPZ.`zip` = Z.`zip`
AND 
3956 * 2 * ASIN(SQRT(POWER(SIN((@LAT - abs(Z.`lat`)) * pi()/180 / 2),2) + COS(@LAT * pi()/180 ) * COS(abs(Z.`lat`) *  pi()/180) * POWER(SIN((@LON - Z.`lon`) *  pi()/180 / 2), 2))) <= 50
ORDER BY
distance LIMIT 20

I understand that this has been asked before, however, everything I see points to calculations based on lat and lon and not the POINT column

Updated Code:

SET @lat = 41.92;
SET @lon = -72.65;
SET @kmRange = 80.4672; -- = 50 Miles

SELECT *, (3956 * 2 * ASIN(SQRT(POWER(SIN((@lat - abs(`lat`)) * pi()/180 / 2),2) + COS(@lat * pi()/180 ) * COS(abs(`lat`) *  pi()/180) * POWER(SIN((lon - `lon`) *  pi()/180 / 2), 2)))) as distance
FROM    `zip_codes`
WHERE   MBRContains(LineString(Point(@lat + @kmRange / 111.1, @lon + @kmRange / (111.1 / COS(RADIANS(@lat)))), Point(@lat - @kmRange / 111.1, @lon - @kmRange / (111.1 / COS(RADIANS(@lat))))), `Location`)
Order By distance
LIMIT 20

Upvotes: 0

Views: 857

Answers (2)

dgeske
dgeske

Reputation: 614

The article Nearest-location finder for MySQL explains in detail various options, and the best choice for use with the Spatial Extensions starting with MySQL 5.6.

From the article, this sample query lists zip codes within a 50 mile radius from given coordinates (42.81, -70.81):

SELECT zip, primary_city,
       latitude, longitude, distance_in_mi
  FROM (
SELECT zip, primary_city, latitude, longitude,r,
       69.0 * DEGREES(ACOS(COS(RADIANS(latpoint))
                 * COS(RADIANS(latitude))
                 * COS(RADIANS(longpoint) - RADIANS(longitude))
                 + SIN(RADIANS(latpoint))
                 * SIN(RADIANS(latitude)))) AS distance_in_mi
 FROM zip
 JOIN (
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint, 50.0 AS r
   ) AS p
 WHERE latitude
  BETWEEN latpoint  - (r / 69)
      AND latpoint  + (r / 69)
   AND longitude
  BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint))))
      AND longpoint + (r / (69 * COS(RADIANS(latpoint))))
  ) d
 WHERE distance_in_mi <= r
 ORDER BY distance_in_mi;

Upvotes: 1

Cybercartel
Cybercartel

Reputation: 12592

Have you looked into hilbert curves solutions? A spatial index doesn't deliver the exact solution? . With a mysql spatial index you can use mbrcontains:

CREATE TABLE lastcrawl (id INT NOT NULL PRIMARY KEY, pnt POINT NOT NULL) ENGINE=MyISAM;

INSERT
INTO    lastcrawl
VALUES  (1, POINT(40, -100));

SET @lat = 40;
SET @lon = -100;

SELECT  *
FROM    lastcrawl
WHERE   MBRContains
                (
                LineString
                        (
                        Point
                                 (
                                 @lat + 10 / 111.1,
                                 @lon + 10 / ( 111.1 / COS(RADIANS(@lat)))
                                 ),
                        Point    (
                                 @lat - 10 / 111.1,
                                 @lon - 10 / ( 111.1 / COS(RADIANS(@lat)))
                                 )
                        ),
                pnt
                );

Look here: MySQL - selecting near a spatial point. Here: http://www.drdobbs.com/database/space-filling-curves-in-geospatial-appli/184410998

Upvotes: 2

Related Questions