How to correctly import latitude / longitude data into MySQL?

Question

I have a dataset of city names with corresponding latitudes / longitudes that I loaded into a MySQL table, for example:

city_id | city_name | Latitude DECIMAL (9.6) | Longitude DECIMAL (9.6)

Typical latitude / longitude coordinates might look like this: 54.284758 / 32.484736.

However, I get values ​​with a scale of 2 to display correctly in my table, in other words, the equivalent of DECIMAL (5,2). Data is downloaded from a text CSV that has been exported from OpenOffice Calc for UTF-8 purposes. I know that OpenOffice has some problems with decimals, but the full latitude / longitude is definitely in the exported CSV. If I open CSV using Notepad, the data will be accurate.

Can anyone see what I can do wrong?

Thanks.

UPDATE: Got it working, thanks for all the input. I recreated everything from scratch, a new schema file (I use ORM), a new CSV export, a new table, a new LOAD DATA INFILE and it works with the correct decimal output. It hits me.

Answer 1

This may be redundant for your needs, but when working with geographic data, you might want to use MySQL Spatial Extensions .

I will add that the data type that you are currently using is sufficient to represent the data, as in your CSV file. Your importer has something that interrupts it during import, or anything you use to view the data trims it.

Answer 2

I would suggest using a string data type like varchar to store Lat, Long. I am working on an application that makes extensive use of these coordinates, and I have not encountered any problems saving it as a string. By saving it as a number, you will run into accuracy problems.

Just think it over.

Answer 3

Why don't you just declare fields as having type DOUBLE in your MySQL table? Qualifiers for the number of digits and digits after the decimal point are optional.