Principe de calcul :

Pour chaque date, on compte les objets qui existent à cette date en reconstituant leur état depuis l'historique :

• Un objet existe à une date donnée s'il a été créé avant ou à cette date
• Et s'il n'a pas été supprimé avant ou à cette date
• Les objets actuels (non modifiés depuis la dernière mise à jour) sont aussi pris en compte

Exemple de requête SQL pour un jour donné :

Cette requête reconstitue l'état des objets à une date donnée depuis l'historique des changements.

-- Compter les objets du projet pour une date donnée
-- Remplacez '2025-01-15' par la date souhaitée
WITH object_states AS (
  -- Dernier état connu de chaque objet avant ou à la date
  SELECT DISTINCT ON (osmid)
    osmid,
    action,
    ts,
    version
  FROM pdm_changes
  WHERE project = '2024-12_streetlamps'
    AND ts <= '2025-01-15T23:59:59Z'
  ORDER BY osmid, ts DESC, version DESC
),
current_objects AS (
  -- Objets actuels qui n'ont pas été supprimés dans l'historique
  SELECT DISTINCT osm_id::TEXT AS osmid
  FROM pdm_project_streetlamps
  WHERE osm_id::TEXT NOT IN (
    SELECT osmid FROM object_states WHERE action = 'delete'
  )
),
existing_objects AS (
  -- Union des objets actuels et des objets de l'historique non supprimés
  SELECT osmid FROM current_objects
  UNION
  SELECT osmid FROM object_states WHERE action != 'delete'
)
SELECT COUNT(*) AS count
FROM existing_objects;

Filtre OSM utilisé pour ce projet :

Exemple de requête SQL pour un jour et une zone donnés :

Cette requête filtre les objets existants à une date donnée pour une zone administrative spécifique.

-- Compter les objets du projet pour une date et une zone administrative
-- Remplacez '2025-01-15' par la date et 12345 par l'ID de la zone (boundary)
WITH object_states AS (
  -- Dernier état connu de chaque objet avant ou à la date
  SELECT DISTINCT ON (osmid)
    osmid,
    action,
    ts,
    version
  FROM pdm_changes
  WHERE project = '2024-12_streetlamps'
    AND ts <= '2025-01-15T23:59:59Z'
  ORDER BY osmid, ts DESC, version DESC
),
current_objects AS (
  -- Objets actuels non supprimés
  SELECT DISTINCT osm_id::TEXT AS osmid
  FROM pdm_project_streetlamps
  WHERE osm_id::TEXT NOT IN (
    SELECT osmid FROM object_states WHERE action = 'delete'
  )
),
existing_objects AS (
  -- Union des objets existants à la date
  SELECT osmid FROM current_objects
  UNION
  SELECT osmid FROM object_states WHERE action != 'delete'
),
objects_in_boundary AS (
  -- Filtrer par zone administrative
  SELECT eo.osmid
  FROM existing_objects eo
  INNER JOIN pdm_features_boundary pfb ON eo.osmid = pfb.osmid
  WHERE pfb.project = '2024-12_streetlamps'
    AND pfb.boundary = 12345  -- ID de la zone (ex: département, commune)
    AND ('2025-01-15T23:59:59Z' BETWEEN pfb.start_ts AND pfb.end_ts
         OR (pfb.start_ts IS NULL AND pfb.end_ts IS NULL)
         OR ('2025-01-15T23:59:59Z' >= pfb.start_ts 
             AND (pfb.end_ts IS NULL OR '2025-01-15T23:59:59Z' <= pfb.end_ts)))
)
SELECT COUNT(*) AS count
FROM objects_in_boundary;