Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Database: Best performance way to query geo location data?

I have a MySQL database. I store homes in the database and perform literally just 1 query against the database, but I need this query to be performed super fast, and that's to return all homes within a square box geo latitude & longitude.

SELECT * FROM homes  WHERE geolat BETWEEN ??? AND ??? AND geolng BETWEEN ??? AND ??? 

How is the best way for me to store my geo data so that I can perform this query of displaying all home within the geolocation box the quickest?

Basically:

  • Am I using the best SQL statement to perform this query the quickest?
  • Does any other method exist, maybe not even using a database, for me to query the fastest way a result of homes within a boxed geolocation bounds?

In case it helps, I've include my database table schema below:

CREATE TABLE IF NOT EXISTS `homes` (   `home_id` int(10) unsigned NOT NULL auto_increment,   `address` varchar(128) collate utf8_unicode_ci NOT NULL,   `city` varchar(64) collate utf8_unicode_ci NOT NULL,   `state` varchar(2) collate utf8_unicode_ci NOT NULL,   `zip` mediumint(8) unsigned NOT NULL,   `price` mediumint(8) unsigned NOT NULL,   `sqft` smallint(5) unsigned NOT NULL,   `year_built` smallint(5) unsigned NOT NULL,   `geolat` decimal(10,6) default NULL,   `geolng` decimal(10,6) default NULL,   PRIMARY KEY  (`home_id`),   KEY `geolat` (`geolat`),   KEY `geolng` (`geolng`), ) ENGINE=InnoDB  ; 

UPDATE

I understand spatial will factor in the curvature of the earth but I'm most interested in returning geo data the FASTEST. Unless these spatial database packages somehow return data faster, please don't recommend spatial extensions. Thanks

UPDATE 2

Please note, no one below has truly answered the question. I'm really looking forward to any assistance I might receive. Thanks in advance.

like image 287
HankW Avatar asked Nov 28 '09 19:11

HankW


People also ask

How can I make my database search faster?

Try these five tips to boost the speed of your database: Make sure all of your tables have primary keysRunning a table without a primary key is like running a four-cylinder engine with only two active pistons. Open every table in Design view and make sure each has a primary key.

What is geolocation database?

A TVWS geolocation database is an Internet service, which responds to TVWS device requests, containing the location of the device, whose frequencies are available in the device location. From: TV White Space Communications and Networks, 2018.

What type of data is geolocation?

Geolocation data is information that can be used to identify an electronic device's physical location.


1 Answers

There is a good paper on MySQL geolocation performance here.

EDIT Pretty sure this is using fixed radius. Also I am not 100% certain the algorithm for calculating distance is the most advanced (i.e. it'll "drill" through Earth).

What's significant is that the algorithm is cheap to give you a ball park limit on the number of rows to do proper distance search.


The algorithm pre-filters by taking candidates in a square around the source point, then calculating the distance in miles.

Pre-calculate this, or use a stored procedure as the source suggests:

# Pseudo code # user_lon and user_lat are the source longitude and latitude # radius is the radius where you want to search lon_distance = radius / abs(cos(radians(user_lat))*69); min_lon = user_lon - lon_distance; max_lon = user_lon + lon_distance; min_lat = user_lat - (radius / 69); max_lat = user_lat + (radius / 69); 
SELECT dest.*,   3956 * 2 * ASIN(     SQRT(       POWER(         SIN(           (user_lat - dest.lat) * pi() / 180 / 2         ), 2       ) + COS(         user_lat * pi() / 180       ) * COS(         dest.lat * pi() / 180       ) * POWER(         SIN(           (user_lon - dest.lon) * pi() / 180 / 2         ), 2       )     )   ) as distance FROM dest WHERE    dest.lon between min_lon and max_lon AND   dest.lat between min_lat and max_lat HAVING distance < radius ORDER BY distance LIMIT 10 
like image 117
Igor Zevaka Avatar answered Sep 19 '22 12:09

Igor Zevaka