Current GPS location saved to MySQL

Robert Hester
Robert Hester used Ask the Experts™
on
I would like to update a MySQL database with the latitude and longitude values of the mobile when user clicks on a button. The purpose is to register the date/time he arrived at his programmed destination for which  Latitude and Longitude would have previously already  been informed.
To finish up, the diference in meters between programmed destination and current location should be also saved on the database.
I would appreciatte if the necessary code could be presented for the solution.
Thanks in advance.
Robert
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Simple to do. Just take time to code.

Likely best to hire someone to provide you with real/full working code, rather than code snippets.

Or you can search GitHub + you'll likely find some working code matching your requirements.

Author

Commented:
Hi David,

I wouldn´t think this would require as much coding as some answers and solutions I´ve seen on EE...
Thanks for your hint anyway...any idea where I could hire someone? Would you be available?

Robert
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
There are multiple items here that need to be addressed.

  • updating a database
  • Grabbing lat/Lon
  • Calculating distance
  • Web app vs native app
  • Server-side code

It will be best to break down your needs to each item and one item per queston.

I suggest starting the conversation with web app vs native. You can achieve your goals with either. There are pros and cons for each.  Most important though is complexity and skill level is greater with a native app.

What is your skill level and how much time are you wanting to invest. Or do you just want somebody to do it for you?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Scott,
Thank you for your reply with orientations...

Basically I am not a developer. I am a consultant that uses with ease PHPRunner from Xiinesoft. Every hour or then I need a code snippet that I either create myself or seek for help as I am now.
So I have already a full web app project (PHP) and need to store in MySQL database the current datetime plus latitude/longitude when user clicks a button.

The purpose of this snippet is to control the time of arrival of the visitor on a scheduled visit.
Therefore there is a record on the Visits table for each visit of each user (visitor). The fields Arrival_Dt, Arrival_Long and Arrival_Lat are empty to be updated when the button is clicked.


I will reformulate and renew my question to simplify as you suggest.
Thank you for your support.
Robert
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
Thank you for the details.

I think what I would do is have a table with the row_id, start_time, start_lat, start_lon, end_time_end_lat, end_lon where the start fields are updated on initial insert of the row and the end fields are updated on arrival.

Then any calculations for distance could be done with some math https://www.movable-type.co.uk/scripts/latlong.html. But that will give you a straight line distance. You probably want distance given a certain route? You could use a maps api for this. But then you may not know the exact route they took.  

One option worth looking into is once they click the start button, have an ajax call run every 15 or 30 seconds until the end button is clicked. When you click the start button, you will create a table tracking_header with fields row_id, start_timestamp, lat, lon and end_timestamp and perhaps some type of name?  Then the ajax request will insert records into another table called tracking_detail with row_id, tracking_header_id, timestamp, lat, lon.  Where tracking_header_id is related to the parent table. The ajax call will run as long as the field end_timestamp  from the parent table is null.

Now that you have all this detail, you can calculate a bunch of short lines for better accuracy. The less you make your interval (going from 30 seconds to 15 seconds to 5 seconds), the greater your accuracy.
Hi Scott,

Thank you very much for your attention devoted to my question.
I ended up finding a very good  solution of my own together with PHPRunner staff.. Not much code and split between a button and one or two events handling.

OnClick Event for Button (Javascript)
function getLocation()
{
      if (navigator.geolocation)
      {
            navigator.geolocation.getCurrentPosition(showPosition);
      }
}
function showPosition(position)
{
      var vlat = position.coords.latitude;
      var vlong = position.coords.longitude;
      $.get( "visitas_list.php", {
            a: "geo1",
            vlat: vlat,
            vlong: vlong,
            id:row.row.keys[0]
      })
      .done(function(txt){
            if(!txt)
                  alert("Localização registrada");
      })
}

if(confirm("Permite que RHC registre a sua localização neste momento?"))
      getLocation();
else
      return false;


Occurs when page is processed (PHP)
if(postvalue("a")=="geo1"){
      DB::Exec("update visitas set Chegada_Lat=".postvalue("vlat").", Chegada_Long=".postvalue("vlong").", Chegada_Dt='".now()."' where Código=".postvalue("id"));
      die();
}
if(postvalue("a")=="geo2"){
      $rs = DB::Query("select * from visitas where Código=".postvalue("id"));
      $data = $rs ->fetchAssoc();
      if($data["Saida_Long"] || $data["Saida_Lat"])
            echo "error";
      else
            DB::Exec("update visitas set Saida_Lat=".postvalue("vlat").", Saida_Long=".postvalue("vlong").", Saida_Dt='".now()."' where Código=".postvalue("id"));
      die();
}

Cheers
Robert
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
That updates your lat/lon.  But didn't address the distance calculation/reporting.  The one suggestion I have worth looking into is to run the ajax every x seconds or minutes.  Then run your distance calculations from each point.   Imagine a user that had to get around a lake where there is no bridge. They would have to travel around the lake to get to the other side. The end point may only show up as a half mile, but the user traveled 2 miles to get there.  

You have the main function working though!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial