1

In Postgres 9.1+ database contains route points:

create table doksyndm (
id serial primary key,
readingtime timestamp with time zone not null,
latitude float,
longitude float
 );

Points are saved from browser using navigator.geolocation calls from javascript.

How to calculate route lenght from this data ? Route is less than 300 km so direct line lenghts between points can summed. It should work in Postgres 9.1 and newer.

Result shoud be one number, total distance between points in kilometers. Database and javascript code sending points can re-factored if this is reasonable.

Answers in Calculate distance between two points in google maps V3 how to implemtn this in produral languages.

They use for loop which is not available in SQL. Maby SUM() with window function can used to implement this ?

Community
  • 1
  • 1
Andrus
  • 26,339
  • 60
  • 204
  • 378
  • 1
    http://stackoverflow.com/questions/1502590/calculate-distance-between-two-points-in-google-maps-v3/1502821#1502821 – Teemu Nov 09 '16 at 19:46
  • Answers to this contain code in C#, Javascript and Java. They use for loop which is not available in sql. How to implement this in sql or Postgres? Probably some window function should used. – Andrus Nov 09 '16 at 20:26
  • ??? There are plenty of answers without loops. The idea was to introduce the haversine formula, you can apply that in any language you want. With that rep you already know, that you can't expect getting a copy-pasta-answer for a question which doesn't show any attempt to solve the problem. – Teemu Nov 10 '16 at 05:09
  • mlinth great answer looks like copy and paste answer. Attempt description in question is not mandatory. So why I shouldn't expect such answer ? – Andrus Nov 10 '16 at 06:25
  • 1
    [Becoming features](http://meta.stackoverflow.com/questions/274630/should-we-add-a-do-my-work-for-me-close-reason) ; ). – Teemu Nov 10 '16 at 06:30

1 Answers1

1

I'm assuming you want the distance from the earliest point to the next earliest, and so on...

As of Postgresql 9.3, there is an elegant solution using a lateral join.

First, install the postgis extension, so you can use its distance functions:

CREATE EXTENSION POSTGIS;

Now the query:

 SELECT SUM(st_distance(st_point(a.longitude,a.latitude)::geography,
                       st_point(b.longitude,b.latitude)::geography)
            ) / 1000 AS distance_km FROM doksyndm a
LEFT JOIN LATERAL
 (SELECT * FROM doksyndm WHERE readingtime > a.readingtime limit 1) b ON TRUE;

This query using distinct on should work for all versions:

 SELECT SUM (st_distance(st_point(alon,alat)::geography,
              st_point(blon,blat)::geography))/1000 as distance_km FROM
    ( select distinct ON (a.readingtime) a.readingtime, 
                                        a.latitude as alat,
                                        a.longitude as alon, 
                                        b.latitude as blat ,
                                        b.longitude as blon
    FROM doksyndm a inner join doksyndm b on a.readingtime < b.readingtime
 ORDER by a.readingtime,b.readingtime) x
Community
  • 1
  • 1
mlinth
  • 2,968
  • 6
  • 30
  • 30
  • `CREATE EXTENSION POSTGIS` returns `ERROR: could not open extension control file "/usr/share/postgresql/9.1/extension/postgis.control": No such file or directory` There are other extensions but no postgis in this directory. How to install postgis in Postgres 9.1 in Debian Squeeze x64 Linux? – Andrus Nov 09 '16 at 22:16
  • Never tried, but there's a guide e.g. here: http://postgis.net/install/ – mlinth Nov 09 '16 at 22:19
  • This page wrote that postgis is not supported in 9.1. How to use formula for distance calculation instead of postgis ? – Andrus Nov 10 '16 at 07:57
  • Well, I can't see that on the page. Postgis has been running on much older versions of Postgres than 9.1, so I am sure it is possible to install it. Just google "install postgis linux". It'll be easier to install postgis and use its distance calculation than writing your own function. – mlinth Nov 10 '16 at 10:22
  • I have installed Postgres 9.1 in squeeze from backports. I havent found any packages. Trying to install from source throws unmet dependencies error. I found `create or replace function gc_dist(_lat1 float8, _lon1 float8, _lat2 float8, _lon2 float8) returns float8 as $$ select ACOS(SIN($1)*SIN($3)+COS($1)*COS($3)*COS($4-$2))*6371; $$ language sql immutable;` from https://www.postgresql.org/message-id/AANLkTimvprPanJ48_uzuWoHJfGsX8ihXO7bLStFfjWf8@mail.gmail.com Will this work? – Andrus Nov 10 '16 at 17:51
  • 1
    Well, given that it appears in the link Teemu posted, probably, yes BUT note that your coordinates need to be in radians. There's an answer here: http://stackoverflow.com/questions/6975669/using-the-haversine-formula-with-postgresql-and-pdo – mlinth Nov 10 '16 at 20:19
  • I wrapped all arguments to radians() call: `select ACOS(SIN(radians($1))*SIN(radians($3))+COS(radians($1))*COS(radians($3))*COS(radians($4)-radians($2)))*6371; ` Is this OK ? – Andrus Nov 11 '16 at 08:17
  • Route appears in wrong position in google map Required precision in some meters. Is is sufficient to use float data type to store latitude and longitude or should double or numeric used? – Andrus Nov 11 '16 at 08:55