CODEWITHSUNDEEP
postgresqldistancehaversine
Joyson
Joyson

Reputation: 3040

Calculate distance travelled using PostgreSQL query

I am using PostgreSQL to store the location of a user send to the server by my android app. I needed to find the total distance travelled by the user for a particular time duration.

The user location is stored in the following table : 

CREATE TABLE userlocation
(
latitude character varying,
longitude character varying,
geopoint point,
userid integer,
locationtime timestamp
)

I retrieved the records and calculated the distance in java using the following haversine distance method : 

public double getdistance(final double lat1, final double lon1, final double lat2, final double lon2, final char unit) {
    final double theta = lon1 - lon2;
    double dist = Math.sin(deg2rad(lat1)) * Math.sin(deg2rad(lat2))
            + Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2))
            * Math.cos(deg2rad(theta));
    dist = Math.acos(dist);
    dist = rad2deg(dist);
    dist = dist * 60 * 1.1515;
    if (unit == 'K') {
        dist = dist * 1.609344;
    } else if (unit == 'N') {
        dist = dist * 0.8684;
    }
    if (Double.isNaN(dist)) {
        dist = 0.0;
    }
    return (dist);
}

However this calculation is time consuming especially while calculating the distance for multiple days as there are a lot of records. I decided to try doing the distance calculation at the database level to reduce the calculation time. I found the the following query which allows me to calculate the distance to a certain point : 

SELECT latitude, longitude, geopoint <-> '19.23,72.89' AS distance FROM userlocation ORDER BY distance;

I tried to create a query that would either return the total distance traveled or atleast calculate the distance between two consecutive rows and store it in another column so that I calculate the sum in Java instead of the distance calculations.

I have tried searching for a solution but I have been unable to find one yet. Most of the questions on SO deal with distance calculation between two points.

I do not have PostGIS at the moment. Would it be possible to calculate distance in PostgreSQL or should I just continue with my current approach? In that case is there an alternative for reducing the distance calculation time.

Upvotes: 1

Views: 2195

Answers (1)

Abincepto
Abincepto

Reputation: 1036

I had the same problem last month.

I added the module Earthdistance to PostgreSQL. This plugin add functions to compute the great circle distances between two points.

Installation is simple:

CREATE EXTENSION "cube";
CREATE EXTENSION "earthdistance";

Hope that helps

Upvotes: 4

Related Questions