Exif and Access queries

[dlugirapfr]

Hi All,

I read a lot of articles about Exif coordinates in Access but maybe you have solution how to deal it in queries.
I know there is huge tool https://www.access-programmers.co.u...current-geolocation-data-using-access.299932/

I have table like this

ID	URL
1	c:\test1.jpg
2	c:\test2.jpg

etc.
Do you know how to create query like this.

ID	URL	Latitude	Longitude	Altitude
1	c:\test1.jpg	-32,434	1,33	0,2
2	c:\test2.jpg	-32,433	1,44	0,6

I don't know how to adapt solution https://www.everythingaccess.com/tutorials.asp?ID=Extracting-GPS-data-from-JPEG-files into query.
Thank you for your help.

Karol

 

[isladogs]

Hi
You referred to my current geolocation app above.
Have you seen another of my apps which extracts any geolocation data from photos: Get Geolocation Data from Photos | Access World Forums (access-programmers.co.uk). Its the first item in the similar threads below. As part of this, I also used the code supplied by Wayne Phillips from EverythingAccess.com

The latest version is also available on my website: Get Geolocation From Photos - Mendip Data Systems

Once you have extracted the coordinates as shown, it would be a trivial matter to save those in a table for future use if required

 

[dlugirapfr]

Hi thank's for replay but to be honest when I enter code of your app or "Australian app" I don't full know how to adapt code to query function.
I want to create function which I will put to queries like Latitude([table1].url). I already put to my database these three modules: GPSExifProperties, GPSExifReader, GPSExifReader_helper but I have problem to create public function: Latitude, Longitude, Altitude. Thank's for your help.

 

[isladogs]

Sorry - no idea what you mean by "Australian app" and I'm struggling to follow what you're having problems with in my app.

You say you've looked at my photo location app.
If you look at the code in cmdBrowse_Click event of the main form it includes this section:

 With GPSExifReader.BrowseAndOpenFile()
        Me.txtPath = .FilePath
     '   Me.txtDateTime = .DateTimeOriginal
        strTimeTaken = .DateTimeOriginal
        Me.txtAltitude = .GPSAltitudeDecimal
        'Me.txtLatitude = .GPSLatitudeDecimal
        'Me.txtLongitude = .GPSLongitudeDecimal
        sngLat = .GPSLatitudeDecimal
        sngLong = .GPSLongitudeDecimal
        Me.txtDirection = .GPSImgDirectionDecimal
    End With

The sngLat/sngLong variables are used by me to nudge the displayed map using the direction buttons on the form.
If you don't want that feature, just use these 3 lines:

Me.txtAltitude = .GPSAltitudeDecimal
Me.txtLatitude = .GPSLatitudeDecimal
Me.txtLongitude = .GPSLongitudeDecimal

To use these values in a query, do one of the following:
1. Use form control references e.g. Latitude: Forms!frmGelocation.txtLatitude
2. Save those value as variables e.g. Latitude: sngLat
3. Save those values to a table and use those in the query

4. Or if you want to use a public function, then possibly e.g.

Public Function GetLatitude()
   GetLatitude = sngLat
End Function

Then in your query, use Latitude: GetLatitude()

If I'm completely missing what your point is, then you'll need to explain more clearly

 

[dlugirapfr]

Thanks for replay I will try to manage with it Australian app I thought that: https://www.everythingaccess.com/tutorials.asp?ID=Extracting-GPS-data-from-JPEG-files

Many thanks go to Australia's North Central Catchment Management Authority for allowing us to release this code publicly.

Main idea of my solution is creating link to google maps with direct link like:

Map: "http://maps.google.com/maps?ll=" & Replace([tbl].[Location Latitude];",";".") & "," & Replace([tbl].[Location Longitude];",";".") & "&spn=0.03,0.03&t=m&q=" & Replace([tbl].[Location Latitude];",";".") & "," & Replace([tbl].[Location Longitude];",";".")

So I need this location - I hope I will try to manage with it

 

[isladogs]

Thanks for reminding me of the Australian origins of Wayne's code.

Just to recheck have you actually looked at my app shown with a link in post #2 as it really doesn't appear so from your responses.
If so you would realise that the EXIF data is used to generate the Google static map. The code is in the GetMapImage procedure.
I've already done all the work for you. You are free to use my code in your own app - its open source code.

 

[dlugirapfr]

Unfortunately I have problem with creating public function e.g. PublicGetLatitude([name field]). I think it is easy but my mind doesn't work.
Is it possible that you will write how this code should look like? According of your code I will prepare PublicGetLongitude etc...

Thank you very much.

 

[isladogs]

Karol
I have already provided code including that to get the value from a function - see post #4.
All the code you need is included in my app. All you have to do is adapt it for your own purposes

In my opinion there is no real value in saving the coordinates to a table as you can read the EXIF data direct from any photo you select.
By doing that the map can be loaded at the same time as the image & its coordinates are displayed (as in my app)
If you first save the data to a table then use that saved data to generate the Google maps URL, it will take slightly longer to run.

