Origin-Destination Counts

README.md

This analysis will allow a user to aggregate deliveries between origin and destination pairs. After applying, style and filter a map based on trips between origins and destinations.

Requirements:

• A dataset (without geometries) of deliveries between origins and destinations. The deliveries schema should include at minimum:

  • origin id (numeric or string)
  • destination id (numeric or string)
  • at least one aggregation column such as cost or pallets (numeric)

• A dataset (with geometries) of locations. The locations schema should include at minimum:

  • the_geom (point geometry)
  • id (numeric or string)

• Custom SQL nodes must be enabled and one custom SQL functions is needed: DEP_EXT_ODCounts

For the following example, use this .carto file. It contains the required datasets and analysis & widgets setup. large_fake_bxb_locations map 1 (on 2017-11-27 at 17.36.04).carto.zip

Steps for creating map and analysis with (fake) sample datasets:

1. From the CARTO dashboard, select "New Map"

2. Upload the sample BXB .carto file (which contains the needed sample datasets for deliveries and locations) At this point, the map should not render. This is because the custom SQL function does not exist yet.

3. To create the custom SQL function, open any dataset SQL pane.

4. Copy-paste the DEP_EXT_ODCounts CREATE OR REPLACE SQL code and apply

5. Returning to the map, lines should connect the fake sample points in the rectangle, representing deliveries

6. Notice the input parameters of the SQL function node on the deliveries layer:

• Location_table: large_fake_bxb_locations (the locations table with point geometries)
• ORIGIN_ID: origin (the column with origin IDs in the delivery table)
• DEST_ID: destinations (the column with destination IDs in the delivery table)
• AGG_COLS: pallets,pallets (a comma separated list of columns to aggregate by, such as cost or pallets)
• AGG_TYPES: sum,count (a comma separated list of aggregation methods. Same length as AGG_COLS)
• DUMMY: 3

This analysis returns two aggregations: the sum and the count of pallets. The dummy variable is to override any cached results and has no effect on the output.

7. Explore the deliveries dataset using widgets.

8. Add styling, more widgets, pop-ups, change name and titles, and add legends as desired.

DEP_EXT_ODCounts.sql

CREATE OR REPLACE FUNCTION DEP_EXT_ODCounts(
         operation text,
         table_name text,
         source_query text,
         source_columns text[], -- to be filtered
         location_table text,
         origin_id text,
         dest_id text,
         agg_cols text,
         agg_types text,
         dummy text
     )
     RETURNS VOID AS $$
         DECLARE
             selected_columns text;
             agg_create text;
             agg_compute text;
             agg_cols_array text[];
             agg_types_array text[];
             sql text;

         BEGIN

             selected_columns := array_to_string(source_columns, ',');

             agg_cols_array := regexp_split_to_array(agg_cols,',');
             agg_types_array := regexp_split_to_array(agg_types,',');

             agg_create= '';
             agg_compute='';

             FOR i IN 1 .. array_upper(agg_cols_array, 1)
             LOOP
            agg_create = agg_create || ', 1::NUMERIC as ' || agg_cols_array[i] || '_' ||  agg_types_array[i] ;
            agg_compute = agg_compute || ', ' || agg_types_array[i] || '('|| agg_cols_array[i] || ' )::NUMERIC as '|| agg_cols_array[i] || '_' ||  agg_types_array[i];
            END LOOP;


             IF operation = 'create' THEN

                 EXECUTE 'DROP TABLE IF EXISTS ' || table_name;

                  EXECUTE 'DROP TABLE IF EXISTS ' || table_name;

                  EXECUTE
                   'CREATE TABLE '
                   || table_name
                   || ' AS SELECT 1::NUMERIC cartodb_id, NULL::geometry as the_geom, NULL::geometry as the_geom_webmercator, 1::NUMERIC origin, 1::NUMERIC dest, NULL::TEXT origin_country, NULL::TEXT dest_country ' || agg_create
                   || ' FROM (' || source_query || ') _source'
                   || ' LIMIT 0' ;


             ELSEIF operation = 'populate' THEN

                 EXECUTE format( $QUERY$
                   with result as (
                       SELECT row_number() OVER () As cartodb_id, ST_MAKELINE(s.the_geom, d.the_geom) as the_geom,
                              ST_Transform(ST_MAKELINE(s.the_geom, d.the_geom), 3857) as the_geom_webmercator,
                              s.id origin_id, d.id dest_id,
                              s.country origin_country, d.country dest_country %s
                       FROM %s  s,  %s  d , (%s) data_table
                       WHERE s.id = data_table.%s AND d.id = data_table.%s
                       group by ST_MAKELINE(s.the_geom, d.the_geom), s.id + d.id, s.id, d.id, s.country, d.country
                   )
                   INSERT INTO %I select * from result
                   $QUERY$,
                   agg_compute,
                   location_table,
                   location_table,
                   source_query,
                   origin_id,
                   dest_id,
                   table_name
                 );

             END IF;
         END;
 $$ LANGUAGE plpgsql;