CODEWITHSUNDEEP
sql-servert-sqlsql-server-2016exifgeography
gotqn
gotqn

Reputation: 43636

How to convert GPS exif to geography?

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

Upvotes: 1

Views: 426

Answers (2)

HoneyBadger
HoneyBadger

Reputation: 15140

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.

Upvotes: 1

MJH
MJH

Reputation: 1750

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.

Upvotes: 1

Related Questions