How to store geographical locations with foreign keys?

Question

Currently I'm developing location-based application. One of major application function is locating points based on search criteria and locating points in given range from one point.

Obvious choice was to store lat/lon as floats or integers but I found spatial data types in MySQL and started digging. Unfortunately if I'm correct spatial indexes are supported in MyISAM only which doesn't support foreign keys (which I need).

So, what's gonna be better (and faster) - storing lat/lon as normal numbers or using POINT type without index?

Answer 1

You can use a quadkey instead. Translate the x- and y value to a binary and then concatenate the binaries. The points get sorted along a z-curve, a.k.a monster curve. It has very good spatial properties. You can verify the upper bound with the mostsignificant bits.

Answer 2

Edit. To answer the question in your title, in short you can't, without some hackery (see below). However, I came across this today when answering another question on MySQL spatial, http://mysqlserverteam.com/innodb-spatial-indexes-in-5-7-4-lab-release/. I have no idea when it will go live, but if you can wait that long, you will finally have foreign keys and spatial indexes in the same engine in MySQL. Further digging suggests that in fact spatial indexes will be released in MySQL 5.7.5 which would appear to be imminent, although it is marked as a milestone release.

As for your other question, this will depend on your table size, but having a compound index on latitude and longitude stored as floats will vastly outperform a full table scan on a non-indexed Point beyond trivial row counts -- sadly I can't give you exact numbers, but last time I tried this on a few million rows, we were looking at orders of magnitude.

EDIT: while this answers you original question, as stated, a spatial index on Point will vastly outperform a compound index on lat/lon, as well as allow you to use MySQL's spatial functions. However, as you have pointed out, this will only work in MyISAM, so you are still stuck if you want spatial indexes, geometry types and foreign keys all at once, until InnoDB adds spatial indexing, which is in the pipeline, as stated in the above link.

There is a suggestion here: https://dba.stackexchange.com/questions/49307/spatial-index-on-an-innodb-table about keeping you spatial data in both InnoDB and MyISAM, doing the spatial search in MyISAM and then joining on a shared key and querying the attributes in InnoDB, but this seems pretty hacky and inefficient and there are questions about different lock behaviors between the two engines too and how this might perform at scale.

It will be a good day when InnoDB finally supports spatial indexing, it has been many years in the waiting.