0

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?

kiler129
  • 1,063
  • 2
  • 11
  • 21
  • Are you sure that indexes would be used? – sectus Aug 11 '14 at 08:24
  • @sectus, spatial data tends to be large by definition, so spatial indexes make a huge difference. – John Powell Aug 12 '14 at 06:18
  • @sectus: unfortunately yes. Having large set of data will make table table slow for sure. – kiler129 Aug 12 '14 at 07:28
  • Perhaps you could provide some more context about your application, such as how many rows you are likely to have, what sort of queries you are likely to run, etc. I have attempted to answer your question based on long experience of the issue you are facing, but maybe need more info. – John Powell Aug 12 '14 at 16:45
  • Have you found a way round this problem that you would like to share with us? – John Powell Aug 29 '14 at 20:48

2 Answers2

0

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.

Micromega
  • 12,486
  • 7
  • 35
  • 72
  • I do not think that spatial indexes could work with this approach. – sectus Aug 12 '14 at 07:32
  • @sectus, quadkeys and geohashes are mechanisms for converting 2-dimensional coordinates into something one dimensional, such that they can be indexed using B-trees. – John Powell Aug 12 '14 at 08:29
  • @JohnBarça , the purpose is to use an index. http://dev.mysql.com/doc/refman/5.5/en/using-spatial-indexes.html – sectus Aug 12 '14 at 08:36
  • @sectus, I am well aware of this fact, but he asks "storing lat/lon as normal numbers or using POINT type without index?" because InnoDB does not support spatial indexing and MyISAM does not support foreign keys. That is the crux of the issue. – John Powell Aug 12 '14 at 08:40
  • @sectus, for what it is worth, I was one of the beta testers when MySQL first come out with proper spatial functionality. I know the code at source code level and have had MySQL table with hundreds of millions of spatial rows. I have since moved to Postgres/Postgis because you don't have to make tradeoffs like this between ACID/foreign keys and spatial indexes. But the OP's question asks about indexing lat/lon points, or having a point (geometry type) WITHOUT a spatial index. – John Powell Aug 12 '14 at 08:51
  • MongoDB uses geohashing for its spatial indexing, so the approach suggested by Phpdna definitely could work. It isn't as good an an R-tree, but it is certainly better than compound index on lat/lon or no index at all on a geometry column. – John Powell Aug 12 '14 at 08:59
  • @JohnBarça, What spatial function that uses spatial index could be rewriting to use this index? – sectus Aug 12 '14 at 09:03
  • 1
    @sectus. It is worth reading the wikipedia article on geohashing or the MongoDB spatial docs to answer that -- but contains, intersects and distance queries can all be written to work with geohashes/quadkeys (but they are not as efficient as an R-tree, which MySQL's spatial index uses). But this is not the OP's original question :-) – John Powell Aug 12 '14 at 09:07
  • @JohnBarça , i am interested in this theme. Thanks. – sectus Aug 12 '14 at 09:12
  • @sectus. Me too, I love spatial. Sorry if I sounded irritated before -- I misunderstood you. If you are really interested in serious spatial work, check out Postgis. – John Powell Aug 12 '14 at 09:14
0

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.

Community
  • 1
  • 1
John Powell
  • 12,253
  • 6
  • 59
  • 67