How to convert GPS exif to geography?

Question

I have attachments table which has GPSLatitude and GPSLongitude columns for each attachment. It's legacy code which is populating the fields and the values looks like:

GPSLatitude
50/1,5/1,1897/100

GPSLongitude
14/1,25/1,4221/100

Is there any build in function which I can use in order to convert them to latitude and longitude decimal values like this:

Location Latitude   
41.5803 
Location Longitude
-83.9124

I can implement SQL CLR function if this can be done easier with .net also.

What is most difficult for me right now is to understand what these values represent. The legacy code is using some API with no documentation about the returned format and how to read it.

The values above are just for showing how the data is formatted. The following library is used to get the values - chestysoft like this:

 IF Image.ExifValueByName("GPSLatitude") <> "" THEN GPSLatitude = Image.ExifValueByName("GPSLatitude") ELSE GPSLatitude = NULL END IF
 IF Image.ExifValueByName("GPSLongitude") <> "" THEN GPSLongitude = Image.ExifValueByName("GPSLongitude") ELSE GPSLongitude = NULL END IF

Could those legacy values be some convoluted variant of "Degrees, Minutes and Seconds" format? — Mazhar, Apr 24 '18 at 08:12
@Cool_Br33ze I guess so. I am searching and it must be `(1) degrees, (2) degrees and decimal minutes, or (3) degrees, minutes, and decimal seconds.` - I guess the `,` is separating these values. — gotqn, Apr 24 '18 at 08:19
In your data is the value after the first two `/` always 1 and after the third `/` always 100? — Mazhar, Apr 24 '18 at 08:28
Is `50/1,5/1,1897/100` equal to `41.5803 ` or are the values in the question just there to show the formatting? — MJH, Apr 24 '18 at 08:48
If you menage to convert those values to more commonly used latitude/longitude values, do you have a way to validate those values? — HoneyBadger, Apr 24 '18 at 08:49
@MJH Just formatting. I have no idea to what this could be translated. — gotqn, Apr 24 '18 at 09:16

score 1 · Answer 1 · answered Apr 24 '18 at 09:19

Assuming that @Cool_Br33ze is correct, and the data is in degrees, minutes and seconds, you can calculate the values you need using the following:

declare @v varchar(30) = '50/1,5/1,1897/100'

select  @v Original_Value,
        convert(decimal(18,4), left(@v, charindex('/', @v) - 1)) [Degrees],
        convert(decimal(18,4), substring(
                                            @v, 
                                            charindex(',', @v) + 1, 
                                            charindex('/', @v, charindex(',', @v)) - (charindex(',', @v) + 1)
                                        ) / 60.0
        ) [Minutes],
        convert(decimal(18,4), substring(
                                            @v, 
                                            charindex(',', @v, (charindex(',', @v) + 1)) + 1, 
                                            charindex('/', @v, charindex(',', @v, (charindex(',', @v) + 1))) - (charindex(',', @v, (charindex(',', @v) + 1)) + 1)
                                        ) / 360000.0
        ) [Seconds]

It looks a bit of a mess, but it splits out the degrees, minutes and seconds (converted to DECIMAL(18,4)), all you need to do is add the three values together to get your Lat/Long value in degrees.

I'd test it thoroughly before implementing it though.

HoneyBadger · Accepted Answer · 2018-04-24T09:57:38.930

1

I'm fairly certain you should read it as:

50/1: 50 Degrees
5/1: 5 Minutes
1897/100: 18.97 Seconds

This would put the location you've given in New York (assuming N/W), does that make sense? If you have no way to validate the output it's very difficult to make any other suggestion... See also here

In the link you provided, you can upload a picture to view the exif data. There you can test with known locations. It is also apparent that in the values you mentioned, the GPSLatitudeRef and GPSLongitudeRef are missing. You need these to change the values to a location. Do you have those values in your table? Otherwise you'll have to make (wild) assumptions.

This is by the way the standard EXIF notation for latitude/longitude; I assume there are many, many tools to convert it.

edited Apr 24 '18 at 09:57

answered Apr 24 '18 at 09:24

HoneyBadger

14,750
3
34
48

Those are the assumptions I have used in my answer. – MJH Apr 24 '18 at 09:29
@MJH, not really. What I meant was, the `/` in the value should be read as a division. So you shouldn't divide by `60.0` and `360000.0`, but by `1` and `100` respectively. – HoneyBadger Apr 24 '18 at 09:54
I have divided minutes by 60 and seconds by 3600 to convert them to degrees, the intention is to add the three values together to give an answer in degrees. – MJH Apr 24 '18 at 09:59
1

@MJH, Ah yes, I was still thinking in minutes/seconds rather than degrees. I think you would still need to divide the seconds by 100 though (and the minutes by 1(there might be a different value to divide by in other values)). – HoneyBadger Apr 24 '18 at 10:05
That's something for the OP to watch out for. :) – MJH Apr 24 '18 at 10:41
It seems that you are right. These values recorded as float using division. I have tested this creating a attachment, uploading it and then locating the coordinates. – gotqn Apr 24 '18 at 10:43

How to convert GPS exif to geography?

2 Answers2