Considering that I'm going to be carrying out information on lat / lengthy pairs, what datatype is most effective to be used having a MySQL database?
Use MySQL's spatial extensions with GIS.
MySQL's Spatial Extensions are the most useful option because you will find the full listing of spatial operators and indices available. A spatial index will help you to perform distance-based information very rapidly. Please bear in mind that by 6., the Spatial Extension continues to be incomplete. I'm not putting lower MySQL Spatial, only telling you from the issues before getting too much along about this.
If you're dealing strictly with points and just the length function, this really is fine. If you want to inflict information with Polygons, Lines, or Buffered-Points, the spatial operators don't provide exact results unless of course you apply the "relate" operator. Begin to see the warning towards the top of 21.5.6. Associations for example consists of, within, or intersects are utilizing the MBR, not the precise geometry shape (i.e. an Ellipse is treated just like a Rectangle).
Also, the distances in MySQL Spatial have been in exactly the same models as the first geometry. What this means is if you are using Decimal Levels, your distance dimensions have been in Decimal Levels. This makes it tough to get exact results as you become furthur in the equator.
After I did this for any navigation database constructed from ARINC424 Used to do a reasonable quantity of testing and searching back in the code, I made use of a DECIMAL(18,12) (Really a Number(18,12) since it was firebird).
Floats and doubles aren't as precise and may lead to rounding errors which might be a really bad factor. I can not remember basically found any real data which had problems - but I am fairly sure that the lack of ability to keep precisely inside a float or perhaps a double might cause problems
The thing is that after using levels or radians we all know the plethora of the values - and also the fractional part needs probably the most numbers.
The spatial functions in PostGIS tend to be more functional (i.e. not restricted to BBOX procedures) than individuals within the MySQL spatial functions. Take a look: link text
I discovered this link very helpful: http://howto-use-mysql-spatial-ext.blogspot.com/2007/11/using-circular-area-selection.html
It might be a bit older, however it consists of an entire explanation including good examples.
Lat Lengthy information require precision, so use some form of decimal type making the truth a minimum of 2 greater than the quantity you will store to be able to perform math information. I'm not sure concerning the my sql datatypes however in SQL server people frequently use float or real rather than decimal and obtain into trouble because they are are believed amounts a fantasy ones. So just make certain the information type you utilize is really a true decimal type and never a floating decimal type and you ought to be fine.
If you havea really small error ina calculation you finish up in an exceedingly differnt location. FLoat datypes aren't exact, they'll introduce errors in information. There's an enormous differnce in location having a rounding differnce of .001 and you will easily get way in addition to that using float and a lot of math especially division.
We store latitude/longitude X 1,000,000 within our oracle database as Amounts to prevent round off errors with doubles.
Considering the fact that latitude/longitude towards the sixth decimal place was 10 centimetres precision which was all we needed. A number of other databases also store lat/lengthy towards the sixth decimal place.