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:
- Get the data from the Natural Earth populated places the most important cities
- Get the origin using the id for Dubai
- Get the destinations as a select from the first query but closer to a distance using
ST_DWithin. Converting the geometries to the
geographytype the distance is calculated over a sphere, that is better than using the Web Mercator projection.
- Generate the route lines using the
ST_MakeLinefunction, converting the output to a geography type, split it using
ST_Segmentizeand transform it back to a
geometryin Web Mercator projection. This chain produces lines that walks the maximum circle between two points.
- Calculate a series of steps for every line between 0 and 1
- Calculate the final points dataset with these characteristics:
cartodb_ididentifier 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