Geo (proximity) Search with MySQL

Embed or link this publication

Description

Geo/Spatial Search with MySQL, Alexander Rubin, Senior Consultant, MySQL AB

Popular Pages


p. 1

geo proximity search with mysql alexander rubin senior consultant mysql ab copyright 2006 mysql ab the world s most popular open source database 1

[close]

p. 2

why geo search · stores find locations new you · social networks find friends close to you · online maps find points of interest near your position · online newspapers/yellow pages find show times next to you home copyright 2006 mysql ab the world s most popular open source database 2

[close]

p. 3

poi search example copyright 2006 mysql ab the world s most popular open source database 3

[close]

p. 4

common task · task find 10 nearby hotels and sort by distance · what do we have ­ given point on earth latitude longitude ­ hotels table hotel name latitude longitude · question how to calculate distance between us and hotel copyright 2006 mysql ab the world s most popular open source database 4

[close]

p. 5

latitudes and longitudes copyright 2006 mysql ab the world s most popular open source database 5

[close]

p. 6

distance between 2 points the haversine formula for two points on a sphere of radius r with latitudes 1 and 2 latitude separation 1 2 and longitude separation the distance d between the two points copyright 2006 mysql ab the world s most popular open source database 6

[close]

p. 7

the haversine formula in mysql r earth s radius lat lat2 lat1 long long2 long1 a sin²lat/2 coslat1 coslat2 sin²long/2 c 2atan2a 1-a d rc angles need to be in radians 3956 2 asin sqrt powersin orig.lat dest.lat pi 180 2 2 cosorig.lat pi 180 cosdest.lat pi 180 powersin orig.lon dest.lon pi 180 2 2 as distance copyright 2006 mysql ab the world s most popular open source database 7

[close]

p. 8

mysql query find nearby hotels set @orig_lat=121.9763 set @orig_lon=37.40445 set @dist=10 lat can be negative select 3956 2 asinsqrt powersin orig_lat absdest.lat pi 180 2 2 cos orig_lat pi 180 cosabsdest.lat pi 180 powersin orig_lon dest.lon pi 180 2 2 as distance from hotels dest having distance @dist order by distance limit 10 g copyright 2006 mysql ab the world s most popular open source database 8

[close]

p. 9

find nearby hotels results hotel_name lat lon dist hotel astori 122.41 37.79 0.0054 juliana hote 122.41 37.79 0.0069 orchard gard 122.41 37.79 0.0345 orchard gard 122.41 37.79 0.0345 10 rows in set 4.10 sec · 4 seconds very slow for web query copyright 2006 mysql ab the world s most popular open source database 9

[close]

p. 10

mysql explain query mysql explain select_type simple table dest type all possible_keys null key null key_len null ref null rows 1787219 extra using filesort 1 row in set 0.00 sec copyright 2006 mysql ab the world s most popular open source database 10

[close]

p. 11

how to speed up the query · we only need hotels in 10 miles radius ­ no need to scan the whole table 10 miles copyright 2006 mysql ab the world s most popular open source database 11

[close]

p. 12

how to calculate needed coordinates · 1° of latitude 69 miles · 1° of longitude coslatitude 69 · to calculate lon and lat for the rectangle set lon1 mylondist/abscosradiansmylat 69 set lon2 mylon +dist/abscosradiansmylat 69 set lat1 mylat dist/69 set lat2 mylat dist/69 copyright 2006 mysql ab the world s most popular open source database 12

[close]

p. 13

modify the query select destination 3956 2 asinsqrt powersin orig.lat dest.lat pi 180 2 2 cosorig.lat pi 180 cosdest.lat pi 180 powersin orig.lon -dest.lon pi 180 2 2 as distance from users destination users origin where origin.id=userid and destination.longitude between lon1 and lon2 and destination.latitude between lat1 and lat2 copyright 2006 mysql ab the world s most popular open source database 13

[close]

p. 14

speed comparison · test data users and coordinates ­ id username lat lon · original query full table scan ­ 8 seconds · optimized query stored procedure ­ 0.06 to 1.2 seconds depending upon the number of records in the given radius copyright 2006 mysql ab the world s most popular open source database 14

[close]

p. 15

stored procedure create procedure geodist in userid int in dist int begin declare mylon double declare mylat double declare lon1 float declare lon2 float declare lat1 float declare lat2 float get the original lon and lat for the userid select longitude latitude into mylon mylat from users where id=userid limit 1 calculate lon and lat for the rectangle set lon1 mylon-dist/abscosradiansmylat 69 set lon2 mylon+dist/abscosradiansmylat 69 set lat1 mylat dist/69 set lat2 mylat dist/69 copyright 2006 mysql ab the world s most popular open source database 15

[close]

Comments

no comments yet