14.000km to Dubai

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.

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

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