geocoding + routing + named + static

0_query.sql

WITH point1 AS (
  SELECT cdb_geocode_postalcode_point(<%= postcode1 %>, 'USA') as geo 
), 
point2 AS (
  SELECT cdb_geocode_postalcode_point(<%= postcode2 %>, 'USA') as geo 
), 
route AS (
  SELECT 
    r.shape as geo, 
    r.length 
  FROM 
    point1, 
    point2, 
    cdb_route_point_to_point(point1.geo, point2.geo, 'car') r 
) 
SELECT 
  3 as cartodb_id, 
  ST_Transform(geo,3857) as the_geom_webmercator, 
  to_char(length::real, '999,999 km') as label 
  FROM route 
UNION ALL 
SELECT 
  1 as cartodb_id, 
  ST_Transform(geo,3857) as the_geom_webmercator, 
  'From ' || <%= postcode1 %> as label 
  FROM point1 
UNION ALL 
SELECT 
  2 as cartodb_id, 
  ST_Transform(geo,3857) as the_geom_webmercator, 
  'To ' || <%= postcode2 %> as label 
  FROM point2

1_styling.css

#layer['mapnik::geometry_type'=1] {
marker-width: 15;
marker-fill: #FFB927;
marker-fill-opacity: 0.9;
marker-line-color: #ffda8d;
marker-line-width:  1.5;
marker-line-opacity: 1;
marker-placement: point;
marker-type: ellipse;
marker-allow-overlap: true;
}
#layer['mapnik::geometry_type'=2] {
line-color: #3EBCAE;
line-width: 1.5;
line-opacity: 1;
}
#layer::labels ['mapnik::geometry_type'=1]{
  text-name: [label];
  text-face-name: 'DejaVu Sans Book';
  text-size: 10;
  text-fill: #6F808D;
  text-label-position-tolerance: 0;
  text-halo-radius: 1;
  text-halo-fill: #FFFFFF;
  text-dy: -10;
  text-allow-overlap: true;
  text-placement: point;
  text-placement-type: dummy;
}

2_template.json

{
    "auth": {
      "method": "open"
    },
    "version": "0.0.1",
    "name": "routingtest",
    "placeholders": {
      "postcode1": {
        "type": "sql_ident",
        "default": "28001"
      },
      "postcode2": {
        "type": "sql_ident",
        "default": "28002"
      }
    },
    "layergroup": {
      "version": "1.0.1",
      "layers": [
        {
          "type": "http",
          "options": {
            "urlTemplate": "http://{s}.basemaps.cartocdn.com/light_nolabels/{z}/{x}/{y}.png",
            "subdomains": "abcd"
          }
        },
        {
          "type": "cartodb",
          "options": {
            "sql": "WITH point1 as (SELECT cdb_geocode_postalcode_point(<%= postcode1 %>, 'USA') as geo ), point2 as (SELECT cdb_geocode_postalcode_point(<%= postcode2 %>, 'USA') as geo ), route AS (SELECT r.shape as geo, r.length FROM point1, point2, cdb_route_point_to_point(point1.geo, point2.geo, 'car') r ) SELECT 3 as cartodb_id, ST_Transform(geo,3857) as the_geom_webmercator, to_char(length::real, '999,999 km') as label FROM route UNION ALL SELECT 1 as cartodb_id, ST_Transform(geo,3857) as the_geom_webmercator, 'From ' || <%= postcode1 %> as label FROM point1 UNION ALL SELECT 2 as cartodb_id, ST_Transform(geo,3857) as the_geom_webmercator, 'To ' || <%= postcode2 %> as label FROM point2 ",
            "layer_name": "route",
            "cartocss": "#layer['mapnik::geometry_type'=1] {marker-width: 15; marker-fill: #FFB927; marker-fill-opacity: 0.9; marker-line-color: #ffda8d; marker-line-width:  1.5; marker-line-opacity: 1; marker-placement: point; marker-type: ellipse; marker-allow-overlap: true; } #layer['mapnik::geometry_type'=2] {line-width: 9; line-color: #3EBCAE; line-opacity: .7; } #layer::labels {text-name: [label]; text-face-name: 'DejaVu Sans Book'; text-size: 15; text-fill: #343A40; text-label-position-tolerance: 0; text-halo-radius: 1; text-halo-fill: #FFFFFF; text-dy: -10; text-allow-overlap: true; text-placement: point; text-placement-type: dummy; } ",
            "cartocss_version": "2.1.1",
            "interactivity": [
              "cartodb_id",
              "name"
            ],
            "table_name": "route",
            "attributes": {}
          }
        }
      ]
    },
    "view": {
      "zoom": 4,
      "center": {
        "lng": -98.8, 
        "lat": 38.65
      },
      "bounds": {
        "west" : -129.7,
        "south":   24.5,
        "east" :  -67.9,
        "north":   50.5
      } 
    }
}

3_create_namedmap.sh

curl -X POST \
   -H 'Content-Type: application/json' \
   -d @template.json \
   'https://{USER}.carto.com/api/v1/map/named?api_key=/{API_KEY}'

4_instantiate_namedmap.sh

curl -X POST \
  -H 'Content-Type: application/json' \
  -d @params.json \
  'https://{USER}.carto.com/api/v1/map/named/routingtest'

5_text.md

Endpoint:

https://{USER}.carto.com/api/v1/map/named/routingtest/750/401.png?config={"postcode1":"'{postcode_1}'","postcode2":"'{postcode_2}'"}

Real Example

http://solutions.carto.com/api/v1/map/static/named/teradata_usa/750/401.png?config={"postcode1":"'11206'","postcode2":"'94105'"}