1

I'm using MySQL version: 5.7.22

I've got two columns Latitude and Longitude both in Degrees/Minutes/Seconds format that I want to convert to Decimal format ex: 48° 52.250' N to 48.93611111

I have the following script but I'm stuck at how to split the degrees minutes and seconds. I cannot hard-code the values as I've done here left(Latitude,2) since the degrees might have 3 decimals as well

SELECT Latitude,
       left(Latitude, 2) +
       (TRUNCATE((Latitude - TRUNCATE(Latitude)) * 100) / 60) +
       (((Latitude * 100) - TRUNCATE(Latitude * 100)) * 100) / (60 * 60) AS DECIMAL_DEGREES
  FROM small_ocean_data

The formula for the conversion is this: D + M/60 + S/3600 * -1 if direction in ['W', 'S'] else 1

Any help would be grateful!

Akash
  • 395
  • 5
  • 16
  • I would rethink the design of the table, storing in 3 fields Degrees, Minutes and Seconds. That would make everything easier – Cid Nov 20 '19 at 08:27
  • Does this answer your question? [Split value from one field to two](https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two) and [How to split the name string in mysql?](https://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql) – Cid Nov 20 '19 at 08:28

1 Answers1

1

Well if I use this formula of yours: D + M/60 + S/3600 Where I believe D is Degrees and M are Minutes and S are Seconds. With this select:

select SUBSTRING_INDEX('48° 52.250', '°',1) + 
       (SUBSTRING_INDEX('48° 52.250',' ',1)/60) +  
       (SUBSTRING_INDEX('48° 52.250','.',1)/3600);

Or for your database:

select SUBSTRING_INDEX(Latitude, '°', 1) + 
       (SUBSTRING_INDEX(Latitude ,' ',1)/60) +  
       (SUBSTRING_INDEX(Latitude ,'.',1)/3600) "DECIMAL_DEGREES"
FROM small_ocean_data;

I get 48.81333333333333

48 + 0.8 + 0.013333333333333334

Here is the DEMO

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • This is what I was looking for @VBoka Thanks a ton! I've suggested one small edit, please have a look and update accordingly – Akash Nov 20 '19 at 08:47
  • You are welcome @Akash, I have updated as suggested with little change: it is `, 2` in first SUBSTRING_INDEX and not `, 1`. I have also updated the DEMO. – VBoka Nov 20 '19 at 08:49
  • There's one more catch about the direction, `-1 if direction in ['W', 'S'] else 1`. How do you think we can include this in the query. – Akash Nov 20 '19 at 08:55
  • You add 1 to the final result if it is not direction in ['W', 'S'] and subsract 1 if it is ? – VBoka Nov 20 '19 at 08:58
  • Multiply by -1 if ['W', 'S'] – Akash Nov 20 '19 at 09:01
  • Also now that you are using SUBSTRING_INDEX to find the degree, it should be `SUBSTRING_INDEX(Latitude, '°', 1)` instead of 2 – Akash Nov 20 '19 at 09:03
  • check this demo for direction https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=17bd8b412574037a3694a11603251cef – VBoka Nov 20 '19 at 09:06
  • 1
    Yes, in every place it should be 1. I have corrected it. – VBoka Nov 20 '19 at 09:08
  • 1
    ```select (SUBSTRING_INDEX("48° 52.250' N",'°', 1) + (SUBSTRING_INDEX("48° 52.250' N",' ',2)/60) + (SUBSTRING_INDEX("48° 52.250' N",'.',3)/3600)) * CASE WHEN SUBSTRING_INDEX("48° 52.250' W", "' ", -1) in ('W', 'S') THEN (-1) ELSE 1 END "DECIMAL_DEGREES"; ``` This works for me! – Akash Nov 20 '19 at 09:33
  • I found out that the entire number with `'` is actually minutes. Seconds is denoted by `''`. This is my final query: ```ALTER TABLE small_ocean_data ADD Lat_in_degree TEXT; Long_in_degree TEXT; SET small_ocean_data.Lat_in_degree = (SUBSTRING_INDEX(small_ocean_data.Latitude,'°', 1) + ((SUBSTRING_INDEX(SUBSTRING_INDEX(small_ocean_data.Latitude,"'",1), "°", -1)) + 0.0) /60) * CASE WHEN SUBSTRING_INDEX(small_ocean_data.Latitude, "' ", -1) in ('W', 'S') THEN (-1) ELSE 1 END select * from small_ocean_data;``` @VBoka can you please update answer accordingly. Thanks for the help! – Akash Nov 20 '19 at 19:07
  • Hi @Akash well the comment you made is just fine because the question is not about that. My answer is ok for the question and if I update the answer it will be confusing. Also, the main thing is the logic of the solution: use of SUBSTRING_INDEX. Cheers and thanks for the additional info. – VBoka Nov 20 '19 at 20:20