So i have the following data for over a month ( an app that registers all i do)

  • Lat
  • Long
  • On ( DateTime )

Now i am interested in creating a history on plotting the points on Google Maps, similar to this blog post

How would i do this? I thought to group the queries andadding a weight per radius every 5 minutes ( eg. within the 50 meter). So for every 5 minutes, the weight gets +1 in weight. The weight has no max.

  • Then i would map it with Polylines on Google Maps so i have a pretty good overview.
  • Add a circle with a width depending the radius on every "still point", eg. at work or swimming. The weight is on its largest at 5.

Any thoughts? How would you create the SQL Query to calculate the distance ( if anyone has experience with his)


The easiest part of this question to answer is how to find the distance between two latitude/longitude points. You can do this using spherical trigonometry and a spherical Earth approximation. Formulas and code sample links can be found here. You probably also want to see if your database has GIS extensions to do it for you. For example, PostgreSQL has the PostGIS package that offers the ST_DistanceSpheroid function. Although, its not clear yet whether the best place to do that calculation for your problem is in the database query or in the application code.

The harder part is determining the still points. Whatever you implement will be an approximation. There will be gray areas and boundary conditions where its not clear how you'd like it to look. If you went to lunch across the street, is that still at work? What about half a mile down the street? I think a heuristic approach like you described could work, but you'd have to prototype it and experiment with real data to know how well.

To solve this rigorously, I would be looking at cluster analysis algorithms such as k-means. Optimization algorithms such as the Nelder-Mead simplex method or maybe particle swarm optimization might be useful for, say, maximizing the number of points that fit inside a fixed size circle. But, you won't find any silver bullets there, just potentially useful tools.

You may just want to start by plotting all the points in a polyline on the map, see how bad it looks, and go from there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.