I understand postgres includes a datatype for storing physical coordinates. But I am searching for a RDBMS agnostic solution. Presently I am using Decimal(25,20) in MySQL. I might be by using this data to research these locations with different given distance from the given location later. Which will be the ultimate way to keep this type of data?
One other good way is to multiply the values with a constant and store them as integer values. Using integers only will also help accelerate information.
Unless of course you're in serious necessity of precision you need to really only have to store 5+ values following the decimal point.
This Latitude Longitude Data Storage Specification provides a chart that shows precision versus decimal places.
# decmal places, example, precision 5 51.22135 ± 0.8 m 6 50.895132 ± 0.08 m
7 works to be 8mm, or about .314 inches.
The conventional is here. Although it's an excessive amount of for any simple use situation for example yours, it could give you ideas about why it might really be better to go and employ some OGC compliant packages many databases have nowadays, even MySQL.
Failing that, and presuming you'll implement the calculations for distance information, any floating point number which has the truth you'll need works.
AFAIK, MS SQL Server 2008 has support for GeoLocations like a DataType. I understand you're using MySQL, but simply thought I'd bring it up about this question.
vfilby's answer is most likely the very best one, however many RDBMS have better support for indexing character fields than indexing dense integer (or floating point) fields.
Because of this alone, I would recommend changing the information first: If you wish to find values "near" another value, you'll furthermore desire a function that preserves this- possibly by transforming to base36 and
_-padding towards the decimal point, but when you simply need a precise match, nearly any fast hashing function is going to do.
Again: Without having lots of data, or you are not utilizing an RDBMS such as this, do what vfilby suggested.
Following @vfilby's answer, why don't you keep two halves from the number as two separate int types?
I have always used just 4-byte floating point posts in order to save latitude and longitude, since the error in precision is a smaller amount compared to precision from the products we use. This might not be together with your application, but when you can't get a lot more RDBMS-agnostic than the usual float.