Skip to content

Instantly share code, notes, and snippets.

@jatorre
Created October 15, 2012 08:04
Show Gist options
  • Save jatorre/3891312 to your computer and use it in GitHub Desktop.
Save jatorre/3891312 to your computer and use it in GitHub Desktop.
Calculating Stratos distances
//Furthest distance travelled from start point. // http://javi.cartodb.com/api/v2/sql?q=
WITH points AS
(SELECT
(SELECT (the_geom) FROM stratos ORDER BY id ASC LIMIT 1) as p1,
(SELECT (the_geom) FROM stratos
WHERE ST_x(the_geom)<180 AND ST_x(the_geom)>-180 AND ST_x(the_geom)<>0
AND ST_x(the_geom)<>0 ORDER BY ST_Distance(the_geom,(SELECT the_geom
FROM stratos ORDER BY id ASC LIMIT 1)) DESC LIMIT 1) as p2
)
SELECT ST_distance(p1::geography,p2::geography) as furthest_distance FROM points
//Distance from start to end // http://javi.cartodb.com/api/v2/sql?q=
WITH points AS
(SELECT
(SELECT (the_geom) FROM stratos ORDER BY id ASC LIMIT 1) as p1,
(SELECT (the_geom) FROM stratos ORDER BY id DESC LIMIT 1) as p2
)
SELECT ST_distance(p1::geography,p2::geography) as distance_from_start_to_end FROM points
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment