SQL: Routing with CartoDB

README.md

Using new CartoDB routing functions, take a scheduled route table made of straight lines with a rough plan for a route and convert it in a detailed route using the shortest path on a car.

Check the viz

create-table-from-routes.sql

DROP TABLE IF EXISTS detailed_routes;

CREATE TABLE detailed_routes AS
WITH -- Dump the lines geometries 
dumps AS (
  SELECT 
    cartodb_id AS edge_id,
    name,
    ST_DumpPoints(the_geom) dp
  FROM draft_routes
), -- Extract the index and point geometries
points AS (
  SELECT
    edge_id, name, (dp).path[2] AS index, (dp).geom AS the_geom
  FROM
    dumps
), -- Generate the rows with pairs of points
leads AS (
  SELECT
    edge_id*1000 + index AS cartodb_id,
    edge_id,
    index,
    name,
    the_geom,
    lead(the_geom) over (partition by edge_id ORDER BY index) AS the_geom_next
  FROM
    points
) -- Create the final schema generating a route line
SELECT
  l.cartodb_id,
  l.edge_id,
  l.index,
  l.name,
  r.shape as the_geom,
  r.duration,
  r.length
FROM
   leads l
CROSS JOIN LATERAL (
  SELECT shape, duration, length 
  FROM 
  	cdb_route_point_to_point(
      l.the_geom,
      l.the_geom_next,
      'car', 
      ARRAY['mode_type=shortest']::text[]
    ) 
) AS r
WHERE
  the_geom_next IS NOT NULL;

SELECT CDB_CartoDBfyTable('solutions','detailed_routes');