Schema: parcel

Auto-generated by npm run gendocs. Do not edit by hand — changes will be overwritten. To update, run npm run gendocs against a database with the latest migrations applied.

Counts: 18 tables · 17 views · 9 materialized views · 37 functions · 3 policies

---

Tables (18)

address_city

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.address_city_id_seq'::regclass)
created_at	timestamp with time zone	no	now()
country_id	integer	yes
name	text	no
coordinates	gis.geometry(Point,4326)	yes

Primary key: PRIMARY KEY (id)

Unique:

• address_city_country_id_name_key: UNIQUE (country_id, name)

Foreign keys:

• address_city_country_id_fkey: FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE SET NULL

Checks:

• city_not_empty: CHECK (name <> ''::text)

address_locality

Address localities (formerly regions)

RLS enabled.

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.address_locality_id_seq'::regclass)
name	text	no
country_id	integer	no	169
created_at	timestamp without time zone	yes	now()
inhabitants	bigint	yes	0
municipality_type	text	yes

Primary key: PRIMARY KEY (id)

Unique:

• address_locality_unique_name_country: UNIQUE (name, country_id)

Policies:

• Anyone can read address localities — SELECT, PERMISSIVE, roles: authenticated
  • USING: true

address_region

Länen i Sverige

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.address_region_id_seq'::regclass)
name	text	no
country_id	integer	no	169
inhabitants	bigint	yes	0
created_at	timestamp without time zone	yes	now()

Primary key: PRIMARY KEY (id)

address_street

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.address_street_id_seq'::regclass)
created_at	timestamp with time zone	no	now()
name	text	no
zipcode_id	integer	yes		street.zipcode_id will soon be removed, use address_v2.zipcode_id instead
city_id	integer	yes		NEW: Street now belongs directly to city (not zipcode)

Primary key: PRIMARY KEY (id)

Unique:

• address_street_zipcode_id_name_key: UNIQUE (zipcode_id, name)

Foreign keys:

• address_street_zipcode_id_fkey: FOREIGN KEY (zipcode_id) REFERENCES parcel.address_zipcode(id) ON DELETE SET NULL
• fk_address_street_city: FOREIGN KEY (city_id) REFERENCES parcel.address_city(id) ON DELETE CASCADE

Checks:

• street_not_empty: CHECK (name <> ''::text)

address_urban_city

Address urban cities

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.address_urban_city_id_seq'::regclass)
name	text	no
created_at	timestamp without time zone	yes	now()
inhabitants	bigint	yes	0

Primary key: PRIMARY KEY (id)

address_v2

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.address_v2_id_seq'::regclass)
created_at	timestamp with time zone	no	now()
street_id	integer	no
street_number	text	no
door	text	no
coordinates	gis.geometry(Point,4326)	yes
households_count	integer	yes
emissions_class_regional_lastmile	parcel.emissions_type	yes
emissions_class_lastmile	parcel.emissions_type	yes
zipcode_id	integer	no		NEW: Address now has direct relationship to zipcode, in addition to street
address_locality_id	integer	yes		NEW: Direct relationship from address to locality
address_type	character varying(2)	yes
urban_city_id	integer	yes
address_region_id	integer	yes

Primary key: PRIMARY KEY (id)

Unique:

• unique_address_v2: UNIQUE (zipcode_id, street_id, street_number, door)

Foreign keys:

• address_v2_address_region_id_fkey: FOREIGN KEY (address_region_id) REFERENCES parcel.address_region(id)
• address_v2_street_id_fkey: FOREIGN KEY (street_id) REFERENCES parcel.address_street(id) ON DELETE CASCADE
• address_v2_urban_city_id_fkey: FOREIGN KEY (urban_city_id) REFERENCES parcel.address_urban_city(id)
• fk_address_v2_locality: FOREIGN KEY (address_locality_id) REFERENCES parcel.address_locality(id) ON DELETE SET NULL
• fk_address_v2_zipcode: FOREIGN KEY (zipcode_id) REFERENCES parcel.address_zipcode(id) ON DELETE CASCADE

Checks:

• address_v2_households_count_check: CHECK (households_count >= 0)

address_zipcode

Column	Type	Null	Default	Comment
id	integer	no
zipcode	text	no
created_at	timestamp with time zone	no	now()
city_id	integer	yes
country_id	integer	yes	169

Primary key: PRIMARY KEY (id)

Unique:

• unique_zipcode: UNIQUE (country_id, zipcode)

Foreign keys:

• address_zipcode_city_id_fkey: FOREIGN KEY (city_id) REFERENCES parcel.address_city(id) ON DELETE SET NULL
• address_zipcode_country_id_fkey: FOREIGN KEY (country_id) REFERENCES country(id) ON DELETE SET NULL

Checks:

• zipcode_not_empty: CHECK (zipcode <> ''::text)

emission_summary

Column	Type	Null	Default	Comment
id	bigint	no	nextval('parcel.emission_summary_id_seq'::regclass)
created_at	timestamp without time zone	yes	now()
from_zipcode_id	bigint	no		Will possibly be replace by from_node_id in the future
to_district_node_id	bigint	no
distance_m	numeric	yes	0
emissions_class	parcel.emissions_type	yes
emissions_gco2ep1kg	numeric	yes
emissions_comparison_gco2ep1kg	numeric	yes
energy_kwhp1kg	numeric	yes
fuel_consumption_lp1kg	numeric	yes
transporter_account_id	uuid	no
is_route_calculated	boolean	yes	false
last_attempted_at	timestamp with time zone	yes
edge_ids	integer[]	yes	'{}'::integer[]
from_node_id	integer	yes		Start moving to a from-node/to-node architecture
emissions_from_logistics_hubs_gco2ep1kg	numeric	yes
emissions_ttw_gco2ep1kg	numeric	yes	0
emissions_wtt_gco2ep1kg	numeric	yes	0

Primary key: PRIMARY KEY (id)

Unique:

• no_duplicate_rows: UNIQUE (transporter_account_id, from_zipcode_id, to_district_node_id)

Foreign keys:

• emission_summary_from_node_id_fkey: FOREIGN KEY (from_node_id) REFERENCES parcel.transporter_node_v2(id) ON DELETE SET NULL
• emission_summary_from_zipcode_id_fkey: FOREIGN KEY (from_zipcode_id) REFERENCES parcel.address_zipcode(id) ON DELETE CASCADE
• emission_summary_to_district_node_id_fkey: FOREIGN KEY (to_district_node_id) REFERENCES parcel.transporter_node_v2(id) ON DELETE CASCADE
• emission_summary_transporter_account_id_fkey: FOREIGN KEY (transporter_account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE

municipality_type

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.municipality_type_id_seq'::regclass)
code	text	no
name	text	no
description	text	no

Primary key: PRIMARY KEY (id)

orders_daily_summary

RLS enabled.

Column	Type	Null	Default	Comment
id	bigint	no	nextval('parcel.emission_summary_orders_id_seq'::regclass)
created_at	timestamp without time zone	yes	now()
delivery_date	date	no
customer_id	uuid	yes
transporter_id	uuid	yes
product_name	text	yes
weight	numeric	yes
volume	numeric	yes
distance_km	numeric	yes
energy_kwh	numeric	yes
fuel_consumption_l	numeric	yes
emissions_gco2e	numeric	yes
emissions_comparison_gco2e	numeric	yes
emissions_class	parcel.emissions_type	yes
orders_count	integer	yes	1
calc_percent	numeric	yes
emissions_from_logistics_hubs_gco2e	numeric	yes
emissions_ttw_gco2e	numeric	yes	0
emissions_wtt_gco2e	numeric	yes	0

Primary key: PRIMARY KEY (id)

Foreign keys:

• emission_summary_orders_customer_id_fkey: FOREIGN KEY (customer_id) REFERENCES basejump.accounts(id) ON DELETE SET NULL
• emission_summary_orders_transporter_id_fkey: FOREIGN KEY (transporter_id) REFERENCES basejump.accounts(id) ON DELETE SET NULL

Policies:

• Customer accounts and transporter accounts can read order summa — SELECT, PERMISSIVE, roles: authenticated
  • USING: ((customer_id IN ( SELECT basejump.get_accounts_with_role() AS get_accounts_with_role)) OR (transporter_id IN ( SELECT basejump.get_accounts_with_role() AS get_accounts_with_role)) OR (transporter_id IN ( SELECT account_account.other_account_id FROM account_account WHERE (account_account.account_id IN ( SELECT basejump.get_accounts_with_role() AS get_accounts_with_role)))))

transporter_edge_v2

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.transporter_edge_v2_id_seq'::regclass)
created_at	timestamp with time zone	no	now()
node_a	integer	no
node_b	integer	no
transporter_account_id	uuid	no
transporter_vehicle_id	integer	no
distance_m	integer	no	0
period	date	no	date_trunc('month'::text, now())
load_factor_100	integer	no	50
empty_running_100	integer	no	15
distance_estimated	boolean	no	false
transporter_vehicle_string	text	yes

Primary key: PRIMARY KEY (id)

Unique:

• transporter_edge_v2_transporter_account_id_node_a_node_b_key: UNIQUE (transporter_account_id, node_a, node_b)

Foreign keys:

• transporter_edge_v2_node_a_fkey: FOREIGN KEY (node_a) REFERENCES parcel.transporter_node_v2(id) ON DELETE CASCADE
• transporter_edge_v2_node_b_fkey: FOREIGN KEY (node_b) REFERENCES parcel.transporter_node_v2(id) ON DELETE CASCADE
• transporter_edge_v2_transporter_account_id_fkey: FOREIGN KEY (transporter_account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE
• transporter_edge_v2_transporter_vehicle_id_fkey: FOREIGN KEY (transporter_vehicle_id) REFERENCES parcel.transporter_vehicle_v2(id) ON DELETE CASCADE

Checks:

• node_a_not_equal_node_b: CHECK (node_a <> node_b)
• transporter_edge_v2_distance_m_check: CHECK (distance_m >= 0)

transporter_node_address

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.transporter_node_address_id_seq'::regclass)
created_at	timestamp with time zone	no	now()
transporter_node_id	integer	no
address_id	integer	no
period	date	yes	date_trunc('month'::text, now())

Primary key: PRIMARY KEY (id)

Unique:

• transporter_node_address_transporter_node_id_address_id_key: UNIQUE (transporter_node_id, address_id)

Foreign keys:

• transporter_node_address_address_id_fkey: FOREIGN KEY (address_id) REFERENCES parcel.address_v2(id) ON DELETE CASCADE
• transporter_node_address_transporter_node_id_fkey: FOREIGN KEY (transporter_node_id) REFERENCES parcel.transporter_node_v2(id) ON DELETE CASCADE

transporter_node_pickup

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.transporter_node_pickup_id_seq'::regclass)
created_at	timestamp with time zone	no	now()
transporter_node_id	integer	no
customer_account_id	uuid	yes

Primary key: PRIMARY KEY (id)

Unique:

• transporter_node_pickup_transporter_node_id_customer_accoun_key: UNIQUE (transporter_node_id, customer_account_id)

Foreign keys:

• transporter_node_pickup_customer_account_id_fkey: FOREIGN KEY (customer_account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE
• transporter_node_pickup_transporter_node_id_fkey: FOREIGN KEY (transporter_node_id) REFERENCES parcel.transporter_node_v2(id) ON DELETE CASCADE

transporter_node_v2

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.transporter_node_v2_id_seq'::regclass)
created_at	timestamp with time zone	no	now()
transporter_account_id	uuid	no
address_id	integer	no
name	text	yes
type	text	yes

Primary key: PRIMARY KEY (id)

Unique:

• transporter_node_v2_transporter_account_id_address_id_key: UNIQUE (transporter_account_id, address_id)

Foreign keys:

• transporter_node_v2_address_id_fkey: FOREIGN KEY (address_id) REFERENCES parcel.address_v2(id) ON DELETE CASCADE
• transporter_node_v2_transporter_account_id_fkey: FOREIGN KEY (transporter_account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE

Checks:

• name_not_empty: CHECK (name <> ''::text)

transporter_vehicle_type_letter

RLS enabled.

Column	Type	Null	Default	Comment
id	uuid	no	uuid_generate_v4()
transporter_account_id	uuid	no
vehicle_type_id	integer	no
vehicle_type_letter	text	no
created_at	timestamp with time zone	no	now()

Primary key: PRIMARY KEY (id)

Unique:

• transporter_vehicle_type_lett_transporter_account_id_vehicl_key: UNIQUE (transporter_account_id, vehicle_type_id)

Foreign keys:

• transporter_vehicle_type_letter_transporter_account_id_fkey: FOREIGN KEY (transporter_account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE
• transporter_vehicle_type_letter_vehicle_type_id_fkey: FOREIGN KEY (vehicle_type_id) REFERENCES parcel.vehicle_type(id) ON DELETE CASCADE

Policies:

• All logged in users can access and modify — ALL, PERMISSIVE, roles: authenticated
  • USING: true

transporter_vehicle_v2

Column	Type	Null	Default	Comment
id	integer	no	nextval('parcel.transporter_vehicle_v2_id_seq'::regclass)
transporter_account_id	uuid	yes
regnr	text	yes
vehicle_type_id	integer	no
created_at	timestamp with time zone	no	now()
max_capacity_kg	integer	yes

Primary key: PRIMARY KEY (id)

Unique:

• transporter_vehicle_v2_regnr_key: UNIQUE (regnr)

Foreign keys:

• transporter_vehicle_v2_transporter_account_id_fkey: FOREIGN KEY (transporter_account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE
• transporter_vehicle_v2_vehicle_type_id_fkey: FOREIGN KEY (vehicle_type_id) REFERENCES parcel.vehicle_type(id) ON DELETE SET NULL

vehicle_fuel_type

Column	Type	Null	Default	Comment
id	integer	no
name	text	yes
emissions_class	text	yes
year	text	yes
wtt_emission_factor_1000	integer	yes
ttw_emission_factor_1000	integer	yes
wtw_emission_factor_1000	integer	yes
description	text	yes
created_at	timestamp with time zone	no	now()

Primary key: PRIMARY KEY (id)

Unique:

• unique_name_class_year: UNIQUE (name, emissions_class, year)

vehicle_type

Column	Type	Null	Default	Comment
identifier	text	no
max_capacity_kg	integer	no	0
fuel_consumption_pkm_1000	integer	no	0
created_at	timestamp with time zone	no	now()
id	integer	no	nextval('parcel.vehicle_type_id_seq'::regclass)
fuel_type_id	integer	yes
type	text	yes
sub_type	text	yes
brand	text	yes
model	text	yes
energy_kwhpkm_1000	integer	yes

Primary key: PRIMARY KEY (id)

Unique:

• vehicle_type_identifier_key: UNIQUE (identifier)

Foreign keys:

• vehicle_type_fuel_type_id_fkey: FOREIGN KEY (fuel_type_id) REFERENCES parcel.vehicle_fuel_type(id) ON DELETE SET NULL

Views (26)

viewm_full_routes

materialized view

Column	Type
from_zipcode_id	bigint
to_district_node_id	bigint
to_city	text
customer	text
distributor	text
transporters	text
legs	integer
emissions_gco2ep1kg	numeric
emissions_class	parcel.emissions_type

SELECT es.from_zipcode_id,
    es.to_district_node_id,
    o.to_city,
    customer.name AS customer,
    distributor.name AS distributor,
    string_agg(DISTINCT o.transporter::text, ', '::text) AS transporters,
    max(array_length(es.edge_ids, 1)) AS legs,
    max(es.emissions_gco2ep1kg) AS emissions_gco2ep1kg,
    es.emissions_class
   FROM "order" o
     JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
     JOIN basejump.accounts customer ON customer.id = o.customer_account_id
     JOIN basejump.accounts distributor ON distributor.id = o.transporter_account_id
  WHERE o.delivery_date::date > '2025-07-01'::date
  GROUP BY es.from_zipcode_id, es.to_district_node_id, o.to_city, es.emissions_class, customer.name, distributor.name;

viewm_geo_network

materialized view

Column	Type
locality	text
address_type	character varying(2)
urban_city	text
region	text
household_count	bigint
locality_inhabitants	bigint
city_inhabitants	bigint
region_inhabitants	bigint
emissions_class_lastmile	parcel.emissions_type
transporter	text
municipality_type	text

WITH address_households AS (
         SELECT a.id AS address_id,
            al.name AS locality,
            a.address_type,
            uc.name AS urban_city,
            ar.name AS region,
            a.emissions_class_lastmile,
            COALESCE(a.households_count, 0) AS household_count,
            al.inhabitants AS locality_inhabitants,
            uc.inhabitants AS city_inhabitants,
            ar.inhabitants AS region_inhabitants,
            (am.code || '-'::text) || am.name AS municipality_type
           FROM parcel.address_v2 a
             JOIN parcel.address_locality al ON al.id = a.address_locality_id
             JOIN parcel.address_urban_city uc ON uc.id = a.urban_city_id
             JOIN parcel.address_region ar ON ar.id = a.address_region_id
             JOIN parcel.municipality_type am ON am.code = al.municipality_type
          WHERE a.emissions_class_lastmile IS NOT NULL
        ), unique_tna AS (
         SELECT DISTINCT ON (tna.address_id) tna.address_id,
            tna.transporter_node_id,
            tna.period
           FROM parcel.transporter_node_address tna
          WHERE tna.period >= ((( SELECT settings.value
                   FROM settings
                  WHERE settings.key = 'period_minimum'::text
                 LIMIT 1))::date)
          ORDER BY tna.address_id, tna.period DESC
        )
 SELECT ah.locality,
    ah.address_type,
    ah.urban_city,
    ah.region,
    sum(ah.household_count) AS household_count,
    ah.locality_inhabitants,
    ah.city_inhabitants,
    ah.region_inhabitants,
    ah.emissions_class_lastmile,
    min(acc.name) AS transporter,
    ah.municipality_type
   FROM address_households ah
     JOIN unique_tna utna ON utna.address_id = ah.address_id
     JOIN parcel.transporter_node_v2 tn ON tn.id = utna.transporter_node_id
     JOIN basejump.accounts acc ON acc.id = tn.transporter_account_id
  GROUP BY ah.locality, ah.address_type, ah.urban_city, ah.region, ah.emissions_class_lastmile, ah.locality_inhabitants, ah.city_inhabitants, ah.region_inhabitants, ah.municipality_type;

viewm_orders_data_geography

materialized view

Column	Type
municipality	text
region	text
product	character varying
area_type	text
urban_city	text
emissions_class	parcel.emissions_type
address_type	character varying(2)
transporter	text
emissions	double precision
weight	real
order_count	bigint
delivery_date	date
customer	text
with_emission_count	bigint

SELECT al.name AS municipality,
    ar.name AS region,
    o.product,
    (al.municipality_type || ' - '::text) || mt.name AS area_type,
    uc.name AS urban_city,
    es.emissions_class,
    a.address_type,
    acc.name AS transporter,
    sum(COALESCE(es.emissions_gco2ep1kg::double precision * o.weight, 0::double precision)) AS emissions,
    sum(o.weight) AS weight,
    count(o.id) AS order_count,
    o.delivery_date::date AS delivery_date,
    cust.name AS customer,
    count(o.id) FILTER (WHERE es.emissions_class IS NOT NULL) AS with_emission_count
   FROM "order" o
     LEFT JOIN parcel.address_v2 a ON a.id = o.to_address_id
     LEFT JOIN basejump.accounts cust ON cust.id = o.customer_account_id
     LEFT JOIN parcel.address_locality al ON al.id = a.address_locality_id
     LEFT JOIN parcel.address_region ar ON ar.id = a.address_region_id
     LEFT JOIN parcel.address_urban_city uc ON uc.id = a.urban_city_id
     LEFT JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
     JOIN basejump.accounts acc ON acc.id = es.transporter_account_id
     JOIN parcel.municipality_type mt ON mt.code = al.municipality_type
  WHERE o.delivery_date::date > '2025-04-01'::date AND acc.name <> 'ICA'::text AND acc.name <> 'PostNord TPL'::text AND acc.name <> 'Bring'::text
  GROUP BY al.name, ar.name, al.municipality_type, mt.name, uc.name, a.address_type, es.emissions_class, acc.name, (o.delivery_date::date), cust.name, o.product;

viewm_orders_list

materialized view

Column	Type
id	bigint
delivery_date	date
tracking_id	character varying
transporter	text
transporter_account_id	uuid
customer	text
customer_account_id	uuid
product	character varying
address	text
emissions_gco2e	numeric
is_route_calculated	boolean
emissions_class	text
vehicle_last_mile	text
region	text
locality	text
municipality_type	text
urban_city	text

SELECT o.id,
    o.delivery_date::date AS delivery_date,
    o.tracking_id,
    transporter.name AS transporter,
    transporter.id AS transporter_account_id,
    customer.name AS customer,
    customer.id AS customer_account_id,
    o.product,
    initcap((((o.to_street || ' '::text) || COALESCE(o.to_door, ''::text)) || ', '::text) || COALESCE(o.to_city, ''::text)) AS address,
    round(es.emissions_gco2ep1kg * o.weight::numeric, 3) AS emissions_gco2e,
    es.is_route_calculated,
    initcap(replace(es.emissions_class::text, '_'::text, '-'::text)) AS emissions_class,
    initcap(replace(((te.vehicle_type || ' ('::text) || te.fuel_type) || ')'::text, '_'::text, ' '::text)) AS vehicle_last_mile,
    address_region.name AS region,
    address_locality.name AS locality,
    (municipality_type.code || ' - '::text) || municipality_type.name AS municipality_type,
    address_urban_city.name AS urban_city
   FROM "order" o
     LEFT JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
     LEFT JOIN basejump.accounts transporter ON transporter.id = o.transporter_account_id
     LEFT JOIN basejump.accounts customer ON customer.id = o.customer_account_id
     LEFT JOIN parcel.address_v2 address_v2 ON address_v2.id = o.to_address_id
     LEFT JOIN parcel.address_locality address_locality ON address_locality.id = address_v2.address_locality_id
     LEFT JOIN parcel.municipality_type municipality_type ON address_locality.municipality_type = municipality_type.code
     LEFT JOIN parcel.address_region address_region ON address_region.id = address_v2.address_region_id
     LEFT JOIN parcel.address_urban_city address_urban_city ON address_urban_city.id = address_v2.urban_city_id
     JOIN LATERAL ( SELECT e.edge_id
           FROM unnest(es.edge_ids) WITH ORDINALITY e(edge_id, ord)
          ORDER BY e.ord DESC
         LIMIT 1) last_edge ON true
     JOIN parcel.viewm_transporter_routes te ON te.edge_id = last_edge.edge_id;

viewm_product_names

materialized view

Column	Type
product	character varying

SELECT DISTINCT "order".product
   FROM "order";

viewm_transporter_nodes

materialized view

Column	Type
node_id	integer
transporter_account_id	uuid
transporter_name	text
node_name	text
node_type	text
address_id	integer
coordinates	text
street_name	text
street_number	text
door	text
zipcode	text
zipcode_id	integer
city_name	text
edges_count	bigint

SELECT tn.id AS node_id,
    t.id AS transporter_account_id,
    t.name AS transporter_name,
    tn.name AS node_name,
    tn.type AS node_type,
    tn.address_id,
        CASE
            WHEN a.coordinates IS NOT NULL THEN concat(gis.st_x(gis.st_transform(a.coordinates, 4326)), ',', gis.st_y(gis.st_transform(a.coordinates, 4326)))
            WHEN c.coordinates IS NOT NULL THEN concat(gis.st_x(gis.st_transform(c.coordinates, 4326)), ',', gis.st_y(gis.st_transform(c.coordinates, 4326)))
            ELSE NULL::text
        END AS coordinates,
    s.name AS street_name,
    a.street_number,
    a.door,
    z.zipcode,
    z.id AS zipcode_id,
    c.name AS city_name,
    ( SELECT count(*) AS count
           FROM parcel.transporter_edge_v2 e
          WHERE e.node_a = tn.id OR e.node_b = tn.id) AS edges_count
   FROM parcel.transporter_node_v2 tn
     LEFT JOIN basejump.accounts t ON tn.transporter_account_id = t.id
     LEFT JOIN parcel.address_v2 a ON tn.address_id = a.id
     LEFT JOIN parcel.address_street s ON a.street_id = s.id
     LEFT JOIN parcel.address_zipcode z ON a.zipcode_id = z.id
     LEFT JOIN parcel.address_city c ON s.city_id = c.id
  ORDER BY t.name, z.zipcode;

viewm_transporter_routes

materialized view

Column	Type
edge_id	integer
period	date
transporter_account_id	uuid
transporter_name	text
from_node_id	integer
from_node_name	text
from_node_type	text
from_node_address_id	integer
from_coordinates	text
from_street	text
from_zipcode	text
from_city	text
to_node_id	integer
to_node_name	text
to_node_type	text
to_node_address_id	integer
to_coordinates	text
to_street	text
to_zipcode	text
to_city	text
route_type	text
vehicle_id	integer
vehicle_type	text
vehicle_sub_type	text
fuel_type	text
emissions_class	text
load_capacity_metrictons	numeric
load_factor	integer
empty_running	integer
capacity_utilisation	integer
fuel_consumption_lpkm	numeric
energy_kwhpkm	numeric
fuel_consumption_lptkm	numeric
energy_kwhptkm	numeric
wtt_emission_factor_gco2e	numeric
ttw_emission_factor_gco2e	numeric
emission_factor_gco2e	numeric
emissions_gco2eptkm	numeric
distance_m	integer
distance_km	numeric
tonkm_p1kg	numeric
fuel_consumption_lp1kg	numeric
energy_kwhp1kg	numeric
emissions_gco2ep1kg	numeric
emissions_wtt_gco2ep1kg	numeric
emissions_ttw_gco2ep1kg	numeric
emissions_comparison_gco2ep1kg	numeric
emissions_reduction_gco2ep1kg	numeric
emissions_reduction_percent	numeric

WITH vehicle_data AS (
         SELECT tv.id AS vehicle_id,
            tv.regnr AS vehicle_regnr,
            vt.type AS vehicle_type,
            vt.sub_type AS vehicle_sub_type,
            vf.name AS vehicle_fuel_type,
            vf.emissions_class,
            vt.fuel_consumption_pkm_1000,
            vf.wtw_emission_factor_1000,
            vt.energy_kwhpkm_1000,
            COALESCE(tv.max_capacity_kg, vt.max_capacity_kg) AS max_capacity_kg,
            vf.wtt_emission_factor_1000,
            vf.ttw_emission_factor_1000
           FROM parcel.transporter_vehicle_v2 tv
             LEFT JOIN parcel.vehicle_type vt ON tv.vehicle_type_id = vt.id
             LEFT JOIN parcel.vehicle_fuel_type vf ON vt.fuel_type_id = vf.id
        )
 SELECT te.id AS edge_id,
    te.period,
    t.id AS transporter_account_id,
    initcap(t.name) AS transporter_name,
    te.node_a AS from_node_id,
    initcap(na.name) AS from_node_name,
    na.type AS from_node_type,
    na.address_id AS from_node_address_id,
        CASE
            WHEN aa.coordinates IS NOT NULL THEN concat(gis.st_x(gis.st_transform(aa.coordinates, 4326)), ',', gis.st_y(gis.st_transform(aa.coordinates, 4326)))
            WHEN ca.coordinates IS NOT NULL THEN concat(gis.st_x(gis.st_transform(ca.coordinates, 4326)), ',', gis.st_y(gis.st_transform(ca.coordinates, 4326)))
            ELSE NULL::text
        END AS from_coordinates,
    initcap(sta.name) AS from_street,
    za.zipcode AS from_zipcode,
    initcap(ca.name) AS from_city,
    te.node_b AS to_node_id,
    initcap(nb.name) AS to_node_name,
    nb.type AS to_node_type,
    nb.address_id AS to_node_address_id,
        CASE
            WHEN ab.coordinates IS NOT NULL THEN concat(gis.st_x(gis.st_transform(ab.coordinates, 4326)), ',', gis.st_y(gis.st_transform(ab.coordinates, 4326)))
            WHEN cb.coordinates IS NOT NULL THEN concat(gis.st_x(gis.st_transform(cb.coordinates, 4326)), ',', gis.st_y(gis.st_transform(cb.coordinates, 4326)))
            ELSE NULL::text
        END AS to_coordinates,
    initcap(stb.name) AS to_street,
    zb.zipcode AS to_zipcode,
    initcap(cb.name) AS to_city,
        CASE
            WHEN na.type = 'pickup'::text OR nb.type = 'pickup'::text THEN 'pickup'::text
            WHEN na.type = 'district'::text OR nb.type = 'district'::text THEN 'last_mile'::text
            WHEN na.type = 't3'::text OR nb.type = 't3'::text THEN 'regional'::text
            ELSE 'line_haul'::text
        END AS route_type,
    vd.vehicle_id,
    vd.vehicle_type,
    vd.vehicle_sub_type,
    vd.vehicle_fuel_type AS fuel_type,
    vd.emissions_class,
    round(vd.max_capacity_kg::numeric / 1000::numeric, 2) AS load_capacity_metrictons,
    te.load_factor_100 AS load_factor,
    te.empty_running_100 AS empty_running,
    100 * te.load_factor_100 / (100 + te.empty_running_100) AS capacity_utilisation,
    vd.fuel_consumption_pkm_1000::numeric / 1000::numeric AS fuel_consumption_lpkm,
    vd.energy_kwhpkm_1000::numeric / 1000::numeric AS energy_kwhpkm,
    round(vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * (100::numeric * te.load_factor_100::numeric / (100::numeric + te.empty_running_100::numeric) / 100::numeric)), 4) AS fuel_consumption_lptkm,
    round(vd.energy_kwhpkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)), 4) AS energy_kwhptkm,
    vd.wtt_emission_factor_1000::numeric AS wtt_emission_factor_gco2e,
    vd.ttw_emission_factor_1000::numeric AS ttw_emission_factor_gco2e,
    vd.wtw_emission_factor_1000::numeric AS emission_factor_gco2e,
    round(
        CASE
            WHEN vd.vehicle_fuel_type = ANY (ARRAY['electricity'::text, 'human'::text]) THEN vd.energy_kwhpkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric
            ELSE vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric
        END, 4) AS emissions_gco2eptkm,
    te.distance_m,
    round(te.distance_m::numeric / 1000::numeric, 1) AS distance_km,
    round(te.distance_m::numeric / 1000::numeric * 0.001, 7) AS tonkm_p1kg,
    round(vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * (100::numeric * te.load_factor_100::numeric / (100::numeric + te.empty_running_100::numeric) / 100::numeric)) / 1000::numeric * (te.distance_m::numeric / 1000::numeric), 4) AS fuel_consumption_lp1kg,
    round(vd.energy_kwhpkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) / 1000::numeric * (te.distance_m::numeric / 1000::numeric), 4) AS energy_kwhp1kg,
    round(
        CASE
            WHEN vd.vehicle_fuel_type = ANY (ARRAY['electricity'::text, 'human'::text]) THEN vd.energy_kwhpkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric / 1000::numeric * (te.distance_m::numeric / 1000::numeric)
            ELSE vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric / 1000::numeric * (te.distance_m::numeric / 1000::numeric)
        END, 4) AS emissions_gco2ep1kg,
    round(
        CASE
            WHEN vd.vehicle_fuel_type = ANY (ARRAY['electricity'::text, 'human'::text]) THEN 0::numeric
            ELSE vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtt_emission_factor_1000::numeric / 1000::numeric * (te.distance_m::numeric / 1000::numeric)
        END, 4) AS emissions_wtt_gco2ep1kg,
    round(
        CASE
            WHEN vd.vehicle_fuel_type = ANY (ARRAY['electricity'::text, 'human'::text]) THEN vd.energy_kwhpkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.ttw_emission_factor_1000::numeric / 1000::numeric * (te.distance_m::numeric / 1000::numeric)
            ELSE vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.ttw_emission_factor_1000::numeric / 1000::numeric * (te.distance_m::numeric / 1000::numeric)
        END, 4) AS emissions_ttw_gco2ep1kg,
    round(0.285 / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * 2710.0 / 1000::numeric * (te.distance_m::numeric / 1000::numeric), 4) AS emissions_comparison_gco2ep1kg,
    round(
        CASE
            WHEN vd.vehicle_fuel_type = ANY (ARRAY['electricity'::text, 'human'::text]) THEN (0.285 / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * 2710.0 - vd.energy_kwhpkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric) / 1000::numeric * (te.distance_m::numeric / 1000::numeric)
            ELSE (0.285 / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * 2710.0 - vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric) / 1000::numeric * (te.distance_m::numeric / 1000::numeric)
        END, 4) AS emissions_reduction_gco2ep1kg,
        CASE
            WHEN te.distance_m = 0 THEN 0::numeric
            ELSE round(
            CASE
                WHEN vd.vehicle_fuel_type = ANY (ARRAY['electricity'::text, 'human'::text]) THEN 0.285 / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * 2710.0 / 1000000::numeric * (te.distance_m::numeric / 1000::numeric) - vd.energy_kwhpkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric / 1000000::numeric * (te.distance_m::numeric / 1000::numeric)
                ELSE 0.285 / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * 2710.0 / 1000000::numeric * (te.distance_m::numeric / 1000::numeric) - vd.fuel_consumption_pkm_1000::numeric / 1000::numeric / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * vd.wtw_emission_factor_1000::numeric / 1000000::numeric * (te.distance_m::numeric / 1000::numeric)
            END / (0.285 / (vd.max_capacity_kg::numeric / 1000::numeric * ((100 * te.load_factor_100 / (100 + te.empty_running_100))::numeric / 100::numeric)) * 2710.0 / 1000000::numeric * (te.distance_m::numeric / 1000::numeric)) * 100::numeric, 2)
        END AS emissions_reduction_percent
   FROM parcel.transporter_edge_v2 te
     LEFT JOIN basejump.accounts t ON te.transporter_account_id = t.id
     LEFT JOIN parcel.transporter_node_v2 na ON te.node_a = na.id
     LEFT JOIN parcel.transporter_node_v2 nb ON te.node_b = nb.id
     LEFT JOIN parcel.address_v2 aa ON na.address_id = aa.id
     LEFT JOIN parcel.address_v2 ab ON nb.address_id = ab.id
     LEFT JOIN parcel.address_street sta ON aa.street_id = sta.id
     LEFT JOIN parcel.address_street stb ON ab.street_id = stb.id
     LEFT JOIN parcel.address_zipcode za ON aa.zipcode_id = za.id
     LEFT JOIN parcel.address_zipcode zb ON ab.zipcode_id = zb.id
     LEFT JOIN parcel.address_city ca ON sta.city_id = ca.id
     LEFT JOIN parcel.address_city cb ON stb.city_id = cb.id
     LEFT JOIN vehicle_data vd ON te.transporter_vehicle_id = vd.vehicle_id
  ORDER BY t.name, za.zipcode, zb.zipcode;

viewm_zipcode_districts

materialized view

Column	Type
node_name	text
zipcode	text
street	text
city	text
node_id	integer
zipcode_id	integer
street_id	integer
city_id	integer

SELECT tn.name AS node_name,
    az.zipcode,
    ast.name AS street,
    ac.name AS city,
    tn.id AS node_id,
    az.id AS zipcode_id,
    ast.id AS street_id,
    ac.id AS city_id
   FROM parcel.transporter_node_address tna
     JOIN parcel.transporter_node_v2 tn ON tna.transporter_node_id = tn.id
     JOIN parcel.address_v2 adr ON tna.address_id = adr.id
     JOIN parcel.address_street ast ON adr.street_id = ast.id
     JOIN parcel.address_zipcode az ON adr.zipcode_id = az.id
     JOIN parcel.address_city ac ON ast.city_id = ac.id
  WHERE tn.type = 'district'::text
  GROUP BY ac.id, ac.name, ast.id, ast.name, az.id, az.zipcode, tn.id, tn.name
  ORDER BY tn.name, az.zipcode;

viewm_zipcode_emissions

materialized view

Column	Type
zipcode_id	integer
zipcode	text
city	text
locality	text
node_id	integer
transporter_name	text
households_count	bigint
emissions_class_lastmile_address	parcel.emissions_type
emissions_class_lastmile_route	parcel.emissions_type
emissions_class_diff	boolean
emissions_class_fullmile_calculated	parcel.emissions_type

WITH addresses AS (
         SELECT a.id AS address_id,
            a.street_id,
            a.zipcode_id,
            a.households_count,
            a.emissions_class_lastmile,
            a.address_locality_id
           FROM parcel.address_v2 a
        ), transporter_per_zipcode AS (
         SELECT az.id AS zipcode_id,
            min(acc.name) AS transporter_name
           FROM parcel.address_v2 a
             JOIN parcel.address_street ast ON a.street_id = ast.id
             JOIN parcel.address_zipcode az ON a.zipcode_id = az.id
             LEFT JOIN parcel.transporter_node_address tna ON tna.address_id = a.id
             LEFT JOIN parcel.transporter_node_v2 tn ON tn.id = tna.transporter_node_id
             LEFT JOIN basejump.accounts acc ON acc.id = tn.transporter_account_id
          GROUP BY az.id
        ), zipcodes AS (
         SELECT az.id AS zipcode_id,
            min(az.zipcode) AS zipcode,
            min(ac.name) AS city,
            min(al.name) AS locality,
            sum(addr.households_count) AS households_count,
            min(DISTINCT addr.emissions_class_lastmile) AS emissions_class_lastmile_address,
            min(es.emissions_class) AS emissions_class_fullmile_calculated,
            zd.node_id
           FROM addresses addr
             LEFT JOIN parcel.address_street ast ON addr.street_id = ast.id
             LEFT JOIN parcel.address_zipcode az ON addr.zipcode_id = az.id
             LEFT JOIN parcel.address_city ac ON ast.city_id = ac.id
             LEFT JOIN parcel.address_locality al ON addr.address_locality_id = al.id
             LEFT JOIN ( SELECT DISTINCT ON (zd_1.zipcode_id) zd_1.node_id,
                    zd_1.zipcode_id
                   FROM parcel.viewm_zipcode_districts zd_1
                  ORDER BY zd_1.zipcode_id, zd_1.node_id) zd ON zd.zipcode_id = az.id
             LEFT JOIN ( SELECT DISTINCT ON (es_1.to_district_node_id) es_1.to_district_node_id,
                    es_1.emissions_class
                   FROM parcel.emission_summary es_1
                  WHERE es_1.from_zipcode_id = 234323) es ON es.to_district_node_id = zd.node_id
          WHERE zd.node_id IS NOT NULL
          GROUP BY az.id, zd.node_id
        ), last_mile_class_per_node AS (
         SELECT DISTINCT ON (vtr.to_node_id) vtr.to_node_id,
            vtr.emissions_class AS emissions_class_lastmile_route,
            vtr.transporter_name
           FROM parcel.viewm_transporter_routes vtr
          WHERE vtr.route_type = 'last_mile'::text
          ORDER BY vtr.to_node_id, vtr.edge_id DESC
        )
 SELECT z.zipcode_id,
    z.zipcode,
    z.city,
    z.locality,
    z.node_id,
    tpz.transporter_name,
    z.households_count,
    z.emissions_class_lastmile_address,
    lmc.emissions_class_lastmile_route::parcel.emissions_type AS emissions_class_lastmile_route,
        CASE
            WHEN z.emissions_class_lastmile_address IS NULL OR lmc.emissions_class_lastmile_route IS NULL THEN NULL::boolean
            ELSE z.emissions_class_lastmile_address IS DISTINCT FROM lmc.emissions_class_lastmile_route::parcel.emissions_type
        END AS emissions_class_diff,
    z.emissions_class_fullmile_calculated
   FROM zipcodes z
     LEFT JOIN transporter_per_zipcode tpz ON z.zipcode_id = tpz.zipcode_id
     LEFT JOIN last_mile_class_per_node lmc ON z.node_id = lmc.to_node_id;

view_addresses_districts

view

Column	Type
address_id	integer
street	text
street_number	text
door	text
zipcode	text
city	text
locality	text
transporter_name	text
district_name	text
district_id	integer
period	date

SELECT a.id AS address_id,
    ast.name AS street,
    a.street_number,
    a.door,
    az.zipcode,
    ac.name AS city,
    al.name AS locality,
    t.name AS transporter_name,
    tnd.name AS district_name,
    tnd.id AS district_id,
    tna.period
   FROM parcel.address_v2 a
     LEFT JOIN parcel.address_street ast ON a.street_id = ast.id
     LEFT JOIN parcel.address_zipcode az ON a.zipcode_id = az.id
     LEFT JOIN parcel.address_city ac ON ast.city_id = ac.id
     LEFT JOIN parcel.address_locality al ON a.address_locality_id = al.id
     LEFT JOIN parcel.transporter_node_address tna ON tna.address_id = a.id
     LEFT JOIN parcel.transporter_node_v2 tnd ON tnd.id = tna.transporter_node_id
     LEFT JOIN basejump.accounts t ON tnd.transporter_account_id = t.id;

view_duplicate_nodes_address

view

Column	Type
address_id	integer
count	bigint
node_ids	text
node_names	text
transporter_names	text
edges_count	text

SELECT viewm_transporter_nodes.address_id,
    count(*) AS count,
    string_agg(viewm_transporter_nodes.node_id::text, ';'::text) AS node_ids,
    string_agg(viewm_transporter_nodes.node_name, ';'::text) AS node_names,
    string_agg(viewm_transporter_nodes.transporter_name, ';'::text) AS transporter_names,
    string_agg(viewm_transporter_nodes.edges_count::text, ';'::text) AS edges_count
   FROM parcel.viewm_transporter_nodes
  WHERE viewm_transporter_nodes.address_id IS NOT NULL
  GROUP BY viewm_transporter_nodes.address_id
 HAVING count(*) > 1
  ORDER BY (count(*)) DESC;

view_duplicate_nodes_nearby

view

Column	Type
street	text
street_numbers	text
city	text
nodes_count	bigint
node_ids	text
address_ids	text
node_names	text
transporter_names	text
edges_counts	text

SELECT lower(vtn.street_name) AS street,
    string_agg(concat(vtn.street_number, ' ', vtn.door), ','::text) AS street_numbers,
    lower(vtn.city_name) AS city,
    count(*) AS nodes_count,
    string_agg(vtn.node_id::text, ','::text) AS node_ids,
    string_agg(vtn.address_id::text, ','::text) AS address_ids,
    string_agg(vtn.node_name, ','::text) AS node_names,
    string_agg(vtn.transporter_name, ','::text) AS transporter_names,
    string_agg(vtn.edges_count::text, ','::text) AS edges_counts
   FROM parcel.viewm_transporter_nodes vtn
  WHERE vtn.node_type IS DISTINCT FROM 'district'::text AND vtn.node_type IS DISTINCT FROM 'pickup'::text
  GROUP BY (lower(vtn.street_name)), (lower(vtn.city_name))
 HAVING count(*) > 1 AND count(DISTINCT concat(vtn.street_number, ' ', vtn.door)) > 1
  ORDER BY (count(*)) DESC;

view_edges_overview

view

Column	Type
transporter_account_id	uuid
transporter_name	text
emissions_class	text
is_dangling	boolean
is_zero_m	boolean
is_default_vehicle	boolean
from_node_type	text
to_node_type	text
vehicle_type	text
route_type	text
edge_count	bigint

SELECT t.id AS transporter_account_id,
    t.name AS transporter_name,
    vf.emissions_class,
        CASE
            WHEN te.node_a = 1 THEN true
            ELSE false
        END AS is_dangling,
    sum(
        CASE
            WHEN te.distance_m = 0 THEN 1
            ELSE 0
        END) > 0 AS is_zero_m,
        CASE
            WHEN te.transporter_vehicle_id = 1 THEN true
            ELSE false
        END AS is_default_vehicle,
    from_node.type AS from_node_type,
    to_node.type AS to_node_type,
    vt.type AS vehicle_type,
        CASE
            WHEN from_node.type = 'pickup'::text OR to_node.type = 'pickup'::text THEN 'pickup'::text
            WHEN from_node.type = 'district'::text OR to_node.type = 'district'::text THEN 'last_mile'::text
            WHEN from_node.type = 't3'::text OR to_node.type = 't3'::text THEN 'regional'::text
            ELSE 'line_haul'::text
        END AS route_type,
    count(*) AS edge_count
   FROM parcel.transporter_edge_v2 te
     LEFT JOIN parcel.transporter_node_v2 from_node ON te.node_a = from_node.id
     LEFT JOIN parcel.transporter_node_v2 to_node ON te.node_b = to_node.id
     LEFT JOIN basejump.accounts t ON te.transporter_account_id = t.id
     LEFT JOIN parcel.transporter_vehicle_v2 tv ON te.transporter_vehicle_id = tv.id
     LEFT JOIN parcel.vehicle_type vt ON tv.vehicle_type_id = vt.id
     LEFT JOIN parcel.vehicle_fuel_type vf ON vt.fuel_type_id = vf.id
  GROUP BY vf.emissions_class, (
        CASE
            WHEN te.node_a = 1 THEN true
            ELSE false
        END), (
        CASE
            WHEN te.transporter_vehicle_id = 1 THEN true
            ELSE false
        END), from_node.type, to_node.type, t.name, t.id, vt.type
  ORDER BY (count(*)) DESC;

view_edges_overview_list

view

Column	Type
edge_id	integer
transporter_account_id	uuid
transporter_name	text
is_dangling	boolean
is_zero_m	boolean
is_default_vehicle	boolean
from_node_id	integer
from_node_name	text
from_node_type	text
from_zipcode	text
from_city_name	text
to_node_id	integer
to_node_name	text
to_node_type	text
to_zipcode	text
to_city_name	text
route_type	text
vehicle_type	text
emissions_class	text

SELECT te.id AS edge_id,
    t.id AS transporter_account_id,
    t.name AS transporter_name,
        CASE
            WHEN te.node_a = 1 THEN true
            ELSE false
        END AS is_dangling,
    sum(
        CASE
            WHEN te.distance_m = 0 THEN 1
            ELSE 0
        END) > 0 AS is_zero_m,
        CASE
            WHEN te.transporter_vehicle_id = 1 THEN true
            ELSE false
        END AS is_default_vehicle,
    from_node.id AS from_node_id,
    from_node.name AS from_node_name,
    from_node.type AS from_node_type,
    from_addr_zipcode.zipcode AS from_zipcode,
    initcap(from_addr_city.name) AS from_city_name,
    to_node.id AS to_node_id,
    to_node.name AS to_node_name,
    to_node.type AS to_node_type,
    to_addr_zipcode.zipcode AS to_zipcode,
    initcap(to_addr_city.name) AS to_city_name,
        CASE
            WHEN from_node.type = 'pickup'::text OR to_node.type = 'pickup'::text THEN 'pickup'::text
            WHEN from_node.type = 'district'::text OR to_node.type = 'district'::text THEN 'last_mile'::text
            WHEN from_node.type = 't3'::text OR to_node.type = 't3'::text THEN 'regional'::text
            ELSE 'line_haul'::text
        END AS route_type,
    vt.type AS vehicle_type,
    vf.emissions_class
   FROM parcel.transporter_edge_v2 te
     LEFT JOIN parcel.transporter_node_v2 from_node ON te.node_a = from_node.id
     LEFT JOIN parcel.address_v2 from_addr ON from_node.address_id = from_addr.id
     LEFT JOIN parcel.address_street from_addr_street ON from_addr.street_id = from_addr_street.id
     LEFT JOIN parcel.address_zipcode from_addr_zipcode ON from_addr.zipcode_id = from_addr_zipcode.id
     LEFT JOIN parcel.address_city from_addr_city ON from_addr_street.city_id = from_addr_city.id
     LEFT JOIN parcel.transporter_node_v2 to_node ON te.node_b = to_node.id
     LEFT JOIN parcel.address_v2 to_addr ON to_node.address_id = to_addr.id
     LEFT JOIN parcel.address_street to_addr_street ON to_addr.street_id = to_addr_street.id
     LEFT JOIN parcel.address_zipcode to_addr_zipcode ON to_addr.zipcode_id = to_addr_zipcode.id
     LEFT JOIN parcel.address_city to_addr_city ON to_addr_street.city_id = to_addr_city.id
     LEFT JOIN basejump.accounts t ON te.transporter_account_id = t.id
     LEFT JOIN parcel.transporter_vehicle_v2 tv ON te.transporter_vehicle_id = tv.id
     LEFT JOIN parcel.vehicle_type vt ON tv.vehicle_type_id = vt.id
     LEFT JOIN parcel.vehicle_fuel_type vf ON vt.fuel_type_id = vf.id
  GROUP BY (
        CASE
            WHEN te.node_a = 1 THEN true
            ELSE false
        END), (
        CASE
            WHEN te.transporter_vehicle_id = 1 THEN true
            ELSE false
        END), from_node.id, to_node.id, from_node.name, to_node.name, from_node.type, to_node.type, from_addr_zipcode.zipcode, (initcap(from_addr_city.name)), to_addr_zipcode.zipcode, (initcap(to_addr_city.name)), t.name, t.id, vt.type, vf.emissions_class, te.id
  ORDER BY t.name, from_addr_zipcode.zipcode, to_addr_zipcode.zipcode, te.id DESC;

view_emission_summary_calculation_status

view

Column	Type
is_route_calculated	boolean
es_count	bigint

SELECT es.is_route_calculated,
    count(*) AS es_count
   FROM parcel.emission_summary es
  GROUP BY es.is_route_calculated
  ORDER BY es.is_route_calculated;

view_emission_summary_overview

view

Column	Type
transporter_account_id	uuid
transporter_name	text
city_name	text
is_route_calculated	boolean
emissions_class	parcel.emissions_type
es_count	bigint

SELECT vtn.transporter_account_id,
    vtn.transporter_name,
    initcap(vtn.city_name) AS city_name,
    es.is_route_calculated,
        CASE
            WHEN count(es.emissions_class) = 0 THEN NULL::text
            WHEN bool_or(es.emissions_class = 'fossil'::parcel.emissions_type) THEN 'fossil'::text
            WHEN bool_or(es.emissions_class = 'fossil_free'::parcel.emissions_type) THEN 'fossil_free'::text
            ELSE 'emission_free'::text
        END::parcel.emissions_type AS emissions_class,
    count(*) AS es_count
   FROM parcel.emission_summary es
     LEFT JOIN parcel.viewm_transporter_nodes vtn ON es.to_district_node_id = vtn.node_id
  GROUP BY vtn.transporter_account_id, vtn.transporter_name, vtn.city_name, es.is_route_calculated
  ORDER BY es.is_route_calculated, (
        CASE
            WHEN count(es.emissions_class) = 0 THEN NULL::text
            WHEN bool_or(es.emissions_class = 'fossil'::parcel.emissions_type) THEN 'fossil'::text
            WHEN bool_or(es.emissions_class = 'fossil_free'::parcel.emissions_type) THEN 'fossil_free'::text
            ELSE 'emission_free'::text
        END::parcel.emissions_type), (count(*)) DESC;

view_emission_summary_overview_list

view

Column	Type
transporter_account_id	uuid
transporter_name	text
node_name	text
zipcode	text
city_name	text
is_route_calculated	boolean
emissions_class	parcel.emissions_type
distance_km	numeric
emissions_gco2ep1kg	numeric
es_count	bigint

SELECT vtn.transporter_account_id,
    vtn.transporter_name,
    vtn.node_name,
    vtn.zipcode,
    initcap(vtn.city_name) AS city_name,
    es.is_route_calculated,
        CASE
            WHEN count(es.emissions_class) = 0 THEN NULL::text
            WHEN bool_or(es.emissions_class = 'fossil'::parcel.emissions_type) THEN 'fossil'::text
            WHEN bool_or(es.emissions_class = 'fossil_free'::parcel.emissions_type) THEN 'fossil_free'::text
            ELSE 'emission_free'::text
        END::parcel.emissions_type AS emissions_class,
    round(avg(es.distance_m) / 1000::numeric, 1) AS distance_km,
    round(avg(es.emissions_gco2ep1kg), 4) AS emissions_gco2ep1kg,
    count(*) AS es_count
   FROM parcel.emission_summary es
     LEFT JOIN parcel.viewm_transporter_nodes vtn ON es.to_district_node_id = vtn.node_id
  GROUP BY vtn.transporter_account_id, vtn.transporter_name, vtn.node_name, vtn.zipcode, vtn.city_name, es.is_route_calculated
  ORDER BY vtn.transporter_name, vtn.zipcode;

view_nodes_disconnected

view

Column	Type
node_id	integer
transporter_name	text
node_name	text
street_name	text
city_name	text
edges_count	bigint
orders_affected	bigint

SELECT vtn.node_id,
    vtn.transporter_name,
    vtn.node_name,
    vtn.street_name,
    vtn.city_name,
    vtn.edges_count,
    count(o.*) AS orders_affected
   FROM parcel.transporter_node_v2 n
     LEFT JOIN parcel.viewm_transporter_nodes vtn ON n.id = vtn.node_id
     LEFT JOIN parcel.transporter_edge_v2 t ON n.id = t.node_a
     LEFT JOIN "order" o ON t.node_b = o.to_district_node_id
  WHERE n.type <> 'district'::text AND NOT (EXISTS ( SELECT 1
           FROM parcel.viewm_transporter_routes vtr
          WHERE vtr.route_type <> 'last_mile'::text AND (vtr.from_node_address_id = n.address_id OR vtr.to_node_address_id = n.address_id)))
  GROUP BY vtn.node_id, vtn.transporter_name, vtn.node_name, vtn.street_name, vtn.city_name, vtn.edges_count
  ORDER BY (count(o.*)) DESC;

view_orders_customers

view

Column	Type
customer_name	character varying
customer_nos	text
customer_account_id	uuid
pickup_node_id	integer
from_zipcode_id	integer
from_zipcode	text
orders_count_3mo	bigint
calc_percent	integer
pickup_node_zipcode	text

SELECT o.customer AS customer_name,
    string_agg(DISTINCT o.customer_no::text, ', '::text) AS customer_nos,
    o.customer_account_id,
    min(tnp.transporter_node_id) AS pickup_node_id,
    o.from_zipcode_id,
    o.from_zipcode,
    count(DISTINCT o.id) AS orders_count_3mo,
    round(count(*) FILTER (WHERE es.is_route_calculated = true)::numeric / count(*)::numeric * 100::numeric)::integer AS calc_percent,
    min(az_pickup.zipcode) AS pickup_node_zipcode
   FROM "order" o
     LEFT JOIN parcel.transporter_node_pickup tnp ON tnp.customer_account_id = o.customer_account_id
     LEFT JOIN parcel.transporter_node_v2 tn ON tn.id = tnp.transporter_node_id
     LEFT JOIN parcel.address_v2 adr ON adr.id = tn.address_id
     LEFT JOIN parcel.address_zipcode az_pickup ON az_pickup.id = adr.zipcode_id
     LEFT JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
  WHERE o.delivery_date >= (CURRENT_DATE - '3 mons'::interval) AND o.customer::text !~~* 'Vinted%'::text AND o.customer::text !~~* 'Parcel4you%'::text AND o.transporter::text IS DISTINCT FROM 'postnord'::text AND o.transporter::text IS DISTINCT FROM 'ica'::text AND o.transporter::text IS DISTINCT FROM 'bring'::text
  GROUP BY o.customer, o.customer_account_id, o.from_zipcode_id, o.from_zipcode
 HAVING count(DISTINCT o.id) > 2
  ORDER BY o.customer;

view_orders_debug_routing

view

Column	Type
order_id	bigint
tracking_id	character varying
delivery_date	date
customer	character varying
transporter	character varying
from_city	text
to_city	text
emissions_gco2ep1kg	numeric
emissions_class	parcel.emissions_type
edge_ids	integer[]
debug_url	text

SELECT o.id AS order_id,
    o.tracking_id,
    o.delivery_date::date AS delivery_date,
    o.customer,
    o.transporter,
    ac_from.name AS from_city,
    node_to.city_name AS to_city,
    es.emissions_gco2ep1kg,
    es.emissions_class,
    es.edge_ids,
    (('https://app.aimabel.ai/aimabel/parcel/network/debug-routing?from_zipcode_id='::text || o.from_zipcode_id) || '&to_district_node_id='::text) || o.to_district_node_id AS debug_url
   FROM "order" o
     LEFT JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
     LEFT JOIN parcel.address_zipcode az_from ON az_from.id = o.from_zipcode_id
     LEFT JOIN parcel.address_city ac_from ON ac_from.id = az_from.city_id
     LEFT JOIN parcel.viewm_transporter_nodes node_to ON node_to.node_id = o.to_district_node_id
  WHERE o.customer::text !~~* 'Vinted%'::text AND o.customer::text !~~* 'Parcel4you%'::text AND o.transporter::text IS DISTINCT FROM 'postnord'::text AND o.transporter::text IS DISTINCT FROM 'ica'::text AND o.transporter::text IS DISTINCT FROM 'bring'::text
  ORDER BY o.id DESC
 LIMIT 1000;

view_orders_emissions

view

Column	Type
id	bigint
delivery_date	date
tracking_id	character varying
transporter	text
customer	text
product	character varying
address	text
emissions_gco2e	numeric
is_route_calculated	boolean
emissions_class	text
vehicle_last_mile	text

SELECT o.id,
    o.delivery_date::date AS delivery_date,
    o.tracking_id,
    transporter.name AS transporter,
    customer.name AS customer,
    o.product,
    initcap((((o.to_street || ' '::text) || COALESCE(o.to_door, ''::text)) || ', '::text) || COALESCE(o.to_city, ''::text)) AS address,
    round(es.emissions_gco2ep1kg * o.weight::numeric, 3) AS emissions_gco2e,
    es.is_route_calculated,
    initcap(replace(es.emissions_class::text, '_'::text, '-'::text)) AS emissions_class,
    initcap(replace(((te.vehicle_type || ' ('::text) || te.fuel_type) || ')'::text, '_'::text, ' '::text)) AS vehicle_last_mile
   FROM "order" o
     LEFT JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
     LEFT JOIN basejump.accounts transporter ON transporter.id = o.transporter_account_id
     LEFT JOIN basejump.accounts customer ON customer.id = o.customer_account_id
     JOIN LATERAL ( SELECT e.edge_id
           FROM unnest(es.edge_ids) WITH ORDINALITY e(edge_id, ord)
          ORDER BY e.ord DESC
         LIMIT 1) last_edge ON true
     JOIN parcel.viewm_transporter_routes te ON te.edge_id = last_edge.edge_id
  ORDER BY o.id DESC;

view_orders_overview

view

Column	Type
delivery_date	date
weight_metrictons	numeric
orders_count	bigint
transporter_account_id_count	bigint
from_zipcode_id_count	bigint
to_district_node_id_count	bigint
complete_percent	numeric
es_total	bigint
es_calculated	bigint
calc_percent	numeric
customer_account_id_count	bigint
to_zipcode_count	bigint

WITH joined_orders AS (
         SELECT o.id,
            o.delivery_date::date AS delivery_date,
            o.weight,
            o.transporter_account_id,
            o.from_zipcode_id,
            o.to_district_node_id,
            o.customer_account_id,
            o.to_zipcode,
            es.id AS emission_summary_id,
            es.is_route_calculated
           FROM "order" o
             LEFT JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
          WHERE o.delivery_date >= date_trunc('month'::text, CURRENT_DATE - '4 mons'::interval)
        )
 SELECT joined_orders.delivery_date,
    round(sum(joined_orders.weight::numeric) / 1000::numeric, 1) AS weight_metrictons,
    count(*) AS orders_count,
    count(joined_orders.transporter_account_id) AS transporter_account_id_count,
    count(joined_orders.from_zipcode_id) AS from_zipcode_id_count,
    count(joined_orders.to_district_node_id) AS to_district_node_id_count,
    round(LEAST(count(joined_orders.transporter_account_id), count(joined_orders.from_zipcode_id), count(joined_orders.to_district_node_id))::numeric / count(*)::numeric * 100::numeric) AS complete_percent,
    count(joined_orders.emission_summary_id) AS es_total,
    count(*) FILTER (WHERE joined_orders.is_route_calculated = true) AS es_calculated,
    round(count(*) FILTER (WHERE joined_orders.is_route_calculated = true)::numeric / count(*)::numeric * 100::numeric) AS calc_percent,
    count(joined_orders.customer_account_id) AS customer_account_id_count,
    count(joined_orders.to_zipcode) AS to_zipcode_count
   FROM joined_orders
  GROUP BY joined_orders.delivery_date
  ORDER BY joined_orders.delivery_date DESC;

view_orders_transporters

view

Column	Type
transporter_name	character varying
transporter_account_id	uuid
orders_count_3mo	bigint
calc_percent	integer

SELECT o.transporter AS transporter_name,
    o.transporter_account_id,
    count(DISTINCT o.id) AS orders_count_3mo,
    round(count(*) FILTER (WHERE es.is_route_calculated = true)::numeric / count(*)::numeric * 100::numeric)::integer AS calc_percent
   FROM "order" o
     LEFT JOIN parcel.address_zipcode az_from ON o.from_zipcode_id = az_from.id
     LEFT JOIN parcel.address_zipcode az_to ON o.to_zipcode_id = az_to.id
     LEFT JOIN parcel.emission_summary es ON es.from_zipcode_id = o.from_zipcode_id AND es.to_district_node_id = o.to_district_node_id
  WHERE o.delivery_date >= (CURRENT_DATE - '3 mons'::interval) AND o.customer::text !~~* 'Vinted%'::text AND o.customer::text !~~* 'Parcel4you%'::text AND o.transporter::text IS DISTINCT FROM 'postnord'::text AND o.transporter::text IS DISTINCT FROM 'ica'::text AND o.transporter::text IS DISTINCT FROM 'bring'::text
  GROUP BY o.transporter, o.transporter_account_id
 HAVING count(DISTINCT o.id) > 2
  ORDER BY (
        CASE
            WHEN o.transporter::text = ''::text THEN 1
            ELSE 0
        END), o.transporter, (count(DISTINCT o.id)) DESC;

view_vehicle_mapping

view

Column	Type
transporter_vehicle_string	text
mapped_vehicle	text
mapped_vehicle_type	text
route_count	bigint

SELECT te.transporter_vehicle_string,
    tv.regnr AS mapped_vehicle,
    vt.identifier AS mapped_vehicle_type,
    count(*) AS route_count
   FROM parcel.transporter_edge_v2 te
     JOIN parcel.transporter_vehicle_v2 tv ON te.transporter_vehicle_id = tv.id
     JOIN parcel.vehicle_type vt ON tv.vehicle_type_id = vt.id
  GROUP BY te.transporter_vehicle_string, tv.regnr, vt.identifier
  ORDER BY te.transporter_vehicle_string, tv.regnr, vt.identifier;

view_zipcode_emissions_by_locality

view

Column	Type
transporter_name	text
locality	text
emissions_class_lastmile_address	parcel.emissions_type
emissions_class_lastmile_route	parcel.emissions_type
emissions_class_fullmile_calculated	parcel.emissions_type
households_count	numeric

SELECT viewm_zipcode_emissions.transporter_name,
    viewm_zipcode_emissions.locality,
    viewm_zipcode_emissions.emissions_class_lastmile_address,
    viewm_zipcode_emissions.emissions_class_lastmile_route,
    viewm_zipcode_emissions.emissions_class_fullmile_calculated,
    sum(viewm_zipcode_emissions.households_count) AS households_count
   FROM parcel.viewm_zipcode_emissions
  GROUP BY viewm_zipcode_emissions.transporter_name, viewm_zipcode_emissions.locality, viewm_zipcode_emissions.emissions_class_lastmile_address, viewm_zipcode_emissions.emissions_class_fullmile_calculated, viewm_zipcode_emissions.emissions_class_lastmile_route
  ORDER BY viewm_zipcode_emissions.transporter_name, viewm_zipcode_emissions.locality;

view_zipcode_groups

view

Column	Type
zipcode_prefix	text
district_prefix	text
transporter_name	text
district_count	bigint
cities	text

SELECT concat("left"(viewm_transporter_nodes.zipcode, 2), '*') AS zipcode_prefix,
    concat("left"(viewm_transporter_nodes.node_name, 2), '*') AS district_prefix,
    viewm_transporter_nodes.transporter_name,
    count(*) AS district_count,
    string_agg(DISTINCT viewm_transporter_nodes.city_name, ', '::text) AS cities
   FROM parcel.viewm_transporter_nodes
  WHERE viewm_transporter_nodes.node_type = 'district'::text
  GROUP BY viewm_transporter_nodes.transporter_name, (concat("left"(viewm_transporter_nodes.zipcode, 2), '*')), (concat("left"(viewm_transporter_nodes.node_name, 2), '*'))
  ORDER BY (concat("left"(viewm_transporter_nodes.zipcode, 2), '*')), (concat("left"(viewm_transporter_nodes.node_name, 2), '*'));

Functions (37)

calculate_emission_summary()

• Returns: trigger
• Language: plpgsql
• Kind: function

create_address(p_street text, p_street_number text, p_door text, p_zipcode text, p_city text, p_households_count integer DEFAULT NULL::integer, p_emissions_class_lastmile parcel.emissions_type DEFAULT NULL::parcel.emissions_type, p_coordinates gis.geometry DEFAULT NULL::gis.geometry, p_locality_name text DEFAULT NULL::text, p_address_type character varying DEFAULT NULL::character varying)

• Returns: TABLE(address_id integer, street text, street_number text, door text, zipcode text, city text)
• Language: plpgsql
• Kind: function

create_customer_pickup_route(p_transporter_account_name text, p_customer_account_name text, p_node1_street text, p_node1_street_number text, p_node1_door text, p_node1_zipcode text, p_node1_city text, p_node2_name text, p_node2_type text, p_node2_street text, p_node2_street_number text, p_node2_door text, p_node2_zipcode text, p_node2_city text, p_distance_m integer DEFAULT NULL::integer, p_load_factor_100 integer DEFAULT NULL::integer, p_empty_running_100 integer DEFAULT NULL::integer, p_vehicle_type text DEFAULT 'truck'::text, p_fuel_type text DEFAULT 'diesel'::text, p_vehicle_regnr text DEFAULT NULL::text)

• Returns: parcel.transporter_node_pickup
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

create_district(p_transporter_account_name text, p_district_name text, p_address_id integer DEFAULT NULL::integer, p_vehicle_fuel_type text DEFAULT NULL::text, p_vehicle_string text DEFAULT NULL::text)

• Returns: TABLE(account_id uuid, transporter_node_v2_id integer, transporter_node_address_id integer, transporter_vehicle_id integer, transporter_edge_v2_id integer)
• Language: plpgsql
• Kind: function

create_district_terminal_route(p_transporter_account_name text, p_district_name text, p_terminal_name text, p_terminal_street text DEFAULT NULL::text, p_terminal_street_number text DEFAULT NULL::text, p_terminal_zipcode text DEFAULT NULL::text, p_terminal_city text DEFAULT NULL::text, p_distance_m integer DEFAULT NULL::integer)

• Returns: TABLE(account_id uuid, district_node_id integer, terminal_node_id integer, route_id integer)
• Language: plpgsql
• Kind: function

create_district_terminal_route_v2(p_transporter_account_id uuid, p_district_name text, p_terminal_name text, p_terminal_street text DEFAULT NULL::text, p_terminal_street_number text DEFAULT NULL::text, p_terminal_zipcode text DEFAULT NULL::text, p_terminal_city text DEFAULT NULL::text, p_distance_m integer DEFAULT NULL::integer)

• Returns: TABLE(account_id uuid, district_node_id integer, terminal_node_id integer, route_id integer)
• Language: plpgsql
• Kind: function

create_emission_summary(p_delivery_date date DEFAULT NULL::date, p_limit integer DEFAULT 250)

• Returns: integer
• Language: plpgsql
• Kind: function

create_orders_daily_summary(p_date date DEFAULT CURRENT_DATE)

• Returns: integer
• Language: plpgsql
• Kind: function

create_terminals_route(p_transporter_name text, p_node1_name text, p_node1_type text, p_node1_street text, p_node1_street_number text, p_node1_door text, p_node1_zipcode text, p_node1_city text, p_node2_name text, p_node2_type text, p_node2_street text, p_node2_street_number text, p_node2_door text, p_node2_zipcode text, p_node2_city text, p_distance_m integer DEFAULT NULL::integer, p_load_factor_100 integer DEFAULT NULL::integer, p_empty_running_100 integer DEFAULT NULL::integer, p_vehicle_type text DEFAULT 'truck'::text, p_fuel_type text DEFAULT 'diesel'::text, p_vehicle_regnr text DEFAULT NULL::text)

• Returns: TABLE(transporter_id uuid, node1_id integer, node2_id integer, address1_id integer, address2_id integer, vehicle_id integer, vehicle_type_id integer, fuel_type_id integer, edge_id integer)
• Language: plpgsql
• Kind: function

create_vehicle(p_transporter_account_name text, p_fuel_type text, p_vehicle_type text, p_vehicle_sub_type text DEFAULT NULL::text, p_regnr text DEFAULT NULL::text, p_brand text DEFAULT NULL::text, p_model text DEFAULT NULL::text, p_max_capacity_kg integer DEFAULT NULL::integer, p_energy_kwhpkm_1000 integer DEFAULT NULL::integer, p_fuel_consumption_pkm_1000 integer DEFAULT NULL::integer)

• Returns: TABLE(transporter_id uuid, vehicle_id integer, vehicle_type_id integer)
• Language: plpgsql
• Kind: function

estimate_zero_meter_routes()

• Returns: integer
• Language: plpgsql
• Kind: function

find_address(p_street text DEFAULT NULL::text, p_street_number text DEFAULT NULL::text, p_door text DEFAULT NULL::text, p_zipcode text DEFAULT NULL::text, p_city text DEFAULT NULL::text)

• Returns: SETOF parcel.view_addresses_districts
• Language: plpgsql
• Kind: function

find_customer_pickup_node(p_transporter_account_name text, p_customer_account_name text)

• Returns: parcel.viewm_transporter_nodes
• Language: plpgsql
• Kind: function

find_transporter_node(p_transporter_account_name text, p_node_name text DEFAULT NULL::text, p_address_id integer DEFAULT NULL::integer)

• Returns: parcel.viewm_transporter_nodes
• Language: plpgsql
• Kind: function

get_customer_zipcodes(p_customer_account_name text, p_avg_weight integer DEFAULT 1000)

• Returns: TABLE(zipcode text, city text, households_count integer, households_fossil_free_count integer, emissions_class_distr_region parcel.emissions_type, emissions_class_calc parcel.emissions_type, distance_m integer, emissions_gco2ep1kg numeric, emissions_gco2e_avg_weight numeric, district_count bigint, district_node_ids integer[])
• Language: plpgsql
• Kind: function

get_emissions_data_by_locality(type text, transporter text[] DEFAULT NULL::text[])

• Returns: TABLE(locality text, emissions_class parcel.emissions_type, households_count numeric, total numeric)
• Language: plpgsql
• Kind: function

get_full_routes(p_customer text DEFAULT NULL::text, p_transporter_accounts uuid[] DEFAULT NULL::uuid[], p_emissions_class parcel.emissions_type DEFAULT NULL::parcel.emissions_type, p_start_date date DEFAULT '2025-04-01'::date)

• Returns: TABLE(from_zipcode_id bigint, to_district_node_id bigint, to_city text, transporters text, legs integer, emissions_gco2ep1kg numeric, emissions_class parcel.emissions_type)
• Language: plpgsql
• Kind: function

get_geo_network(type text, transporters text[] DEFAULT NULL::text[], locality_filter text[] DEFAULT NULL::text[], urban_city_filter text[] DEFAULT NULL::text[], region_filter text[] DEFAULT NULL::text[], municipality_type_filter text[] DEFAULT NULL::text[])

• Returns: TABLE(name text, emissions_class_lastmile parcel.emissions_type, household_count numeric, total_household_count numeric, inhabitants bigint)
• Language: plpgsql
• Kind: function

get_locality_emissions_class_distribution()

• Returns: TABLE(locality text, emissions_class parcel.emissions_type, households_count numeric, total numeric)
• Language: plpgsql
• Kind: function

get_nodes_disconnected()

• Returns: TABLE(node_id bigint, transporter_name text, node_name text, street_name text, city_name text, edges_count bigint, orders_affected bigint)
• Language: sql
• Kind: function

get_order_emissions(p_account_id uuid, p_groupby_interval text DEFAULT NULL::text, p_filter_start_date date DEFAULT NULL::date, p_filter_end_date date DEFAULT NULL::date, p_groupby_transporter boolean DEFAULT false, p_filter_transporter_account_ids uuid[] DEFAULT NULL::uuid[], p_exclude_transporter_account_ids uuid[] DEFAULT NULL::uuid[], p_groupby_customer boolean DEFAULT false, p_filter_customer_account_ids uuid[] DEFAULT NULL::uuid[], p_groupby_product boolean DEFAULT false, p_filter_product_names text[] DEFAULT NULL::text[], p_exclude_product_names text[] DEFAULT NULL::text[], p_groupby_emissions_class boolean DEFAULT false, p_filter_emissions_class parcel.emissions_type[] DEFAULT NULL::parcel.emissions_type[])

• Returns: TABLE(label text, transporter_name text, customer_name text, product_name text, emissions_class parcel.emissions_type, order_count integer, weight numeric, volume numeric, distance_km numeric, energy_kwh numeric, fuel_l numeric, emissions_gco2e numeric, emissions_ttw_gco2e numeric, emissions_wtt_gco2e numeric, emissions_comparison_gco2e numeric, emissions_from_logistics_hubs_gco2e numeric, calc_percent numeric)
• Language: plpgsql
• Kind: function

get_order_overview()

• Returns: TABLE(delivery_date date, weight_metrictons numeric, orders_count bigint, transporter_account_id_count bigint, from_zipcode_id_count bigint, to_district_node_id_count bigint, complete_percent numeric, es_total numeric, es_calculated numeric, calc_percent numeric, customer_account_id_count bigint, to_zipcode_count bigint)
• Language: sql
• Kind: function

get_orders_data_emissions(p_start_date date DEFAULT NULL::date, p_end_date date DEFAULT NULL::date, p_view_by text DEFAULT 'delivery_date'::text, p_emissions_type text DEFAULT 'co2e'::text, p_customers text[] DEFAULT NULL::text[], p_transporters text[] DEFAULT NULL::text[], p_regions text[] DEFAULT NULL::text[], p_municipality_types text[] DEFAULT NULL::text[], p_cities text[] DEFAULT NULL::text[], p_municipalities text[] DEFAULT NULL::text[], p_total boolean DEFAULT false, p_get_top_ten boolean DEFAULT false)

• Returns: TABLE(delivery_date date, label text, emissions numeric, weight numeric, order_count bigint, calc_percent numeric)
• Language: plpgsql
• Kind: function

get_orders_list(p_account_id uuid, p_start_date date, p_end_date date, p_transporter text[] DEFAULT NULL::text[], p_customer text[] DEFAULT NULL::text[], p_product text[] DEFAULT NULL::text[], p_address text DEFAULT NULL::text, p_tracking_id text DEFAULT NULL::text, p_is_route_calculated boolean DEFAULT NULL::boolean, p_emissions_class text[] DEFAULT NULL::text[], p_vehicle_last_mile text[] DEFAULT NULL::text[], p_sort_by text DEFAULT NULL::text, p_sort_order text DEFAULT NULL::text, p_products_to_exclude text[] DEFAULT NULL::text[], p_region text[] DEFAULT NULL::text[], p_locality text[] DEFAULT NULL::text[], p_municipality_type text[] DEFAULT NULL::text[], p_urban_city text[] DEFAULT NULL::text[])

• Returns: SETOF parcel.viewm_orders_list
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

get_orders_list_count(p_account_id uuid, p_start_date date, p_end_date date, p_transporter text[] DEFAULT NULL::text[], p_customer text[] DEFAULT NULL::text[], p_product text[] DEFAULT NULL::text[], p_address text DEFAULT NULL::text, p_tracking_id text DEFAULT NULL::text, p_is_route_calculated boolean DEFAULT NULL::boolean, p_emissions_class text[] DEFAULT NULL::text[], p_vehicle_last_mile text[] DEFAULT NULL::text[], p_products_to_exclude text[] DEFAULT NULL::text[], p_region text[] DEFAULT NULL::text[], p_locality text[] DEFAULT NULL::text[], p_municipality_type text[] DEFAULT NULL::text[], p_urban_city text[] DEFAULT NULL::text[])

• Returns: integer
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

insert_emission_summary_zipcodes(order_rows jsonb[])

• Returns: TABLE(emission_summary_id bigint, from_zipcode_id bigint, to_district_node_id bigint)
• Language: plpgsql
• Kind: function

insert_orders(order_rows jsonb[])

• Returns: TABLE(transporter character varying, tracking_id character varying, delivery_date timestamp with time zone, volume real, weight real, product character varying, customer character varying, customer_no character varying, metadata jsonb, from_zipcode text, to_zipcode text, to_street text, to_door text, to_city text)
• Language: plpgsql
• Kind: function

recalculate_failed_emission_summaries(p_limit integer DEFAULT 250)

• Returns: integer
• Language: plpgsql
• Kind: function

refresh_network_graph()

• Returns: text
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

refresh_orders_data_geography()

• Returns: void
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

refresh_orders_list_materialized_view()

• Returns: void
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

route_parcel(p_transporter_account_name text, p_from_node_id integer, p_to_address_id integer, p_parcel_weight integer DEFAULT 530)

• Returns: jsonb
• Language: plpgsql
• Kind: function

update_order_relations(p_delivery_date date DEFAULT NULL::date, p_limit integer DEFAULT 250)

• Returns: TABLE(updated_transporter_accounts integer, updated_to_zipcodes integer, updated_district_node_ids integer)
• Language: plpgsql
• Kind: function

update_order_relations_customers(p_delivery_date date DEFAULT NULL::date, p_limit integer DEFAULT 250)

• Returns: integer
• Language: plpgsql
• Kind: function

update_order_relations_district_nodes(p_delivery_date date DEFAULT NULL::date, p_limit integer DEFAULT 250)

• Returns: TABLE(orders_processed integer, orders_with_address integer, orders_with_district integer)
• Language: plpgsql
• Kind: function

update_order_relations_to_zipcodes(p_delivery_date date DEFAULT NULL::date, p_limit integer DEFAULT 250)

• Returns: integer
• Language: plpgsql
• Kind: function

update_order_relations_transporters(p_delivery_date date DEFAULT NULL::date, p_limit integer DEFAULT 250)

• Returns: integer
• Language: plpgsql
• Kind: function