---
title: "Chantier transfert des briques ETL et API de l'observatoire -> RPC"
tags: specs
---

# Chantier transfert des briques ETL et API de l'observatoire -> RPC

## Contexte
Actuellement (septembre 2022), l'observatoire national du covoiturage au quotidien est porté par la startup covoiturage tout en étant un projet distinct du registre de preuve de covoiturage (RPC). C'était un prérequis de la création de l'observatoire car l'ambition était d'impulser le projet au sein de la startup en valorisant les données du registre mais de ne pas le limiter à la seule observation des données issuent des opérateurs de covoiturage. Aussi, il a été conçu pour être facilement transmissible dans le cas ou la startup covoiturage cède cette mission à un tiers.

C'est pour cela que la plate-forme technique de l'observatoire est actuellement autonome et dispose:
* d'une base de données Postgresql + Postgis
* d'un ETL permettant d'alimenter la base de données persistante avec les données en open-data (RPC, transport.data.gouv, cerema)
* d'une API pour servir les données
* d'un frontend

Aujourd'hui, l'évolution du RPC nous conforte dans l'idée que l'observatoire et le registre sont indissociables et qu'il est pertinant de lier plus fortement les deux projets. Les arguments en faveur de ce rapprochement sont:
* Une mutualisation des infrastructures techniques
* La possibilité d'améliorer les indicateurs existant en utilisant les données non anonymisées du registre
* La possibilité de créer de nouveaux indicateurs reflétant les politiques d'incitation du covoiturage dans les territoires français

## Objectif
Le rapprochement des deux projets passe dans un premier temps par un chantier
de transfert des briques ETL et API de l'observatoire vert le RPC. Ce document a pour objectif de préciser le contenu de ces deux briques afin de mettre en place une roadmap pour ce chantier.