However, if you really want to save the data to a table, use a DLookup expression to get the value. Something like this

Public Function GetLatitude()
   GetLatitude = DLookup("Latitude", "tblPhotoLocation", "FilePath = '" & Me.txtFilePath & "'")
End Function

Best of luck. If you are still stuck, please study the code in my app carefully.

 

[dlugirapfr]

I want to save coordinates from Exif because my database create pdf report with picture inside and url to it, but I want to also and url to google maps with coordinates of picture. I hope I will manage with public function who will query coordinates from exif before final report in pdf

 

[isladogs]

Actually so does mine...here's an example of the report for a different photo and this time using roadmap option rather than hybrid
I haven't printed the url on my report but its simple enough to add it.

It seems to me that I've given you all the info you need...if not more than that. You should be able to do the rest for yourself.

 

[dlugirapfr]

Thank's but I don't have api key and I will not have it in future. So I need to create url to google maps not image of it.
Every record has about 20 url's... url1,url2 etc...

 

[isladogs]

Why on earth didn't you say that you wanted to do it without the use of an API key from the start?
In fact, the API key itself is free and you can do up to about 20000 searches per month without any charges being levied

Anyway, as I keep saying, if you study the code in my app, it shows you how to construct a Google map url which you can do without an API.
I've given you a lot of help. Now you need to do what's left for yourself.

 

[Dreamweaver]

This is what I use for getting a map location I found the link In @isladogs App above and updated

FormatLink(Trim("https://www.google.co.uk/maps/place/" & AddressLine & "+" & City))

The format link function you will require

Public Function FormatLink(Str As String) As String
    Dim FStr As String
On Error GoTo HandleErr

    FStr = Replace(Str, " ", "+")
    FormatLink = Replace(FStr, vbCrLf, "+")

HandleExit:
    Exit Function
    
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Function

 

[isladogs]

Hi Mick
Thanks for jumping in but that won't necessarily help the OP.
The EXIF data includes the coordinates but not the address line or city.
My code works out the nearest address to that location but that's not needed for the map url. Just use the latitude/longitude from the photo.

 

[Dreamweaver]

Sorry I read something that talked about the link which i found in your program.
I use it a lot in the project I'm working on and along with my google account I can mark locations I need to get too.

 

[dlugirapfr]

Hi again, last question. (many thanks for replay)
I adapted code in form load and it works is looks like this.

Private Sub Form_Load()

On Error GoTo ExifError

    Dim strDump As String
    Dim filePath1 As String
  
  
    filePath1 = "C:\test.jpg"
      
    With GPSExifReader.OpenFile(filePath1)
        If .GPSLatitudeDecimal > 0 Then
                                 
        txtOutput.Value = "http://maps.google.com/maps?ll=" & Replace(.GPSLatitudeDecimal, ",", ".") & "," & Replace(.GPSLongitudeDecimal, ",", ".") & "&spn=0.03,0.03&t=m&q=" & Replace(.GPSLatitudeDecimal, ",", ".") & "," & Replace(.GPSLongitudeDecimal, ",", ".")
        Else
        txtOutput.Value = "No GPS"
        End If
    End With
  
    Exit Sub
  
ExifError:
    MsgBox "error"


End Sub

Could you help me to convert it to public function? I don't want to use that code form, but as query result. Thank you very much.

 

[isladogs]

Sorry I read something that talked about the link which i found in your program.
I use it a lot in the project I'm working on and along with my google account I can mark locations I need to get too.

Hi Mick
You can use any of the following in the URL for a Google map

1. Geocoordinates - Latitude & Longitude
2. Postcode (or equivalent for other countries
3. Full or partial address

In my mapping apps, I use all three methods for different purposes but usually its method 1

 

[isladogs]

Hi again, last question. (many thanks for replay)
I adapted code in form load and it works is looks like this.
....

Could you help me to convert it to public function? I don't want to use that code form, but as query result. Thank you very much.

I have already told you how to do so - see post #4
Save those EXIF values as variables e.g. sngLat, sngLong then use those in your url expression to create your function

 

[dlugirapfr]

I have already told you how to do so - see post #4
Save those EXIF values as variables e.g. sngLat, sngLong then use those in your url expression to create your function

Hi Colin I know that you put all in #4 but maybe for you it is logical and easy but for me it is difficult and I don't know how to do this.
I tried few times creating vba code but still there is error in it. Only what work in my case is that vba code in load form not as public function which I can use in query.
Apologies for bothering you.

 

[isladogs]

I'm sorry but everyone finds coding hard at first. Each completely new type of project I undertake has a learning curve for me as well.
I've given you more than enough info to allow you to complete this for yourself.
The only way to succeed is to persevere and by doing so you will learn far more than if I provide the complete solution for you.
Whilst someone may step and do that, the purpose of a forum is not to provide a free code authoring service.

You say you tried to write a function but it errored.
OK then, post your function(s) and state what errors you got and on which line.
Then someone, not necessarily me, can offer hints to help you move forward

Having said that, I'm still unclear why you need a query or functions anyway.