1

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

mkyong
  • 12,497
  • 12
  • 37
  • 56
  • 1
    This is a bit of a tangent, but consider using instead of MySQL a NoSQL that is more optimized for rapidly changing data at web scale. E.g. http://www.mongodb.org/display/DOCS/Geospatial+Indexing – dkamins Jan 26 '12 at 22:06
  • @dkamins, MySQL is plenty appropriate for this simple task. – Brad Jan 27 '12 at 01:46
  • @Brad MySQL handling "the user's location will update every second" will not scale well. – dkamins Jan 27 '12 at 02:03
  • 1
    @dkamins, It is a very small amount of numerical data. With decent indexing, I don't really see the problem, even with many millions of rows. Mongo/Couch seem a bit over the top for this, especially when the data to be used is perfectly defined every time. No loose definitions needed. Why store all the overhead of each field definition for each record when you only have a couple floats? In addition, if he is having trouble understanding this basic concept, I don't think we want to add more at the moment. Your comment is fine, I simply disagree. That is all. – Brad Jan 27 '12 at 02:22

4 Answers4

2

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)
Brad
  • 159,648
  • 54
  • 349
  • 530
0

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

Devarsh Bhatt
  • 74
  • 1
  • 10
0

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.

Lars
  • 5,757
  • 4
  • 25
  • 55
-1

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

Scalarr
  • 746
  • 7
  • 26
  • Why use UTM? You're going to lose precision in the conversion, and waste more space in your DB. – Brad Jan 27 '12 at 16:16
  • @Brad The precition can be set to lets say a square meter which is more than enough for most applications. The GPS seldom offers that level of precision anyway. Additionally one can use the string as a primary unique or id in a database. And since its orthogonal one can easily determine neigburing id's etc. – Scalarr Jan 27 '12 at 21:29
  • My point is that if you have WGS84 coordinates, you need WGS84 coordinates, and you convert to something else, every time you convert to something and back, you're losing precision. If you have to do this several times, who knows how far off you might get... – Brad Jan 27 '12 at 21:34
  • Well, if you need and do all calculations in WGS84 or precision is a key issue then of course UTM might not be for you. Could not agree more. But then again, I wish I would have known that there were an alternative to strange ellipsoids before I went into the buisness of dealing with loads of location based data. In the end it all comes down to what the application is. – Scalarr Jan 27 '12 at 22:00
  • This is a really bad idea. UTM uses different ellipsoids; e.g In Italy WGS84 is not used for UTM. And you have to store the UTM Zone, in your DB, too. And when you want to do calculattioons (distances) between pints in different UTM zones, then it becomes demanding. – AlexWien Feb 18 '13 at 02:20