Bearing in mind that I'll be performing calculations on lat / long pairs, what datatype is best suited for use with a MySQL database?
views:
9575answers:
10Do you have any other links to examples or any other info as to how best get started with them?
Mr. Matt
2008-10-01 19:40:31
I cheated by Googling for "mysql spatial example" and got http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html . It has real-world examples for selecting shortest distances, etc.
Just Some Guy
2008-10-01 19:49:21
Awesome, thanks!
Mr. Matt
2008-10-01 19:51:54
MYSQL Spatial is a good option, but still has significant limits and caveats (as of 6). Please see my answer below...
James Schek
2008-10-02 15:43:45
A:
A FLOAT should give you all of the precision you need, and be better for comparison functions than storing each co-ordinate as a string or the like.
If your MySQL version is earlier than 5.0.3, you may need to take heed of certain floating point comparison errors however.
Prior to MySQL 5.0.3, DECIMAL columns store values with exact precision because they are represented as strings, but calculations on DECIMAL values are done using floating-point operations. As of 5.0.3, MySQL performs DECIMAL operations with a precision of 64 decimal digits, which should solve most common inaccuracy problems when it comes to DECIMAL columns
ConroyP
2008-10-01 19:21:22
You need a real latitude/longitude coordinate datatype for easy math. Imagine the convenience of something like the equivalent of "select * from stores where distance(stores.location, mylocation) < 5 miles"
Just Some Guy
2008-10-01 19:23:49
Hadn't heard of the spatial extensions before, that does sound very convenient alright, having previously worked on an inherited app that does quite a bit of geo-related calculations, must check it out.
ConroyP
2008-10-01 19:33:47
A:
Lat Long calculations require precision, so use some type of decimal type and make the precision at least 2 higher than the number you will store in order to perform math calculations. I don't know about the my sql datatypes but in SQL server people often use float or real instead of decimal and get into trouble because these are are estimated numbers not real ones. So just make sure the data type you use is a true decimal type and not a floating decimal type and you should be fine.
HLGEM
2008-10-01 19:23:07
both float and decimal types have their place. as a rule of thumb, floats mean physical variables, and decimals are for countable entities (mostly money). i don't see why you'd prefer decimal for lat/long
Javier
2008-10-01 19:26:04
I also think float is fine for lat/long. At least on SQL Server (4bytes, 7 digits).
Dragoljub
2009-05-21 16:46:24
Float is not exact it is estimated, lake of exactness in a lat long is fatal! It could point you to a completely differnt spot on the globe.
HLGEM
2009-05-21 17:26:09
+6
A:
When I did this for a navigation database built from ARINC424 I did a fair amount of testing and looking back at the code, I used a DECIMAL(18,12) (Actually a NUMERIC(18,12) because it was firebird).
Floats and doubles aren't as precise and may result in rounding errors which may be a very bad thing. I can't remember if I found any real data that had problems - but I'm fairly certain that the inability to store accurately in a float or a double could cause problems
The point is that when using degrees or radians we know the range of the values - and the fractional part needs the most digits.
Richard Harrison
2008-10-01 19:37:02
A:
Because if you havea very small error ina calculation you end up in a very differnt location. FLoat datypes are not exact, they will introduce errors in calculations. There is a huge differnce in location with a rounding differnce of .001 and you can easily get way more than that using float and lots of math especially division.
HLGEM
2008-10-01 20:49:01
+8
A:
MySQL's Spatial Extensions are the best option because you have the full list of spatial operators and indices at your disposal. A spatial index will allow you to perform distance-based calculations very quickly. Please keep in mind that as of 6.0, the Spatial Extension is still incomplete. I am not putting down MySQL Spatial, only letting you know of the pitfalls before you get too far along on this.
If you are dealing strictly with points and only the DISTANCE function, this is fine. If you need to do any calculations with Polygons, Lines, or Buffered-Points, the spatial operators do not provide exact results unless you use the "relate" operator. See the warning at the top of 21.5.6. Relationships such as contains, within, or intersects are using the MBR, not the exact geometry shape (i.e. an Ellipse is treated like a Rectangle).
Also, the distances in MySQL Spatial are in the same units as your first geometry. This means if you're using Decimal Degrees, then your distance measurements are in Decimal Degrees. This will make it very difficult to get exact results as you get furthur from the equator.
James Schek
2008-10-02 15:41:53
A:
We store latitude/longitude X 1,000,000 in our oracle database as NUMBERS to avoid round off errors with doubles.
Given that latitude/longitude to the 6th decimal place was 10 cm accuracy that was all we needed. Many other databases also store lat/long to the 6th decimal place.
tdyen
2008-10-19 01:45:25
A:
I can't get the GIS spatial extensions to work in mySQL 5.1.x on either OS X or Linux. I have tried my own tests, as well as followed this example http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html. I get nothing but NULL values returned.
Anyone having similar issues?
Dex
2010-02-06 12:23:55
A:
depending on you application, i suggest using FLOAT(9,6)
spatial keys will give you more features, but in by production benchmarks the floats are much faster than the spatial keys. (0,01 VS 0,001 in AVG)
d0nut
2010-07-23 12:37:42