How to import latitude/longitude data correctly into MySQL?

Question

Hi,

I've got a dataset of city names with their corresponding latitudes/longitudes that I've loaded into a MySQL table, like so:

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'm only getting values with a scale of 2 to appear correctly in my table, in other words, the equivalent of DECIMAL(5,2). The data is uploaded from a text CSV that's been exported from OpenOffice Calc for UTF-8 purposes. I know OpenOffice has some problems with decimals but the full latitude/longitudes are certainly in the exported CSV. If I open the CSV with Notepad, the data is fine.

Can anyone see what I might be doing wrong?

Thanks.

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

Answer 1

I would suggest using a string data type such as varchar for storing Lat, Long. I am working on an app that makes extensive use of these coordinates and I haven't ran into any problems storing it as a string. Storing it as a number you will run into precision issues.

Just something to consider.

Answer 2

This may be overkill for your needs, but when working geographic data you may want to consider using MySQL Spatial Extensions.

I'll add that the datatype you're currently using is enough to represent the data as it is in your CSV file. There is either something in your importer that is chopping it off at import time, or whatever you're using to view the data is truncating it.

Answer 3

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