What is a SPATIAL INDEX and when should I use it?

56
votes

Like most of the average PHP web developers I use MySql as a RDBMS. MySql (as other RDBMS also) offers SPATIAL INDEX features, but I'm don't get it very well. I have googled for it but didn't find clear real world examples to clarify my bad knowledge about it.

Could someone explain me a little bit what is a SPATIAL INDEX and when should I use it?

4
mysqlindexingspatial-index
demian, did this answer your question? feel free to add a comment to my answer if you need more help. Or if that solved it for you, please be so kind to check my answer as "the answer". TIA, roland. – Roland Bouman
The official MySQL documentation gives explanatations and examples: Creating Spatial Indexes - Using a Spatial Index – Jocelyn

4 Answers

23
votes

You can use a spatial index for indexing geo-objects - shapes. The spatial index makes it possible to efficiently search for objects that overlap in space

7
votes

Spatial Index is like an ordinary index with this difference that Spatial objects are not 1D data points rather are in higher dimension space (e.g. 2D) and thus Ordinary indexes such as BTree are not appropriate for indexing such data. The well-known spatial Index technique is R-tree ( Google it on wikipedia )

3
votes

When we need to store some geographic data for storing locations OR we need to store shape related data then we can use it.

For Instance, Imagine that you are trying to develop an application that helps people find restaurants, pubs, bars and other hangout places near them. In nutshell, this will be a location discovery platform.

Looking from a back-end perspective, we would be needing to store geographic data of these locations like Latitude and Longitude. Then we would need to write functions that calculate the distance between the user and the location (to show how far the location is from him/her). Using the same function, we can design an algorithm that finds closest places near to the user or within a given radius from him/her.

You may find the better idea with example over here :- https://medium.com/sysf/playing-with-geometry-spatial-data-type-in-mysql-645b83880331

-6
votes

The use of spacial index is best for searching exact matching value look-up,not for range scan.It is mainly supported in MyISAM tables but from MySQL 5.7.4 LAB release,it is also supported by Innodb.

References:- http://dev.mysql.com/doc/refman/5.5/en/creating-spatial-indexes.html http://mysqlserverteam.com/innodb-spatial-indexes-in-5-7-4-lab-release/