Skip to content

Instantly share code, notes, and snippets.

@jsanz

jsanz/README.md

Last active November 19, 2018 09:33
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jsanz/8aeb48a274e3b787ca57 to your computer and use it in GitHub Desktop.
Save jsanz/8aeb48a274e3b787ca57 to your computer and use it in GitHub Desktop.
Download ZIP
14.000km to Dubai
Raw
README.md

14.0000km to Dubai

This SQL is an experiment to play with Postgis ST_LineInterpolatePoint function and other SQL tricks to generate a points dataset that travels from a starting point in Dubai to several cities all over the world and under a distance similar the coverage of a long distance flight (between 14 an 15 thousand kilometres).

This SQL is the main part of this CartoDB Visualization.

1000km to Dubai

The SQL is separated in several dataset constructions using the WITHclause so the approach is:

  1. Get the data from the Natural Earth populated places the most important cities
  2. Get the origin using the id for Dubai
  3. Get the destinations as a select from the first query but closer to a distance using ST_DWithin. Converting the geometries to the geography type the distance is calculated over a sphere, that is better than using the Web Mercator projection.
  4. Generate the route lines using the ST_MakeLine function, converting the output to a geography type, split it using ST_Segmentize and transform it back to a geometry in Web Mercator projection. This chain produces lines that walks the maximum circle between two points.
  5. Calculate a series of steps for every line between 0 and 1
  6. Calculate the final points dataset with these characteristics:

  • Fake a cartodb_id identifier using a window function

  • Fake a category to be used by torque (not sure why it didn't work with a normal torque)

  • Calculate the timing of each point as:

    • Fixed starting date and time
    • Substracting almost the time of the travel so they would all arrive to the destination at once
    • Adding some hours using a modulus function over the id so the data is spread a bit more
    • Adding the timing based on the step and the assumed standard velocity of a thousand km per hour

  • Calculate the geometry interpolating the step over the line

Raw
wargames.sql
with -- first data, main cities of the world
data as (
select * from jsanz.ne_10m_populated_places_simple_7
where
(megacity >= 0.5 and megacity <= 1)
and featurecla in
('Admin-0 capital','Admin-1 region capital',
'Admin-0 region capital','Admin-0 capital alt')
), -- from dubai
origin as (
select * from jsanz.ne_10m_populated_places_simple_7 where cartodb_id = 7263
), -- get cities closer to 14000 Km
dests as (
select d.*,
(ST_Distance(
o.the_geom::geography,
d.the_geom::geography
))/1000::int distance
from data d, origin o
where ST_DWithin(
o.the_geom::geography,
d.the_geom::geography,
14000000
)
), -- generate lines using the geographic maxmimum circle
lines as(
select
dests.cartodb_id, dests.name, dests.adm0name, dests.distance,
st_transform(
st_segmentize(
st_makeline(origin.the_geom, dests.the_geom )::geography,
10000
)::geometry,
3857) the_geom_webmercator
from origin,dests
), -- steps to interpolate, 300 per route, from 0 to 1
steps as (
select lines.cartodb_id,
generate_series(0, 300, 1 )/300.0 step
from lines
) -- finally the points over lines
select
-- fake autonum
row_number() over (partition by 1) cartodb_id,
-- fake category (needed by torque)
lines.name, lines.adm0name, 1 as fakecat,
-- calculate the timing of each point starting with this date
timestamp '2015-08-23 10:00'
-- they will all arrive _almost_ at the same time
-- to the destiny (1 hour would be same time)
- interval '45 minutes' * (lines.distance/1000.0)
-- some random exit distribution using modulus
+ interval '1 hour' * (lines.cartodb_id % 15)
-- actual distribution across the line at 1000km/h speed
+ interval '1 hour' * (steps.step*lines.distance/(1000.0)) faketime ,
-- get a point on the line using the step
ST_LineInterpolatePoint(
lines.the_geom_webmercator,
steps.step
) the_geom_webmercator
from lines
join steps on lines.cartodb_id = steps.cartodb_id
order by steps.step
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment