Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.