## Ressources
[Github de l'observatoire](https://github.com/betagouv/observatoire-covoiturage)
[Github du chantier territoire](https://github.com/betagouv/evolution_perimetres_geo)
[Github du RPC](https://github.com/betagouv/preuve-covoiturage)

## Descriptif de l'ETL
**Définition d'un ETL**: ETL est un acronyme qui désigne les termes "Extract Transform Load". Il s’agit d’un programme permettant de collecter des données en provenance de sources multiples pour ensuite les convertir dans un format adapté à un entrepot de données et les y transférer.

[L'ETL de l'observatoire](https://github.com/betagouv/observatoire-covoiturage/tree/main/etl) s'appuit sur le package evolution_perimetres_geo et l'enrichi avec de nouvelles migrations afin de construire plusieurs tables dans la base de données:
* **perimeters:** Table qui contient les différents millésimes de données spatiales et attributaires concernant les pays ainsi que les communes et arrondissements français. (Cette table est identique à celle utilisée par le RPC). 🧮[Définition de la table](https://github.com/betagouv/evolution_perimetres_geo/blob/main/etl/src/datastructure/000_CreateGeoTable.ts)
* **com_evolution:** Table qui recense les évolutions des codes insee communaux. (Cette table est identique à celle utilisée par le RPC). 🧮[Définition de la table](https://github.com/betagouv/evolution_perimetres_geo/blob/main/etl/src/datastructure/001_CreateComEvolutionTable.ts)
* **territories_code**: Table créée pour des questions de performance. C'est la table perimeters sans les données spatiales. 🧮[Définition de la table](https://github.com/betagouv/observatoire-covoiturage/blob/main/etl/src/datastructure/000_CreateTerritoriesCodeTable.ts)
* **territories_point**: Table spatiale de type ponctuel qui contient les centroïdes de tous les types de territoires (arr, com, epci, aom, dep, reg, country). Pour les arrondissements et les communes, le centroïde à été remplacé par le chef-lieu. 🧮[Définition de la table](https://github.com/betagouv/observatoire-covoiturage/blob/main/etl/src/datastructure/001_CreateTerritoriesPointTable.ts)
* **aires_covoiturage**: Table spatiale de type ponctuel qui contient la [Base nationale consolidée des lieux de covoiturage](https://transport.data.gouv.fr/datasets/base-nationale-des-lieux-de-covoiturage) de transport.data.gouv. 🧮[Définition de la table](https://github.com/betagouv/observatoire-covoiturage/blob/main/etl/src/datastructure/002_CreateAiresCovoiturageTable.ts)
* **rpc**: Table qui agrége les données du registre de covoiturage publiées chaque mois en opendata sur [data.gouv.fr](https://www.data.gouv.fr/fr/datasets/trajets-realises-en-covoiturage-registre-de-preuve-de-covoiturage/). (Cette table n'aura plus d'utilité dans le RPC) 🧮[Définition de la table](https://github.com/betagouv/observatoire-covoiturage/blob/main/etl/src/datastructure/003_CreateRpcTable.ts)
* **monthly_flux**: Table qui contient les flux mensuels entre territoires. Les flux sont calculés par une procédure à partir des données de la table rpc. 🧮[Définition de la table et de la procédure](https://github.com/betagouv/observatoire-covoiturage/blob/main/etl/src/datastructure/004_CreateMonthlyFluxTable.ts)
* **monthly_occupation**: Table qui contient les taux d'occupations, le nombre de trajets et le nombre de trajets incités calculés mensuellement pour chaques territoires (arr, com, epci, aom, dep, reg, country). Les calculs sont réalisés par une procédure à partir des données de la table rpc. 🧮[Définition de la table et de la procédure](https://github.com/betagouv/observatoire-covoiturage/blob/main/etl/src/datastructure/005_CreateMonthlyOccupationTable.ts)

La gestion des migrations est réalisée dans le fichier [datasets.ts](https://github.com/betagouv/observatoire-covoiturage/blob/main/etl/src/datasets.ts). L'ajout des nouvelles migrations pour les export opendata du RPC et pour les aires de covoiturage est réalisé dynamiquement en lisant les datasets disponibles sur data.gouv.fr et transport.data.gouv.fr

Le programme de l'ETL est géré par la CI/CD du dépôt Github et est exécuté lors de chaque release du projet (en même temps que le déploiement sur le serveur de production). Il suffit donc de faire une release mensuelle du projet après l'export des données opendata du RPC (le 6 de chaque mois) pour mettre à jour les données de l'observatoire.

## Descriptif de l'API
L'API de l'observatoire sert à alimenter le tableau de bord de l'observatoire (indicateurs, cartes, selection des territoires, etc..)
Elle repose sur le framework [Fastify](https://www.fastify.io/).
### Liste des endpoints:
#### /aires_covoiturage
Retourne les aires de covoiturage d'un territoire
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/aires.s.ts):

{
    tags: ['aires'],
    querystring: {
      t: {
        type: 'string',
        description: 'territory type'
      },
      code: {
        type: 'string',
        description: 'territory id'
      },
    },
    response: {
      200: {
        type: 'array',
        description:'Toutes les aires de covoiturage',
        items: {
          properties: {
            id_lieu: {type:'string'},
            nom_lieu: {type:'string'},
            com_lieu:{type:'string'},
            type:{type:'string'},
            date_maj: {type:'string'},
            nbre_pl: {type:'integer',nullable: true},
            nbre_pmr:{type:'integer',nullable: true},
            duree:{type:'integer',nullable: true},
            horaires:{type:'string',nullable: true},
            proprio:{type:'string',nullable: true},
            lumiere:{type:'boolean',nullable: true},
            comm:{type:'string',nullable: true},
            geom:{type:'object',
              properties: {
                type: {type:'string'},
                coordinates:{type:'array',minItems: 2,maxItems: 2,items:{ type: 'number'}}
              }
            }
          }
        }
      }
    }
  }

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/aires.h.ts)

#### /passengers_monthly_flux
Retourne les données de la table monthly_flux pour le mois, l'année et le territoire souhaité
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/flux.s.ts):

 {
  tags: ['flux'],
  querystring: {
    year: {
      type: 'string',
      description: 'Year (YYYY)'
    },
    month: {
      type: 'string',
      description: 'Month (MM)'
    },
    t: {
      type: 'string',
      description: 'type of selected territory'
    },
    t2: {
      type: 'string',
      description: 'type of observed territory'
    },
    code: {
      type: 'string',
      description: 'territory id'
    },
  },
  response: {
    200: {
      type: 'array',
      description:'Nb de trajets entre communes (tout sens confondus)',
      items: {
        properties: {
          ter_1: {type:'string'},
          lng_1:{type:'number'},
          lat_1:{type:'number'},
          ter_2: {type:'string'},
          lng_2:{type:'number'},
          lat_2:{type:'number'},
          passengers:{type:'integer'},
          distance:{type:'number'},
        }
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/flux.h.ts)

`SELECT l_territory_1 as ter_1, lng_1, lat_1,
l_territory_2 as ter_2, lng_2, lat_2,
passengers,distance,duration 
FROM monthly_flux
WHERE year = '${request.query.year}' 
AND month = '${request.query.month}' 
AND type = '${request.query.t}'
${request.query.code ? 
  `AND (territory_1 IN (
    SELECT ${request.query.t} FROM (SELECT com,epci,aom,dep,reg,country FROM territories_code WHERE year = ${request.query.year}) t 
    WHERE ${request.query.t2} = '${request.query.code}'
  ) OR territory_2 IN (
    SELECT ${request.query.t} FROM (SELECT com,epci,aom,dep,reg,country FROM territories_code WHERE year = ${request.query.year}) t 
    WHERE ${request.query.t2} = '${request.query.code}'
  ))`
  : ''
} 
AND territory_1 <> territory_2;`

#### /monthly_flux/last
Retourne l'année et le mois du dernier enregistrement de la table monthly_flux
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/flux.s.ts):

{
  tags: ['flux'],
  response: {
    200: {
      type: 'object',
      description:'Return last Month and Year available in passengers monthly flux',
      properties: {
        month: {type:'string'},
        year: {type:'string'}
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/flux.h.ts)

SELECT distinct year,month 
FROM monthly_flux 
WHERE type ='com' 
ORDER BY year DESC,month desc 
LIMIT 1;

#### /location
Retourne l'id et le nombre de trajets (entrant et sortant) par hexagone pour une période et un territoire. Le calcul se fait sur la table rpc.
Une requête sql retourne les origines/destinations sélectionnés puis une fonction convertie les coordonnées géographiques en un identifiant correspondant à un hexagone de l'index géospatial [H3](https://h3geo.org/)
La fonction réduit ensuite le tableau en faisant la somme des résultats partageant le même identifiant.
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/location.s.ts):
✍️ [Requête SQL + traitement](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/location.h.ts)

#### /rpc/last
Retourne la date du dernier enregistrement de la table rpc
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/location.s.ts):

{
  tags: ['location'],
  response: {
    200: {
      type: 'object',
      description:'Return last date available in rpc table',
      properties: {
        date: {type:'string'}
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/location.h.ts)

SELECT to_char(journey_start_date,'YYYY-MM-DD') as date 
FROM rpc 
ORDER BY journey_start_date DESC 
LIMIT 1;

#### /journeys_monthly_occupation
Retourne les données de la table monthly_occupation pour le mois, l'année et le territoire souhaité
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/occupation.s.ts):

{
  tags: ['occupation'],
  querystring: {
    year: {
      type: 'string',
      description: 'Year (YYYY)'
    },
    month: {
      type: 'string',
      description: 'Month (MM)'
    },
    t: {
      type: 'string',
      description: 'type of selected territories'
    },
    t2: {
      type: 'string',
      description: 'type of observed territory'
    },
    code: {
      type: 'string',
      description: 'insee code of observed territory'
    },
  },
  response: {
    200: {
      type: 'array',
      description:'Trajets et taux d\'occupation mensuels pour le type de territoires selectionné (tout sens confondus)',
      items: {
        properties: {
          territory: {type:'string'},
          l_territory:{type:'string'},
          journeys:{type:'integer'},
          passengers:{type:'integer'},
          occupation_rate:{type:'number'},
          geom:{type:'object',
            properties: {
              type: {type:'string'},
              coordinates:{type:'array',minItems: 2,maxItems: 2,items:{ type: 'number'}}
            }
          }
        }
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/occupation.h.ts)
#### /territories
Retourne tous les territoires (quelque soit leur type) d'un millésime donnée
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/territory.s.ts):

{
  tags: ['territories'],
  querystring: {
    year: {
      type: 'string',
      description: 'Year (YYYY)'
    }
  },
  response: {
    200: {
      type: 'array',
      description:'Liste des territoires pour un millésime (en paramètre)',
      items: {
        properties: {
          territory: {type:'string'},
          l_territory: {type:'string'},
          type: {type:'string'}
        }
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/territory.h.ts)

SELECT territory, l_territory, type 
FROM territories_point
WHERE year = '${request.query.year}'
ORDER BY type,territory;

#### /territory
Retourne un territoire en fonction de son type, de son code insee et de son millésime.
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/territory.s.ts):

{
  tags: ['territories'],
  querystring: {
    year: {
      type: 'string',
      description: 'Year (YYYY)'
    },
    code: {
      type: 'string',
      description: 'INSEE code'
    },
    type: {
      type: 'string',
      description: 'territory type (country, dep, reg, aom, com)'
    }
  },
  response: {
    200: {
      type: 'object',
      description:'Informations about a territory',
      properties: {
        territory: {type:'string'},
        l_territory: {type:'string'},
        type: {type:'string'}
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/territory.h.ts)

SELECT distinct territory, l_territory, type 
FROM territories_point
WHERE year = '${request.query.year}'
AND territory ='${request.query.code}'
AND type = '${request.query.type}';

#### /indicators
Retourne les indicateurs alimentant le premier onglet du tableau de bord.
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/territory.s.ts):

{
  tags: ['territories'],
  querystring: {
    territory: {
      type: 'string',
      description: 'territory id'
    },
    t: {
      type: 'string',
      description: 'type of territories'
    },
    year: {
      type: 'string',
      description: 'Year (YYYY)'
    },
    month: {
      type: 'string',
      description: 'Month (MM)'
    }
  },
  response: {
    200: {
      type: 'array',
      items: {
        properties: {
          year: {type:'string'},
          month: {type:'string'},
          territory: {type:'string'},
          l_territory: {type:'string'},
          journeys: {type:'integer'},
          passengers: {type:'integer'},
          has_incentive: {type:'integer'},
          distance: {type:'number'},
          duration: {type:'number'},
          occupation_rate: {type:'number'},
          trips: {type:'integer'},
          nb_aires: {type:'integer'}
        }
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/territory.h.ts)

SELECT b.territory,b.l_territory,
sum(a.journeys) AS journeys,
sum(a.passengers) AS passengers,
sum(a.distance) AS distance,
sum(a.duration) AS duration,
b.journeys AS trips,
b.has_incentive,
b.occupation_rate,
c.nb_aires 
FROM monthly_flux a
LEFT JOIN monthly_occupation b ON (a.territory_1 = b.territory OR a.territory_2 = b.territory) 
AND a.type = b.type AND a.year = b.year AND a.month = b.month
LEFT JOIN (
  SELECT '${request.query.territory}' AS territory, count(a.id) AS nb_aires 
  FROM aires_covoiturage a 
  LEFT JOIN territories_code b ON a.insee = b.arr AND b.year = ${request.query.year}
  WHERE b.${request.query.t} = '${request.query.territory}'
) c ON b.territory = c.territory 
WHERE b.territory = '${request.query.territory}' AND a.type = '${request.query.t}' AND a.year = ${request.query.year} AND a.month = ${request.query.month}
GROUP BY b.territory,b.l_territory,b.journeys,b.has_incentive,b.occupation_rate,c.nb_aires;

#### /best_journeys
Retourne les 10 trajets les plus covoiturés pour un mois et un territoire donnée
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/territory.s.ts):

{
  tags: ['territories'],
  querystring: {
    territory: {
      type: 'string',
      description: 'territory id'
    },
    t: {
      type: 'string',
      description: 'type of territories'
    },
    year: {
      type: 'string',
      description: 'Year (YYYY)'
    },
    month: {
      type: 'string',
      description: 'Month (MM)'
    }
  },
  response: {
    200: {
      type: 'array',
      items: {
        properties: {
          territory_1: {type:'string'},
          l_territory_1: {type:'string'},
          territory_2: {type:'string'},
          l_territory_2: {type:'string'},
          journeys: {type:'integer'}
        }
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/territory.h.ts)

SELECT l_territory_1,l_territory_2,journeys 
FROM monthly_flux 
WHERE year = '${request.query.year}' AND month = '${request.query.month}'
AND (territory_1 IN (SELECT com FROM (SELECT com,epci,aom,dep,reg,country FROM territories_code WHERE year = ${request.query.year}) t WHERE ${request.query.t} = '${request.query.territory}') 
OR territory_2 IN (SELECT com FROM (SELECT com,epci,aom,dep,reg,country FROM territories_code WHERE year = ${request.query.year}) t WHERE ${request.query.t} = '${request.query.territory}'))
ORDER BY journeys DESC
LIMIT 10;

#### /aom
Retourne un geojson contenant toutes les aom d'un millésime donnée. Cet endpoint n'est pas utilisé par l'observatoire. Il a été créé pour les besoins de l'outil stat sur Metabase.
✨ [Schéma](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/schemas/territory.s.ts):

{
  tags: ['territories'],
  querystring: {
    year: {
      type: 'string',
      description: 'Year (YYYY)'
    }
  },
  response: {
    200: {
      type: 'object',
      description:'Geojson des aom',
      properties: {
        type: {type:'string'},
        features: {
          type:'array',
          items: {
            properties: {
              type: {type:'string'},
              geometry:{
                type:'object',
                properties:{
                  type: {type:'string'},
                  coordinates:{
                    type:'array',
                    items:{ type: ['array','number']}
                  }
                }
              },
              properties: {
                aom:{type:'string'},
                l_aom:{type:'string'},
              }
            }
          }
        }
      }
    }
  }
}

✍️ [Requête SQL](https://github.com/betagouv/observatoire-covoiturage/blob/main/api/src/api/v1/handlers/territory.h.ts)

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', json_agg(ST_AsGeoJSON(t.*)::json)
)
FROM (
  SELECT aom, l_aom, ST_Multi(ST_Union(geom_simple)) as geom
  FROM perimeters
  WHERE year = ${request.query.year} 
  AND aom IS NOT NULL
  AND dep NOT IN ('971','972','973','974','976')
  GROUP BY aom,l_aom
) as t;