CODEWITHSUNDEEP
phpandroidmysqlgpsgeospatial
mkyong
mkyong

Reputation: 12947

Storing multiple GPS coordinates in MySQL

I'm making an Android app that tracks a user and displays their location in real time. I have it working, but I'm having issues storing the coordinates in a database properly. Right now, the user's location will update every second, and it stores the location in a database and then the web app pulls the most recent from the database. I want to be able to store the list of locations in one row for a particular user. I read some about GeoSpatial information in MySQL, and I think that the linestring datatype would work, but I can't seem to find enough information about how to implement the query in PHP. Can someone provide an example of how to keep appending coordinates to the database in a linestring type using PHP? Or provide a suggestion of how to continually store coordinates using one row of a database.

Thanks

Upvotes: 1

Views: 5942

Answers (4)

Devarsh Bhatt
Devarsh Bhatt

Reputation: 74

Or you can just simply use Firebase for your database which is very flexible and you can easily work in firebase

Upvotes: 0

Brad
Brad

Reputation: 163612

Simply store each point the user is located at into a table, along with an ID and timestamp. You can then assemble the points with a query.

Don't store an entire track in one row, or you won't be able to do much with the data later.

Edit: Here is what your table will look like:

gps_points

  • id (bigint)
  • user_id (int)
  • timestamp (timestamp or datetime, depending on your needs)
  • lat (double)
  • lon (double)

Upvotes: 3

Scalarr
Scalarr

Reputation: 756

My experience is that the UTM format is easier to store in a database since its orthogonal and has a really convenient syntax. And it is suitable for single line string too. You can find information and a handy class that easily converts between GPS and UTM here:

http://www.ibm.com/developerworks/java/library/j-coordconvert/index.html

Upvotes: -1

Lars
Lars

Reputation: 5799

A GPS coordinate is a set of X,Y and Z float value, not a set of points to interpolate a curve (which essentially is what the linestring datatype is for). So I would store the points in 3 float columns with the additional information like a timestamp. If you need, you can extrapolate the linestring afterwards from the given data to show on a map.

Upvotes: 0

Related Questions