1

I'm working on a little app that stores Placemarks specified by iOS devices, into a MySQL backend. When a device saves a new Placemark, it is INSERTed into the DB and the newly generated ID is sent back to the client. If another device attempts to save the same Placemark, the server needs to figure out that it already exists in the DB, and instead of INSERTing and generating a new ID, it needs to send back to the client the existing ID for that Placemark.

2 Placemarks are the same if they have identical:

  • name
  • latitude
  • longitude

Therefore the server attempts to identify if the submitted Placemark already exists by issuing

SELECT id FROM Placemark WHERE name=x, latitude=y, longitude=x

  • In iOS, latitude and longitude are in degrees and of type CLLocationDegrees as reported by <CoreLocation>
  • In MySQL, latitude and longitude are defined as DOUBLE


The problem:
Precision is lost when storing coordinates, for instance:
latidude of 50.09529561485427 is stored as 50.0952956148543
longitude of -122.9893130423316 is stored as -122.989313042332

The question:
Can I use a different data type in MySQL so as to store each coordinate intact?

Notes:

  • I can't store coordinates as TEXT or VARCHAR because I need to be able to say SELECT placemarks coordinates BETWEEN x AND y, or perform other arithmetic SELECTs
  • I can't use MySQL's spatial extensions because portability is an issue, and also because to my understanding, spatial data types are very slow when used as keys (and my use case requires SELECT by coordinates)

Any help appreciated

DTs
  • 1,196
  • 1
  • 11
  • 28
  • 1
    Aside from your question, entering both coordinates into Google Maps gives me an almost identical location (it's off by a couple of feet). Do you really need that much accuracy? – Scott Berrevoets Feb 19 '13 at 04:07
  • 1
    Hi DTs. I recommend closing this question as a duplicate of http://stackoverflow.com/questions/12504208/what-mysql-data-type-should-be-used-for-latitude-longitude-with-8-decimal-places/12504340#12504340 -- you can adapt that answer to your problem with CLLocationDegrees pretty easily. Regards. – Yawar Feb 19 '13 at 04:08
  • @Scott Remember, I use those coordinates in my WHERE clauses. They need to remain intact not because I need to identify a Placemark super accurately, but because they are used as keys in subsequent searches. – DTs Feb 19 '13 at 05:08
  • @DTs floating point data is rarely good choice for any type of key value - it is even almost impossible to precisely compare if two floats are the same. You have to compare with epsilon. I would rethink your program architecture if it is dependent of the float staying the exact value. - If it is not used as key and simply a value in a range then scotts recommendation is a valid one. – Justin Meiners Feb 19 '13 at 05:15

2 Answers2

3

Actually further testing revealed the following curious outcome: the precision is lost when the DB driver binds the data to the query statement, and before the data is actually inserted into the DB.

This has the following interesting consequence: The precision seems to be lost in an identical manner even when I bind keys to my subsequent search queries, and therefore almost "accidentally" the correct result is returned. That is, the key (coordinates) is stored incorrectly, and then subsequent selects also convert query keys incorrectly, but in a matching manner, and therefore the record is found. Given that the conversions due to precision loss are always performed on the same platform (the server), it's probably safe to leave as is?? Or maybe not, perhaps a future migration exercise (i.e., due to scaling) will make the rounding errors apparent.

Yawar's pointer led me to MySQL's manual which explains that floating point numbers can be stored without loss of precision as DECIMAL:

"Exact-value numeric literals have an integer part or fractional part, or both. They may be signed" http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

Given that the existing solution seems to work, the question then becomes, is it better to keep the DB data type DOUBLE, or switch to DECIMAL?

Storage:
Latitude has range from -90 to +90, and iOS seems to use precision of 14 decimals
Longitude has range from -180 to +180, which leaves precision of 13 decimals on iOS

That means we need:
Latitude : DECIMAL(16,14), which takes less the 8 bytes of storage (4 bytes for each pack of 9d)
Longitude: DECIMAL(16,13), also requiring less than 8 bytes of storage

In contrast, a column of type DOUBLE requires 8 bytes of storage.

Speed (arithmetic):
Floating point arithmetic will almost always be materially faster because it is supported natively by the hardware

Speed (searches):
I believe index of DECIMAL vs index of DOUBLE have a very comparable runtime.

Conclusion:
Switching to DECIMAL eliminates the precision errors and also reduces storage requirements, at an arithmetic performance penalty which is negligible in this use case.

DTs
  • 1,196
  • 1
  • 11
  • 28
0

Instead of storing latitude and longitude as double, multiply by 1000000, convert to integer, and store that. Of course, divide by 1000000.0 if you need to read them back in your app.

This will give you plenty of accuracy (0.000001º) for placemarks and will eliminate floating point conversion and precision problems that you are having and take up slightly less space. I use this in my own data (it's binary, not SQL) and call them MicroDegrees.

progrmr
  • 75,956
  • 16
  • 112
  • 147